Macro security settings determine how permissive Excel should be about allowing macros to be run on your computer. There are four security levels: Very High, High, Medium, and Low. You control these in the Security dialog box (Tools menu, Options command, Security tab, Macro Security button), as shown in the following illustration.
When to use Low and Medium macro security
You should cross Low macro security off your list of acceptable macro security settings. Even if you have an all-but-foolproof virus scanner, allowing even one virus to get through is one too many. No software can know what macros you are or aren’t expecting, so you need to make sure there’s an active human in the security loop. That human is you.
The next level of macro security is Medium. At this level, whenever you open a workbook that contains macros, Excel displays a dialog box asking if you want to enable macros. You have the following options:
- Click Disable Macros to open the workbook but prevent the macros from being run.
- Click Enable Macros to open the workbook and allow the macros to run in the workbook.
- Click More Info if you need help making your decision.
- Close the Security Warning dialog box to prevent the workbook from being opened at all.
If you need to work with a lot of macros, and you are the only person who uses your computer, and you are confident you’ll remember not to click Enable Macros if you get an unexpected file (or a file you didn’t expect to contain macros), you may want to use Medium macro security from time to time. However, it’s not recommended that you use the Medium setting on a regular basis. Also, if you share your computer with other users, you should strongly consider changing the macro security setting to High.
When to use High macro security
High macro security is the recommended macro security setting for all users. You should consider using this level of macro security if you work in an environment where you don’t need to write any of your own macros and the only add-ins you would consider adding are either included with Excel (for example, the Analysis ToolPak) or are add-ins you have written yourself and signed with a digital certificate.
This setting requires that an add-in or macro be digitally signed by a trusted publisher, or be installed in a trusted location, before the macro will be allowed to run. A trusted publisher (called a trusted source in previous versions of Excel) is an entity that:
- Obtains a digital certificate from a certificating authority.
- Signs the macro with that digital certificate.
- Has gained your trust.
Most often you choose to trust an individual or corporation with a digital certificate by selecting a check box. For example, the Always trust content from Microsoft Corporation check box appears in a dialog box when you’re asked to install an add-in to Excel or a plug-in for Internet Explorer.
When your macro security is set to High, Excel will run only those macros that are digitally signed or stored in a trusted location, such as the Excel startup folder. Permissions should be applied to all trusted locations, so that only administrators can manage the files that are stored there.
Note Unsigned macros stored in the Excel startup folder will only run when you first start Excel. They will not run when you open the file in Excel by clicking Open on the File menu.
For unsigned macros to run, the Trust all installed add-ins and templates check box needs to be selected on the Trusted Publishers tab of the Security dialog box. This option is selected by default, as shown in the following illustration. If it is not selected, Excel will only run macros with trusted digital signatures. Digital signatures and certificates are covered in some depth later in this article.
The Trusted Publishers tab also provides the Trust access to Visual Basic Project check box, which is cleared by default so that Excel can warn you about macros that are trying to access the Visual Basic Project.
When to use Very High macro security
The highest level of macro security, Very High, prevents Excel from running any macros or add-ins. Only macros in a specific folder on your computer are trusted, whether they are digitally signed or not. This folder must have folder or file level permissions set, for security purposes.
Unless you are an administrator who controls the degree of security at the folder or file level, you won’t need to use Very High macro security.
Working with digital signatures
The mechanics of High macro security rely in part on the use of digital signatures to verify the source of the VBA code in a workbook. A digital signature is the result of using the principles of public-key encryption techniques to create a unique combination of the signed material and another file that is known only to you — the result of which can be verified as having been signed by you. The mathematics are quite complex, relying on group theory and other disciplines only a handful of researchers really understand, but here’s a synopsis of how it works.
Generating encryption keys
The first step is for you to generate a key. It will be used to encrypt your data by adding the values in the key to the values in the text. A simple example would be if your key were the letter a and the word to be encrypted were kazoo. If you “add” a (the first letter in the alphabet) to each letter in kazoo, each letter moves one place up in the alphabet, resulting in the encrypted word lbapp. The key you’ll actually generate is much, much longer, and it’s split into two parts:
- Private key A private portion of the encrypted key, called the private key, is to be kept secret; you don’t need to share it with anyone else — and anyone who asks you to do so does not have your best interests at heart.
- Public key A public portion of the encrypted key, called the public key, can and should be distributed to anyone with whom you’ll exchange digitally signed files. The trick behind public-key encryption is that anyone who has a copy of your public key can verify that a document you signed using your private key is from you and is unchanged since you signed it. It’s important to note that anyone who attempts to sign a file using your public key will not appear to be you.
Establishing trust outside your organization
Depending on the encryption software package you use, you might be able to use your public-key and private-key pair to digitally sign workbooks and macro code that you distribute over your internal network. The problem with using key pairs that aren’t distributed outside your organization is that no one outside your network will have any idea whether your signature is valid or not. If you need to work with individuals outside your corporate network, you can obtain a digital certificate from a trusted third-party vendor. A digital certificate is an electronic file that identifies you, and contains information such as:
- Your organization’s name.
- The certificate’s issuing certification authority.
- Your e-mail address and country.
- The certificate’s expiration date.
- A copy of your public key.
After you sign a document using a digital certificate, anyone who wants to verify that certificate can go to the key server maintained by the issuing authority and match the signature to your public key.
You can find a list of participants in the Microsoft Root Certificate Program on the Microsoft Developer Network (MSDN) Web site.
Create your own digital certificate
If you don’t want to take the plunge and purchase a digital certificate from a commercial provider, you can create one of your own by using the Office helper application, Selfcert.exe.
- On the Windows Start menu, click All Programs.
- Click Microsoft Office, point to Microsoft Office Tools, and then click Digital Certificate for VBA Projects.
Note Bear in mind that this home-grown certificate can only be used on your own computer. Other users who open a file you signed with Selfcert.exe won’t have the ability to trust the certificate you created.
Digitally signing workbooks and macros
After you have acquired a digital certificate from a certification authority, you can digitally sign a workbook by using the Select Certificate dialog box.
Digitally sign a workbook
- On the Tools menu, click Options.
- On the Security tab, click Digital Signatures.
- Click Add.
- Do one of the following:
- In the Select Certificate you want to use box, select the name of the certificate with which you want to sign your workbook.
- To view the details of the certificate in case some of your certificates have similar names, click View Certificate.
Digitally sign a macro
- On the Tools menu, point to Macro, and then click Visual Basic Editor.
- In the Visual Basic Editor, on the Tools menu, click Digital Signature.
- Click Choose, and then do one of the following:
- In the Select Certificate you want to use box, select the name of the certificate with which you want to sign your macro.
- To view the details of the certificate, in case some of your certificates have similar names, click View Certificate.
Note The Select Certificate dialog box that appears when you sign your VBA code may contain certificates that have expired. Make sure the certificate you select is current, because signing a macro with an expired (that is, invalid) certificate is equivalent to not signing it at all.
The macro security settings available to you in Excel may seem confusing at first, but the guidelines for working with macros can be summarized like this:
- Update your virus protection software frequently.
- Never open a workbook (or other document) that you didn’t expect and don’t recognize.
- Never use the Low macro security setting.
- Use the Medium macro security setting occasionally if you work with macros frequently and want to choose whether or not to run macros each time you open a workbook.
- Use the High macro security setting on a regular basis as the recommended setting to prevent any macro from an untrusted source from running on your computer.
- Use the Very High macro security setting to prevent all macros from running on your computer unless they are located in a specific folder.
- Acquire a digital certificate if you need to send trustworthy workbooks outside your organization.
Recommendation for optimal macro virus protection
For optimal macro virus protection and assurance that all macros will run, you should do all of the following:
- Set the macro security level to High.
- Digitally sign all macros (by using Selfcert.exe or by acquiring a digital certificate).
- Clear the check box Trust all installed add-ins and templates on the Trusted Publishers tab of the Security dialog box.