Smart way of unlocking the Excel VBA project and workbook

by Krukarius · 2020-07-28

Probably experienced a sad situation when the password for your Excel workbook is lost or forgotten. Head up, because it’s not the end of the world. Nowadays we can easily break down the forgotten password and recover our hidden VBA project in at least a few ways.
From these many ways, there are major 2 ways of breaking the MS Excel password both for the workbook only and the VBA project:
– using the VBA code
– unpacking the Excel file and changing the existing XML code manually.

I am going to focus on this second option, which I guess wasn’t fully explained in the one piece so far.

There are a lot of sources on the Web trying to show step-by-step how to unlock the Excel document, but I couldn’t find any with a detailed explanation including these 2 situations, which this article is about. Let’s get started then.

First of all, I would like to show the MS Excel workbook’s essence. The Excel document, which we know contains a multitude of cells, is located in columns and rows. Moreover, our workbook can be expanded by a various number of worksheets, between which we can smoothly migrate afterward. Probably you didn’t wonder before why the Excel document looks like this is it really one file, or do we have another feature behind it? All these things will be clarified as you continue to read this article.

Pic. 1 Typical MS Excel document is built from the bulk of cells placed in rows and columns. Every single worksheet looks the same at the initial stage. The file can be expanded without limitation.

You don’t need to care about it really when everything is fine with your document, i.e. it’s not locked or at least you know the password. Checking the workbook protection is a simple thing, You must select the “File” in the main toolbar (top left) and go to the “Info” section, where you will see all protected sheets (or workbook) in the yellow label (Pic. 2).

Pic. 2 The list of worksheets protected in Excel, which is labeled in yellow.

Pic. 3 Excel 2016 – protection of the whole workbook.

If your VBA project is locked, you won’t access it at all. After the double-click, the console will ask you about the password (Pic. 4).

Pic. 4 VBA project in Excel 2016 locked.

This is all, I wanted to say about the Excel workbook and the VBA project protection. If your password is unknown without any reason, then you must break it if some alterations within the workbook are needed.

The option, which I would like to explain in detail is unpacking (unzipping) the Excel document. Here we are heading to the explanation, of what the Excel document is built from.
The essential software, which you will need for this purpose is the 7Zip program. It’s quite similar to WinZip or WinRar. This application allows us to open the zipped GIS files with i.e. .kmz extension, like here. In this event, we should do the right click on our Excel document, next, select the 7Zip and “Open archive” option (Pic. 5).

Pic. 5 Opening the Excel file in the 7Zip application.

As you can see, the 7Zip application works pretty much the same as WinZip mentioned above. Another thing, which brings us an answer about the essence of the Excel workbook is, that our file is built from many other minor files. They are mostly .xml files comprising our spreadsheets and data included (Pic. 6).

Pic. 6 The structure of the MS Excel workbook previewed via 7Zip software with the random directory opened.

Now every file can be opened at least in the standard Notepad application. In our case, the most important files to open are based in the /xl/ folder (Pic.7), where we have basically all that has been included in our visual Excel workbook. Now we can see it in separate .xml files (Pic. 6).

Excel xl folder displayed in 7Zip software

Pic. 7 The /xl/ folder includes all elements present in our Workbook (charts, images, calculations, etc.). In order to break the password, we must take a look at the /worksheets/ subfolder and workbook.xls file.

If we want to remove the password, we must look at the /worksheets/ subfolder or workbook.xml file. It depends on our workbook information. If we have a few sheets locked only, then we must enter the aforementioned folder. If our whole workbook has been locked, then we must open the file.
Firstly let’s see how to deal with the separate sheets. When you open the /worksheets/ directory, you will see the list of all sheets available in your workbook (Pic. 8).

Pic. 7 The list of all worksheets available in the given workbook is previewed in the 7Zip software.

Now we can open any of it with the mouse right-click (Pic. 8). The tool will use Notepad as the default editor (Pic. 9).

Pic. 8 Opening the .xml Excel sheet via 7Zip and right-click.

Pic. 9 One of the worksheet opened as the .xml file in the Notepad application.

The XML code above doesn’t look friendly. We can obviously find a way forward to display it tidier, however, we don’t need to do it necessarily.
The Notepad app is enough for the changes, which we desire. Once you’ve opened the worksheet file, click the Ctrl+F and find the “Protection” string inside the code (Pic. 10). If you do so, then select it gently down to the bracket, which appears to finish the section. When you select it, just press the Backspace button and save the document. Your sheet should be already unprotected.

Pic. 10 Easy way for sheet unprotection in Excel.

Repeat this task for all the sheets, which are protected. It may be hard to determine which sheet exactly is protected when we are based on the yellow-labeled list in our file information (Pic. 2). In this event, I would advise you to jump into the VBA console, where you will see the sheet number next to the name (Pic. 11).

Pic. 11 List of worksheets with their original order in the VBA Excel console.

If your VBA project is locked, then the only option available is to due to the SHEET function. The short formula will bring you the answer shortly (Pic. 12).

Pic. 11 Determination of the worksheet number in our Excel workbook in comparison with the VBA project worksheet list.

If we know the sheet number is easier for us to pick up the .xml file and do the relevant alterations.
Again, Notepad doesn’t display our XML code in a smart way. However, there is no other option to launch the .xml file, unless we extract it outside the main Excel file and open it in different applications or browsers. This is not the primary thing of this article. If you really want to have a smarter look, use Notepad ++ instead.
When you have a whole workbook locked, do the same thing for the workbook.xml file described above. Thereafter you can enjoy the result because the unwanted password is gone and your document is not protected anymore. In order to make sure, that all the changes have been applied, just go to the “File” -> “Info” again and see what happened (Pic. 12).

Pic. 12 Excel workbook unprotected as seen in the file information.

Because the yellow label is gone, your file is not protected anymore. The information about the locked workbook structure is also gone, which means that our process has been successful.

The thing looks more complicated when we have no access to the VBA project. Basically, if we don’t particularly need to change the VBA code in our workbook it’s fine. We can follow the process above and should be ok. However, sometimes the VBA code might trigger the automatic protection of some particular sheet. If this is so, even with the best will in the world we won’t be able to unlock the worksheet with the aforementioned method.
In this case, we can use the 7Zip software again. In our /xl/ folder we should find the vbaProject.bin file, just above the workbook.xml one (Pic. 13).

Pic. 13 The vbaProject.bin file location in the /xl/ directory.

We can access this file because it works analog to our workbook file – it’s just another directory containing the specified VBA code placed in the specified worksheet. Unfortunately, we can’t open the files physically, even after the extraction. Adding up the .bas extension won’t work either. The only way to read this file is with Notepad ++, which can be accessed inside (Pic. 14).

Exccel VBAproject bin file in Notepad ++

Pic. 14 Our vbaProject.bin file opened in Notepad++ with the most important line of code marked red.

There is been told, that Notepad ++ cannot cope with the .bin file extension and we need a separate tool – HEX Editor. It’s independent software for Windows, but it also can be a plugin for Notepad ++. It’s not true, at least for newer versions of the Notepad ++ (I guess from 6.0 onwards), because between these terrible blobs, we can find some important lines of code (Pic. 14). It means, that it’s enough to run the .bas file and find the line of code commencing with DPB using, for instance, the Ctrl+F as previously (Pic. 15). Next, we must change the “DPB” to “DPx” (Pic. 15), save the file, and export it back to the 7Zip directory, where it should be replaced.

Reading and finding the code in bin file in Notepad++

Pic. 15 Finding the “DPB” in the .bin file and replacing it with “DPx” by Notepad ++.

When everything is ready, we can launch our Excel workbook again. The first impression is not the best, as we get the following alert (Pic. 16).

Pic. 16 Alert informing about the changes done in the VBA project.

We should click “OK” and “Continue Loading Project”. When the project is loaded, go to the VBA console, where you will be welcomed with another alert (Pic. 17). By clicking “OK” you will be able to see at least the list of sheets, that you weren’t able before (Pic. 17).

Pic. 17 Unexpected error in our VBA Excel project.

Now you must right-click and select the “VBA project properties”, where you should switch to the “Protection” section (Pic. 18). There is an option of “Lock project for viewing” which initially is selected. You have to untick it if you wish to see your VBA project.

Pic. 18 Unlocking the VBA Excel project for viewing.

The last step here is to run the workbook again in order to make the error disappear. Finally, you have full access to the VBA project in your Excel workbook again (Pic. 19).

Pic. 19 The unprotected VBA Excel project.

Now you can set the new password or keep it unprotected. As you can notice, the whole process is not difficult, but you must spend a while.

Mariusz Krukar

Links:

Forums:

Youtube:

Smart way of unlocking the Excel VBA project and workbook

You may also like...

Batch import PDF document to Google Sheets – random data

Choropleth map in QGIS based on the MS Excel data

Fixing issues with opening inserted objects in Excel

Categories