How can I have a shared Excel spreadsheet with an user-defined function while making sure nobody adds any other VBA code?

Posted by:

You can have a shared excel spreadsheet with an user-defined function while making sure nobody adds any VBA code. How? This way, KulfiSoftware is providing not one, two, three but, four ways to make the excel spreadsheet protected and with this no one can change the VBA code.

There are four ways are:

I know of to make sure somebody hasn’t added any other VBA code to your workbook.

The minimalist approach would be adding your digital signature to the workbook. This signature will be removed automatically if anybody adds or changes code in your workbook.

So you can look at a copy of the workbook and know whether somebody has been messing with the code. It won’t impede them from doing so, however. Assuming you have a digital signature, you add it using the Tools…

Digital Signature menu item in the VBA Editor. If you distribute the workbook widely, you will probably be wanting to purchase a digital signature from a commercial signing authority.

It should cost you a couple of hundred dollars. If you are just distributing the workbook to a handful of people, it is possible to create a self-certified digital signature for free—but then you need to install that signature on each user’s workstation for them.

You could protect your VBA project with a password.

This will stop the average person from attempting to tamper with your VBA code but can be circumvented by a motivated hacker. You add password protection using the Tools…VBAProject Properties menu item.

Go to the Protection tab in the resulting dialog, and check the box to protect the project for viewing. Note that commercial software and certain manual methods are capable of removing the password protection.

For more robust protection, consider making the VBA code unviewable. You do this with a $100 software package called Unviewable + sold by Petros Chatzipantazis.

You can’t add code if you can’t see any of the VBA code panes. Users do not need to buy or install anything. The protection cannot be broken using any of the commercial software packages.

Finally, you could compile the workbook and its VBA code into a .exe file. There may be more vendor, but the people at Calc4Web have been offering a package for over a decade that converts worksheets, formulas and VBA code into C++ code that is then compiled into a .exe file.

When you run the .exe file, the user interacts with it just like it was an Excel workbook. Besides protecting your intellectual property and preventing tampering, the .exe file recalcs much faster than the native Excel workbook.


Add a Comment