Macro Security and Digital Certificates in VBA
Part two 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
  2. Using Digital Certificates in Excel VBA (this blog)

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.

Using Digital Certificates in Excel VBA

A digital certificate is like a security tag that you can attach to your VBA projects to verify that they are safe.  They sound like a fantastic idea in theory, but in practice they have one major limitation which makes them virtually redundant.

Digital Certificates for VBA Projects

When you install Microsoft Office on a computer there's a set of extra tools that are installed along with the applications.  One of these is the Digital Certificate for VBA Projects tool.

Certifcates tool

Somewhere in your Start menu you should find this option.

 

If you can't find the option in the Start menu it could be that you don't have the tool installed.  You can install it by running the setup program from your Microsoft Office installation media.

Changing installation

Choose to Add or Remove Features from your Office installation and then click Continue.

 

On the next step of the wizard you can choose which features you want to modify:

Installing Certificates

Choose the option shown here and then click Continue to install the Digital Certificate tool.

Creating a Digital Certificate

Once you have the feature installed, you can create a certificate by running the program from your Start menu:

Creating certificate

Simply type in a sensible name for your certificate and click OK.

If everything works properly you should see a confirmation message like so:

Successfully created certificate

Click OK to confirm the creation of your certificate.

 

Attaching a Certificate to a Project

Once you've created a certificate, the next step is to attach it to a VBA project.  You'll need to be in the VB Editor in order to do this:

  1. From the menu select: Tools ->Digital Signature...
Choosing certificate

This dialog box allows you to choose a certificate for this project.

 
  1. Click the Choose... button to select a certificate.
List of certificates

This dialog box provides you with a list of certificates.

  1. Select the certificate you want to assign to the project and click OK.
Assigned certificate

The dialog box will now show you which certificate has been assigned to the project.

 
  1. Click OK once more to finish assigning a certificate to the project.

Trusting a Digital Certificate in Excel 2007

In order for your certificate to do its job, you must choose to trust it.  To do this in Excel 2007:

  1. Set your macro security level to Disable all macros except digitally signed macros or Disable all macros with notification.
Setting security level

Choose either the second or third option in this list.

 
  1. Save the file, then close and reopen it.
  2. When the file opens, click the Options... button.
Choose options

Click here to see further options.

 
  1. Choose the option shown in the diagram below and click OK to always trust macros when your digital certificate is attached to a project.
trusting a certificate

Choose the option shown to always trust macros with this digital certificate attached.

Trusting a Digital Certificate in Excel 2010

Trusting a certificate in Excel 2010 is slightly more complicated.  To do this:

  1. Set your macro security level to Disable all macros except digitally signed macros or Disable all macros with notification.
Security level

Choose either the second or third option in this list.

 
  1. Save the file, then close and reopen it.
  2. Don't choose to Enable Content at this point!
Don't click this button

Don't click this button! Otherwise you'll have to close and reopen the file again.

  1. From the ribbon select: File -> Info.
Extra options

Here you'll be able to choose extra options related to the macros in the file.

  1. Click the Enable Content button and choose Advanced Options.
Choosing advanced options

Click here to see further options related to macro security.

 
  1. Choose the option shown in the diagram below and click OK to always trust macros when your digital certificate is attached to a project.
Trusting a certificate

Choose the option shown to trust the attached certificate.

Viewing the List of Trusted Publishers

If you decide that you want to stop trusting a particular digital certificate you can remove it from the list of trusted publishers.  To do this:

  1. From the ribbon choose: Developer -> Macro Security.
  2. Select the Trusted Publishers tab on the dialog box.
Remove certificate

This tab of the dialog box shows a list of all the certificates that are currently trusted on this computer.

  1. Select the certificate you want to remove.
  2. Click Remove.

Removing a certificate from this list doesn't delete the certificate, nor does it remove the certificate from the VBA project.

The Advantage of Using a Trusted Certificate

The whole point of using a trusted digital certificate is that it allows you to keep your security settings at a relatively high level but still allow the code that you know is safe to run.

Security level

With a trusted digital certificate attached to your projects you can use this security level, safe in the knowledge that the macros you have marked as trusted will still work.

 

The Limitation of Digital Certificates

The single biggest limitation of a digital certificate, and one that many people find surprising, is that you can only trust a certificate on the computer on which it was created.  This means that if you send a file to someone else with your digital certificate attached they won't be able to trust it.

This sounds very limited, but it is the way that Microsoft intended for this system to work.  A digital certificate that you create yourself is intended for personal use only.  It allows you to create your own code, or look at someone else's, and when you're satisfied that it's safe you can indicate that by attaching your self-signed certificate.

Message

This information does appear when you choose to create a certificate, but it's easy to miss.

If anyone could create a certificate that could be trusted on any computer there would be absolutely no guarantee that the code contained in a file was safe.  What if you wanted a certificate that you could share around your office or organisation however?  It is possible to obtain a certificate that can be trusted on other computers, depending on how much money you're willing to spend...  Look for this link when you choose to create a new certificate:

Commercial certificates

Click here if you have money to burn.

 

The link shown above will take you to a help page on the Microsoft web site which lists companies from whom you can buy digital certificates.  The last time I checked the page was full of irrelevant (tourist information for Boston anyone?) or dead links.  One company which does still provide certificates for sale is VeriSign (now part of Symantec), and if you have around 800 pounds or 1300 dollars to spare you can buy a certificate that will last for three years!

Verisign

Wise Owl's opinion: not worth it!

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