How password protection of Excel VBA code works?

By : axk

This question is related to my previous one.

Can you explain or provide a link to an explanation of how Excel VBA code password protection actually works in versions prior to 2007? What is the difference in Excel 2007 and previous versions in terms of password protection?

Also does Excel's password protection actually encrypt the code? How does Excel execute the code if it is encrypted?

Lastly, how does password removal software for excel work?

By : axk


Someone made a working vba code that changes the vba protection password to "macro", for all excel files, including .xlsm (2007+ versions). You can see how it works by browsing his code.

Here's the guy blog: Here's the file that does the work:

Pasted from a previous post from his blog:

For Excel 2007/2010 (.xlsm) files do following steps:

  1. Create a new .xlsm file.
  2. In the VBA part, set a simple password (for instance 'macro').
  3. Save the file and exit.
  4. Change file extention to '.zip', open it by any archiver program.
  5. Find the file: 'vbaProject.bin' (in 'xl' folder).
  6. Extract it from archive.
  7. Open the file you just extracted with a hex editor.
  8. Find and copy the value from parameter DPB (value in quotation mark), example: DPB="282A84CBA1CBA1345FCCB154E20721DE77F7D2378D0EAC90427A22021A46E9CE6F17188A". (This value generated for 'macro' password. You can use this DPB value to skip steps 1-8)

  9. Do steps 4-7 for file with unknown password (file you want to unlock).

  10. Change DBP value in this file on value that you have copied in step 8.

    If copied value is shorter than in encrypted file you should populate missing characters with 0 (zero). If value is longer - that is not a problem (paste it as is).

  11. Save the 'vbaProject.bin' file and exit from hex editor.

  12. Replace existing 'vbaProject.bin' file with modified one.
  13. Change extention from '.zip' back to '.xlsm'
  14. Now, open the excel file you need to see the VBA code in. The password for the VBA code will simply be macro (as in the example I'm showing here).
By : Morosho

Phil is correct - the password prevent you from looking at the modules, they are not encrypted themselves. I know in excel 2007 a file is essentially a zipped collection of XML and other files, but I don't know the details of how encryption is handled. For earlier versions - excel 2, 3, 4, 5, 95, 97, 2000, XP, & 2003, there is the comprehensive's Documentation of the Microsoft Excel File Format:

The Excel file format is named BIFF (Binary Interchange File Format). It is used to store all types of documents: worksheet documents, workbook documents, and workspace documents. There are different versions of this file format, depending on the version of Excel that has written the file, and depending on the document type.

A workbook document with several sheets (BIFF5-BIFF8) is usually stored using the compound document file format (also known as “OLE2 storage file format” or “Microsoft Office compatible storage file format”). It contains several streams for different types of data. A complete documentation of the format of compound document files can be found here.

The Workbook Protection Block occurs just after the DEFINEDNAME block (i.e. Named Ranges) in most BIFF streams, although BIFF8 is a major departure from that pattern. The record protection block In Biff5 - Biff8 the structure of the Workbook Protection Block:

  • WINDOWPROTECT Window settings: 1 = protected
  • PROTECT Workbook contents: 1 = protected
  • PASSWORD Hash value of the password; 0 = no password
  • PROT4REV Shared workbook: 1 = protected
  • PROT4REVPASS Hash value of the shared password; 0 = no password

The password block stores a 16-bit hash value, calculated from the worksheet or workbook protection password.

VBA security is widely considered to be pretty poor. The VBA code isn't compiled, and the source is available in the excel file. The password protection is pretty easy to circumvent.

As I understand it, Office 2003 and earlier saves the vba code as part of the binary format of the worksheet (or document / presentation). When you fire up the VBA IDE, it simply looks to see whether the VBA code has been "protected" or not. This doesn't mean it's encrypted - just unavailable for viewing. The theory is that this stops your users from meddling with your code, but a hard-core coder would be able to get around the password.

So Excel doesn't need to unencrypt any code - it just needs to stop people from viewing it.

Office 2007 does encrypt macros (don't ask me how or what algorithm). This is necessary presumably because XLSM files (or any Office 2007 file) are just zip files with a different extension. Anyone can get into those files and poke around.

To answer your last question - how does the password removal work on older Office formats, I'm not entirely sure. Different vendors will possibly approach the problem different ways, but I suspect the most common approach will be a brute-force attack on the passwords until a match is found.

The Excel VBProject object has a Protection property which will return different enumerations depending on the protection status of the macro (vbext_pp_locked if the macro is protected, for example). If you were to keep trying passwords programmatically until the vbext_pp_locked evaluated to false, you would have found your password.

This video can help you solving your question :)
By: admin