Over at Data Pig Mike shows us how to crack the VBA Password. I think it’s always been a bit of an unwritten rule that those of us in the club, never passed on how to do the crack, for example if someone asked on a news group or forum. So Mike has broken the code a bit. But to be honest, that’s of little practical importance. This is not the first time this information has been disclosed on the internet, [1, 2, 3, 4], and there where post about this method for years and years, in one place or another. I’ve even seen code on Linked in showing how to automate the removal of passwords – hell, I’ve even copied that code and put it in an addin I use!!!
Securing VBA code has been a bit of a Golden Goose for me over the years. I would love to just compile some VBA code and ship that as a .dll addin. Indeed, this is possible – assuming you have a copy of MS Office Developer (last version XP I think), so that is one option, – old copies can be had for as little as 60 quid (Microsoft Office XP Developer) . But to be honest if you’re going to do that, you might as well go the whole hog and do a VB6 COM addin, right? – I’m not sure about UDF’s via Office Dev versions…?, they don’t work via COM addins.
Any way, I have looked into this whole issue in some depth, and I cannot think of a way to really lock down your VBA code. I have suggested this MDE method before which works in a pinch.
In a product I have in development, I’ve designed and coded a secure system which uses VB6 to hold a licence code, tying the app to the PC – days left can be used – but even this is an issue without a internet connection – everything is a compromise. In this approach the UI stays in VBA, but the important methods are hidden in complied dll’s, which the VBA app calls to. These VB6 dll’s (could be C, or other lang) then do a licence check and if ok carries on. works fine, even on Win 8.1, but requires installation etc.
Basically there’s no way to secure VBA in Excel as we know it. You can make it harder, there’s the old shared workbook trick, but that’s easy to overcome if you know how. VBA ossification can be OK if done well, but you can’t obscure an Office Apps OM, so it of limited use.
The best answer if you want a secure an addin is to used something that makes a complied dll, .Net*, C, or I guess VB6/Dev editions of Office, and if you want a quick fixed consider the mde method above.
I’m always interested in other approaches people have, leave a comment.
*well, ossification – its different to VBA ossification