Ever felt the need to open a VBA protected excel file... maybe one of your old files that contained an excellent routine! How do you come out of that pain?
Important: This article is for educational purposes. Try this method for opening ONLY your own files, as I did too!
So how does Excel store the file contents - cell data including formulas and formats, conditional formatting, VBA code, etc. etc. Lets investigate. Create a new Excel file MyTest.xlsm and enter some dummy test data in the first sheet. Add some formulas and conditional formatting (if you want to really understand the details).
Try to open the file in Excel. It gives an error saying that the file contains an invalid key DBx. Hit Yes and proceed. The file opens. So far good. Now go to the VBA editor.
So Excel may NOT be encrypting the VBA file after all, rather it is only setting a flag to lock the VBA content. This is evident because we can still see the code in the vbaProject.bin file (that we had written in the VBA earlier).
That also says that, it may be better to access Excel data directly from these XML files rather than through the Excel application model. We could have much more control and simplified logic for storing (for instance in database), transporting (eg client machine to server), opening and displaying simple content. Many possibilities here...
Well, I was pretty pleased that today I learned something new... and an unlocked VBA code.