A VBA project is nothing but the bundle of macro(s) along with the host application in which it has been coded. For example, if we write a small reusable function to split a column of cells values into two using the MS Excel VBA Editor, the workbook containing is a project in Visual Basic for Applications.
Protecting a VBA Project
Just like how Excel offers protection to its sheets / data using passwords, it is possible to protect a VBA project’s code too. This can be done through the following steps.
- Right click on the VBA Project in the Project Explorer. Select VBAProject Properties.
- Select the “Protection" tab in the “VBAProject – Project Properties" window.
- Select the “Lock project for viewing" option. Enter a password and confirm the same in the second text box too.
- Click on the ok button and close the dialog box. For this to work, the Excel workbook has to be closed and re-opened.
Opening a Macro Protected Workbook:
- On opening a workbook which contains a password protected VBA project, we may try to view or edit the code of any module. But we see that all buttons except “Run" are disabled.
- If we want to create / edit , we can type a new procedure name in that edit box as below. This will enable the create button.
- Clicking on the create button will ask you for the password. If you type the correct password , you will be given an access to the project code. Otherwise, you can just run, but not view, the modules.
Introduction to VBAProject.bin
The VBAProject.bin is a binary file of any VBA project (as described above, an Excel workbook or document with a macro) that contains all the code modules of the project/workbook in it. “VBAProject.bin" is a default name given by MS Office to the binary VB Project file. It can actually be renamed by the user.
It can be opened and read using an OLE document container such as a DocFile viewer that is available in the tools menu of VB 6.0 or the OLE Viewer which is available as freeware on the internet.
Convert an excel file into a zip file:
When the extension of a normal excel file is changed to a “.zip" extension, the zip file contains all details of the Excel workbook — including every worksheet, the row height , content, the macros in it, etc. After you extract this zip file and explore inside, a VBAProject.bin file can be found.
Years ago, it was not so easy to extract the source code from MS Office documents as the format of their files was not documented. In recent years, Microsoft published a huge guide with documentation on MS-OVBA which talks about the storage of this code. Click on the below link to read the document.
After this information was released, several tools were developed to extract code from the storage of these documents. As I mentioned earlier, when the zip archive is viewed through an OLE document container, the VBA macros can be seen in a VBA project structure. The structure of this arrangement is different depending on the host, content and version of the host.
As described in the MS-OVBA document, the root of the structure is a VBA Project with the name “Macros" or “_VBA_PROJECT_CUR". It should contain a minimum of 3 elements whose names are are case- insensitive.
- Storage for VBA
- A PROJECT stream
Two separate streams within VBA:
One of the streams inside the VBA storage should contain all the source code. Here are some tools that are available in the market to extract data from VBAProject.bin file.
Uses of the VBAProject.bin file
- The macros of a corrupted Excel workbook can be recovered using this bin file.
- It can be easily exported to other devices because of its small size.
- It can be used to analyze/ understand the working of code that connects several workbooks, or even a single workbook.
- It can help the user read the VBA macro code of the project without opening the workbook.
There may be situations where the Excel documents get corrupted or your MS Office installation might not have been done ideally. This causes the macro components of a VBA project to be unable to be viewed or edited. This is where VBAProject.bin can be of assistance in retrieving the lost or hidden code.
The Microsoft documentation [MS-OVBA] can help you understand how to read and extract data from VBAProject.bin files. Read through the document to explore and learn more about this.