How to protect your VBA code in Excel 2010

VBA scripts are usually written by Excel experts or enthusiasts in order to achieve everything from simple automations through to highly detailed processing and report/dashboard creation.  When you write a VBA script for an Excel file you should consider protecting it from unauthorised changes, read on to learn how…

 

Protecting your VBA scripts may seem a little extreme, especially with so many IT experts now recognising the value of open source and free access but it does have a couple of very valid reason within the business environment:

  • Protection from unauthorised changes
  • Protection from getting broken

Protection from unauthorised changes

This is an important consideration within the business environment, for example if your script allowed users to enter some data, then it calculated bad debt provisions based off the businesses legal guidelines, that criteria is not something you want to allow anyone to change as the results could be catastrophic!

Protection from getting broken

If you are writing VBA scripts within an Excel file that is going out to a lot of users that have a limited understanding of Excel then it is prudent to consider whether the script is worth protecting to prevent anyone from breaking it.  This is particularly important if all users are accessing the same Excel file or the file lives on an open network as an over-zealous user could accidentally make a change that causes everything to halt across the business or worse.

Remember that with a large user base there are always going to be users that are inquisitive and decide to view your VBA script to see what is going on behind the scenes, personally I encourage that as it helps people to learn and grow their knowledge of Excel but make sure you encourage it under the right circumstances, not every file you produce should allow access to the VBA script.

How to Protect

Protection is simple and takes the form of password protecting your VBA script, this prevents users without the password from even seeing the script that is within the file.

The useful aspect of password protection is that if someone did have a good reason to look through your script, for example training or development purposes, then you can always share it with them.

Follow these steps to password protect your VBA scripts:

1)      Go into the Visual Basic mode in Excel, do so by clicking on the “Developer” tab on the Excel ribbon then on “Visual Basic”, alternatively the shortcut command is ALT+F11:

00069_Excel Protect VBA_01

2)      Within the Visual Basic Area select “Tools” from the menu bar along the top, followed by “VBAProject Properties” in the drop down box:

00069_Excel Protect VBA_02

3)      Within the VBAProject – Project Properties box select the “Protection” tab, then check the box called “Lock Project for Viewing” following by entering your password and confirmation in the relevant boxes:

00069_Excel Protect VBA_03

Tip: Use a strong password (Upper/Lower case, numbers etc. ) as small one-word passwords can be easily broken by someone with a bit of know-how.

4)      Click on OK and the VBA script is now protected. The changes will not take effect until you reopen the file so save the file, close it down and then open it back up.  You will now find if you go into the Visual Basic area everything will appear blank and trying to view anything results in the password prompt:

 

 

00069_Excel Protect VBA_04

Summary

  • Consider the potential implications and risks associated with allowing access to your VBA script and take a decision on whether you need to protect the it from unauthorised changes.
  • Always try to retain an open-source and free access perspective in business as this can help people grow their knowledge of Excel but some projects can potentially have huge financial, legal and data security implications to a business, in those cases protection of scripts and files should always be the starting stance.
  • You can use some form of password management if there are additional users in the business that need to view the script.

 

Keep Excelling,