It’s happened before, a business user brings up an excel spreadsheet with some critical macro that needs to be fixed or updated. Except whomever wrote it protected the VB6 project with a password, possibly to hide database connection strings or other such protected data. However, that person is not available, long gone, or simply doesn’t remember. The business logic of the macro is a long forgotten voodoo, so rewriting from scratch just isn’t an option. No matter. Googling around will find a few available methods to do this that involve editing the file to replace the password, or to invalidate the password. Sometimes these work, sometimes they don’t. I had just such an occasion where the file editing methods(that I’ve used successfully in the past) just resulted in corrupt files or repeated error messages trying to open the VBA Project. So I’m presenting here an alternative method. Since I am a software developer, I decided to try to use Visual Studio’s debugger to skip around in the code. If you don’t have VS, and are desperate to unlock a macro, the current ‘Community Edition’ is free, although its a hefty download and install, and it may be a bit much if your not used to software development. But I’ll go step by step so you can follow along.
First, fire up the Excel spreadsheet and Visual Studio. I’ve prepared a sample workbook and protected the VBAProject with a password and set the ‘Lock project for viewing’ flag. In VS (I’m using Community Edition 2013), go to Tools > Attach To Process, then attach the debugger to Excel.exe.
Now, ensure your options are setup for this, which they probably are not by default. Debug > Options and Settings, uncheck ‘Enable Just my Code’ and check ‘Enable address-level debugging’ and ‘Show disassembly if source is not available’.
Now go to Excel and right click on the VBAProject in the tree on the left, and select ‘VBA Project Properties’.
Now that you have the password prompt, go back to Visual Studio and hit the break button. Now, ignore the assembly code you are faced with and find your call stack window. To help us out, you should be able to right click one of the stack frames for both user32.dll and VBE6.DLL and select ‘Load Symbols’ (So you hit load symbols twice, once for each dll).
Now this should automatically get the debugging symbols for both these dlls from the Microsoft symbol servers. This is helpful because it gives us function names in the stack trace instead of just meaningless addresses. Your call stack should look like this now.
If it doesn’t look like this, it’s possible you happened to stop on another thread. Hit Continue and Break again, and see if it looks better. Double click on the stack frame for VBE6.DLL!Project::ValidateAccess(Char *), or right click and select ‘Switch to frame’.
You should be brought to the assembly code with the cursor just at the line after the Call instruction to DlgBoxParam. Before that call you’ll see a series of Push instructions. If your not familiar with assembly, the four pushes are the four parameters that that function takes. You’ll also notice there is no Pop instruction after the call, meaning the function doesn’t return a value.
Now what we will do here is place a breakpoint on the first Push, which will stop the program just before it executes any of the pushes. What we are going to do is skip the call to DlgBoxParam, and we have to skip the related Pushes too, or we will unbalance the stack and crash Excel. To place the breakpoint, just click in the area just to the left of the source window.
Now hit continue, go back to excel and cancel out of the password dialog. Then try to go back to the project properties. This time you should hit your breakpoint in VS. Just right click on the test line right after the call instruction and select ‘Set Next Statement’. You’ll see the little yellow arrow is now at that line. Hit continue (F5).
Now just go to the Protection tab, uncheck ‘Lock Project for Viewing’ and hit OK.
BAM! You’re in.
Note, this doesn’t clear the password. If you save and close, you will be prompted for the password again to access the project properties, but you cleared the viewing protection so you should still be able to view and edit the code. However repeating this method does not allow you to turn the viewing protection back on without resetting the password to something new. Alternatively, if you want to leave the macro protected with the mystery password, you can do the same procedure, just click the plus to expand the tree to get the password prompt, instead of entering the project properties. This way, you gain access for just your session, and the viewing protection resumes once you exit and reopen excel.
Like always, hopefully someone will find this little trick useful. Thanks for reading.