Security Settings for VBA Macros

If you want your VBA macros to run when they should you need to understand how security settings work in the Office application you're using.  This blog uses Microsoft Excel 2010 to show you how to change security settings, create trusted locations and assign digital certificates to your code.  The settings for Excel 2007 are very similar.

Viewing Macro Security Settings

The easiest way to view the current macro security settings is to use the Developer tab of the ribbon.

Once you've clicked the button shown above you'll be in the Orwellian-sounding Trust Center dialog box.

The next step is to choose from one of the four levels of macro security.

The Four Levels of Macro Security

In Excel VBA there are four levels of macro security, as described in the table below:

In my opinion the most sensible choice is to Disable all macros with notification.  This gives you the flexibility to choose what you want to do each time you open a file without leaving you at the mercy of malicious macros that run automatically.

The message that you'll see when you use this setting in Excel 2010 is shown below:

In Excel 2007 the message is slightly different:

Trusted Documents

Trusted documents is a new feature in Excel 2010 that allows you to tell Excel that some files should always have their macros enabled, regardless of the level of macro security.

This would be a reasonably useful feature if you had any control over which documents should be trusted, but unfortunately the system doesn't work like that!  Instead, whenever you click the Enable Content button after opening a file, that file will automatically become a trusted document.  This means that the macros contained in that file will always be allowed to run, even if the macros are subsequently changed.

In my opinion the only useful thing you can do with trusted documents is to disable them!  You can do this from the Trust Center:

1. From the ribbon choose: Developer -> Macro Security.
2. On the dialog box, select the Trusted Documents tab.
3. Check the option to Disable Trusted Documents.

Trusted Locations

A slightly better idea then trusted documents is trusted locations.  As the name suggests, these are locations that you can mark as "trusted" and so enable macros in any files that are stored there.

Some locations on your computer will be trusted by default, but you can also add your own custom locations.  To do this:

1. From the ribbon select: Developer -> Macro Security.
2. On the dialog box, select the Trusted Locations tab.

3. You can see a list of existing locations in this area.
4. To create a new location, click here.
5. Enter the path for the folder that you want to trust, or click the Browse... button to look for one.

6. Choose whether you'd like to trust files stored in folders within this folder.
7. Click OK.

Your new trusted location will appear in the list of existing locations:

What's Next?

Using trusted locations is a neat way to control which of your macros will always run without having to click the Enable Content button each time you open a file.  The next part of this series shows you one other way to do this; using digital certificates to verify that your code is safe.

 

Comments on this blog

This blog has one comment: