Advertisements
Advertisements

An original point of view

Miscellaneous

Smart way of unlocking the Excel VBA project and workbook

Excel file is based on the XML language

The MS Excel file is based on the XML language

Probably you 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 on 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 is 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 essence.  The Excel document, which we known contains a multitude of cells, located in the 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 and 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.

Excel typical document

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 the limitation.

You don’t need 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 in the yellow label (Pic. 2).

Excel info sheets protection

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

Excel whole workbook protection

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).

Excel VBA project locked

Pic. 4 VBA projet in Excel 2016 locked.

This is all, what I wanted to say within 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, 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).

Excel opening in 7zip

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 of our spreadsheets and data included (Pic. 6).

Excel .xml structure opened in 7Zip

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 including 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).

Excel worksheets 7Zip xml

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

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

Excel spreadsheet worksheets opened

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

Excel .xml sheet opened

Pic. 9 One of the worksheet opened as the .xml file in 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 will do so, then select it gently down to the bracket, which appears to finish the section. When you selected it, just press the Backspace button and save the document. Your sheet should be already unprotected.

Excel sheet unprotection

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 basing 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).

Excel VBA list of sheets

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).

Excel sheet number and VBA project list

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

If we are knowing the sheet number is easier for us to pick up the .xml file and do the relevant alterations.
Again, the 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 in different applications or browsers. This is not the primary thing of this article. If you really want to have a smarter look, use the 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).

Excel file unprotected

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 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 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).

Excel VBA project bin

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

We can access to 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 too. The only way to read this file is with the Notepad ++, which can access 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 the Notepad ++ cannot cope with the .bin file extension and we need the separate tool – HEX Editor. It’s the 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 .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).

Excel changes in VBA code

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, what you weren’t able before (Pic. 17).

Excel VBA priject unexpected error

Pic. 17 Unexpected error in our VBA Excel project.

Now you must do the 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.

Excel VBA project protection section

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 disappeared. Finally, you have full access to the VBA project in your Excel workbook again (Pic. 19).

VBA Excel unprotected

Pic. 19 The unprotected VBA Excel project.

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

Mariusz Krukar

 

Links:

  1. Notepad ++ Hex edit
  2. remove_vba_project_password.bas (up to 32 bit system)
  3. StackOverflow: remove_vba_project_password.bas (64 bit system)
  4. https://www.systoolsgroup.com/how-to/break-vba-password-in-excel/
  5. https://www.makeuseof.com/tag/recover-excel-vba-password/
  6. SHEET and SHEETS Functions in Excel
  7. How To Get The Current Sheet Number Of A Workbook?
  8. Determining a Worksheet’s Number
  9. Excel SHEET Function

Forums:

  1. https://superuser.com/questions/807926/how-to-bypass-the-vba-project-password-from-excel
  2. https://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project
  3. https://stackoverflow.com/questions/272503/removing-the-password-from-a-vba-project
  4. https://stackoverflow.com/questions/60641199/use-notepad-as-hex-editor
  5. extracted VBA hex files from vbaproject.bin – how to extract VBA code?
  6. How do I determine the sheet number in Excel when they’ve been renamed?

Youtube:

 

Advertisements
Advertisements
Follow

Get the latest posts delivered to your mailbox:

%d bloggers like this: