Understanding macro security is essential if you want the code you've written to run when it's supposed to, and at the same time protect yourself from potentially malicious code. This blog explains how to change your security settings to an appropriate level and discusses the ins and outs of digital certification.

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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

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

There are many settings that you can change here, but we'll focus on the
Macro Settings tab to begin with.

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:

Security level

Description

Enable all macros

This setting allows any macros to run as soon as a file is opened. This
includes potentially dangerous code that is attached to the Open
event of the workbook, so beware!

Disable all macros except digitally signed macros

You won't be able to run any macros unless they have a trusted digital
certificate attached. We'll see how to create a personal certificate later
in this blog.

Disable all macros with notification

When you open a file all the macros will be disabled, but you'll be presented
with a message allowing you to enable them each time.

Disable all macros without notification

All macros will be disabled and you won't even be told why!

The macro security settings shown here only apply to files that aren't stored
in trusted locations, or have not been made a trusted document.

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 2010 you simply need to click the
Enable Content button to allow your macros to run.

In Excel 2007 the message is slightly different:

First you'll have to click
Options...

You'll then need to choose
Enable this content and finally click
OK.

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:

From the ribbon choose: Developer -> Macro Security.

On the dialog box, select the Trusted Documents
tab.

Check the option to Disable Trusted Documents.

You can also click
the Clear button to remove any documents that you have previously inadvertently trusted.

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:

From the ribbon select: Developer -> Macro Security.

On the dialog box, select the Trusted Locations
tab.

Use this dialog box to create and modify trusted locations on your computer.

You can see a list of existing locations in this area.

To create a new location, click here.

Enter the path for the folder that you want to trust, or
click the Browse... button to look for one.

Use this dialog box to set up a new trusted location.

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

Click OK.

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

You can remove or modify the trusted location by selecting it and then using the appropriate button.

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.