I have been told, but have not been able to confirm, that passwords protecting VBA code are harder to crack than passwords protecting worksheets. If this is actually true, it suggests that putting some of your key algorithms in VBA rather than in standard cell formulae might make it your intellectual property a bit harder for a thief to extract.
However even if that is correct it does not prevent your thief from USING the stolen spreadsheet even if (s)he cannot see how it works.
For this second problem, I have used approaches along the following lines.
(1) Get some core part of your spreadsheet to execute in VBA rather than in spreadsheet formulae, as recommended above. Call this the core VBA.
(2) Make the execution of this core VBA conditional upon some other VBA-based operation, in such a way that the default condition is for the core VBA to not run. Call this the qualifying VBA.
(3) Set things up so that the qualifying VBA is automatically run when the spreadsheet is first opened.
(4) Embellish the core VBA so that it will print out an appropriate message if it is run without the qualifying VBA having been satisfactorily run, and will then set some key answers to #NA() or something similar. Resist the temptation to have it give wrong-but-feasible answers, as this might land you at the wrong end of the legal system.
(5) Password-protect both the VBA and the spreadsheet.
The simplest form for getting a pass/fail message from the qualifying VBA to the core VBA is to use a global public integer variable. The qualifying VBA sets this variable to a positive value if the test(s) are passed, or to zero if the test(s) are failed. Since variables are implicitly initialised to zero in VBA, the default condition is "fail" as required. The core VBA then merely has to examine this variable to decide whether to run correctly.
What sort of qualifying tests can the qualifying VBA perform? If you want to impose expiry dates on the spreadsheet, it can examine the system date. If you want to restrict execution to certain computers, it can examine the computer's MAC address. If you want to restrict execution to computers attached to the company's network, it can look for a file on some particular network server. If you want to restrict to certain network users, there are probably appropriate environment variables set by your network login processes, or you can have Registry entries set. And so on.
Any method that restricts execution to nominated MAC addresses or nominated users requires that a list be maintained somewhere. For small, relatively static, lists, this can be in the VBA code itself. For longer or more dynamic lists, it would need to be in some network file.
A couple of other comments. Whatever you implement you should ensure that you do not tell people what you have done: the method relies in part on your thief not knowing what needs to be cracked. Also, it is not a bad idea to put some sort of expiry date on software even if you are using MAC addresses as your principal test: firstly a thief who half-cracks your system will hit a later disappointment; secondly your valid users will eventually be forced to ask for refreshed versions, thereby enabling you to ensure they all upgrade to your new improved version.
None of this is perfect. But you can use it to give your thief a run for his/her money.
Did it work in my case? I'll never know.
![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")
![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
