Download the best tutorial, website and reference tool on Excel

Lesson 10: VBA Security and Protection in Excel

Sometimes when you send a workbook with macros in it to a colleague and he can't get them to work it is probably because his security setting is at "High". Just tell him how to change it in "Tools/Macros/Security".

An Excel file (.xls) cannot be infected by one of these viruses that appear regularly on the Internet but somebody can develop VBA procedures (macros) that can harm your data and your computer seriously. So set the security level of Excel to "Medium" (Tool/Macro/Security) and each time you will be trying to open a workbook that contains macro the following dialog window will appear.

Adopt the same attitude as you have with documents attached to Emails. If you know the origin of the file you may enable the macros if not click on "Disable Macros" and you are fully protected. You can look at the workbook but the VBA procedures (macros) are not operational. You can go to the Visual Basic Editor to take a look at the macros and if they don't look suspicious close the workbook and re-open it enabling the macros. If in any of the modules or other elements of the workbook you see "API Function" it means that the programmer is trying to access your computer through Microsoft Windows THAT IS SUSPICIOUS.

Protecting the code

As an Excel-VBA Developer you might want to protect your code so that nobody else may modify it. In the VBE editor go to "Tools/VBAProject Properties/Protection". Check the box and submit a password. Make sure that you save the password somewhere you will remember because cracking VBA passwords is very expensive.

Protecting the Workbook

There are many levels of protection that you can set for the workbook. First you might want to forbid anybody from opening the workbook unless they know a password. To do so in Excel go to "File/Save As" and click on "Tools/General Options".

Hiding Worksheets

In one of your workbook you might want to hide a sheet that contains confidential information (salaries and other parameters). If you just hide the sheet users can unhide it. There is a VBA way to hide a sheet without protecting the entire workbook. To view the sheet  one has to go to the Visual Basic Editor and change the property of the sheet. If your code is protected, he also needs the password. So the sheet is very hidden and only you can get to it to modify its content.  Select the sheet in the VBAProject window and set the visible property at xlVeryHidden

Download the best tutorial, website and reference tool on Excel