Excel Password Security 

Cracking the VBA Code Page Password Protection
Sometimes we allow users to run Excel VBA applications but we don't want them to browse through the VBA code. One example of this would be an Excel report that extracts data from an ERP system via SQL Query. It is likely with an ODBC connection that the ERP database username and password are keyed in clear text in the VBA code. One common practice is to protect the VBA code page with a password. So, here's how to crack that password:

If you haven't yet, copy the locked Excel file to a clean folder. It's better to not work with the original file.

To start, you need to complete the front half of the ZIP process FIRST... to reveal Excel file XML contents. 
If you haven't done that yet, Go to the ZIP Process first. If you've already extracted the ZIP file, then continue below...

Next, you're going to need a HEX file editor. Personally, I like to use HxD largely because it is FREE. You can get it HERE . Just download and run the setup. Once installed, you can continue with this procedure.

  • In the resulting folder after the ZIP process extraction above... if you drill down into the xl subfolder, you will find the binary file vbaProject.bin. This is the file we need to open in the HEX editor. 

  • In the HEX editor, we're only going to do one thing... Search for the string DPB and replace it with the string DPX. There should only be one. Once the replacement is made, save the file and exit the HEX editor. That's why I like FREE. If you are not going to use a HEX editor for anything else except this simple search and replace, then why pay for editing software?

  • Now, we'll work in reverse. Go back up to the extraction folder top level, highlight all of the contents, and Send it to a new ZIP compressed folder.

  • Continuing the reverse process and rename the file from ZIP back to Excel's xlsm file extension.

  • Once the file is renamed, open it as you normally would in Excel. When you do so, Excel should immediately complain that there is something wrong with the file. Of course there is... you just ruined the file above. Excel will ask to recover as much as it can... Click Yes to proceed.

  • Once repairs are made, Excel will tell you it either repaired or removed the unreadable content. You can close this message. This is where this procedure sometimes doesn't work out as nicely as you wanted it to. Nevertheless, let's press on.

  • Next at the top of the screen, Enable Content from the Security Warning. Now, VBA is going to complain about the DPX key you replaced with the HEX editor. Just click Yes to continue.

  • Now hop over to the Developer Menu and ask to View Code. Excel should start throwing these 40230 errors. Simply click OK through all of them. You're going to get this error message for every single object in the VBA editor. If you have a lot of sheets and modules, just be patient and click OK through all of the error messages.

  • Now hop back to the sheet and Save As to a completely new Excel file. Excel will clean up the error areas when saving the new file. Then close Excel and re-open the file fresh. The file should show as a clean, repaired file.

  • Again, hop over to the VBA editor, and, if everything worked, you will now see code WITHOUT the password protection! Now you can set your own password.............  but why bother?   :-)
Search