How password protection of Excel VBA code works?

Question

This question is related to my previous one.

Can you explain or give a link to an explanation of how Excel VBA code password protection actually works in versions prior to 2007, and what is the difference in 2007? Does it actually encrypt the code and how Excel executes the code if it is encrypted? How password removal software for excel works?

Answer 1

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.

Answer 2

Hi I 'd like to know .How can this code use in macro "unprotect sheet"? At this time I made this macro but it dosen't work!

Tanks dam

Answer 3

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 OpenOffice.org'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.