Macro Security and Digital Certificates in VBA
Part one of a two-part series of blogs

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.

  1. Security Settings for VBA Macros (this blog)
  2. Using Digital Certificates in Excel VBA

This blog is part of our Excel VBA Tutorial.  We also run excellent training courses in VBA!

Posted by Andrew Gould on 06 March 2012

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

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

Selecting macro security

Click the Macro Security option to view and change your settings.


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

Trust center

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:

Enable macro in 2010

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:

Message in 2007

First you'll have to click Options...

Enable macros 2007

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:

  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.
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:

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

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

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

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

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

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

New trusted location

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.


  1. Security Settings for VBA Macros (this blog)
  2. Using Digital Certificates in Excel VBA
This blog has 0 threads Add post