tl;dr: Don’t use VBA Password Protection. It is insulting, and it doesn’t actually protect the macros.
There are a few ways to protect Excel workbooks. The most common protection is password-to-read. That actually involves encrypting the worksheets, and the 40-bit RC4 scheme is reversible given a day or two (but certainly not trivial). Another “protection” method is to put a password for the VBA Project:
- Go to the VBA Editor
- Right-click the project and go to Properties
- Set the password
You would think that the source were obfuscated or encrypted or something. You would be wrong: the protection is merely for the UI.
Basically, the internal project file has a few lines of the form:
Those fields correspond to ProjectProtectionState, ProjectPassword, and ProjectVisibilityState (respectively). By wiping those fields (replacing the text with blank spaces), Excel does not ask for a password to view the project code.
Now, how does one actually remove password protection from a file?
For XLS, XLSB, and other file formats that use the Compound File Binary containers (The first few bytes are D0 CF), the storage PROJECT (which is located at the path R/_VBA_PROJECT_CUR/PROJECT) has an ASCII rendering of those lines. Use a hex editor to find the location of those ASCII characters, and just replace them with blanks.
For XLSM and other formats that use ZIP containers (which have the zip magic number 50 4B), unzip the file (you can directly run `unzip foo.xlsm`), apply the aforementioned procedure to xl/vbaProject.bin (a file which is in the CFB format), and then zip.
As a demonstration, consider arena.xlsm, an interesting game with protected VBA modules. Using the aforementioned process, the protection is easily removed.
EDIT: There is a discussion on Hacker News and Reddit