Export (0) Print
Expand All
Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services
Testing: Get Your Customers Involved in the Testing Process with Functional Tests in Excel
Expand Minimize
409 out of 415 rated this helpful - Rate this topic

Understanding Security for Add-ins and VBA Macros in Excel 2003

Office 2003
 

Siew Moi Khor
Microsoft Corporation

May 2005

Applies to:
    Microsoft Office Excel 2003

Summary: Learn how to use Excel 2003 security settings and options to mitigate the risks of loading malicious components, check the identity of their publishers, and help your users make explicit trust-based decisions. (12 printed pages)

Contents

Introduction
Excel Security Options Matrix
Understanding Excel 2003 Security Prompts
Understanding the "Trust All Installed Add-ins and Templates" Option
Additional Resources

Introduction

Microsoft Office Excel 2003 provides a number of security options that can help reduce the risk of users loading malicious components. Whether or not Excel 2003 will load or disable an Excel add-in or Microsoft Visual Basic for Applications (VBA) macro depends on many factors.

This article demonstrates how Excel 2003 security features mitigate the risks of loading malicious components by checking the identity of their publishers and letting users make explicit trust-based decisions, depending on users' specific security settings.

Excel Security Options Matrix

Table 1 and Table 2 describe the behavior of Excel 2003 when loading macros and unmanaged-code COM add-ins (those built using Microsoft Visual Studio 6.0).

Important   If you want information about managed-code COM add-ins (those built using Microsoft Visual Studio .NET), see Isolating Office Extensions with the COM Shim Wizard and Deployment of Managed COM Add-ins in Office XP for a detailed explanation of how security checking is done and how managed-code COM add-ins are loaded.

Note that the availability of the Security dialog box varies depending on the specific Office application. The options within this dialog box also can vary, depending on the application. To open the Security dialog box, click the Tools menu, point to Macro, and click Security (see Figure 1).

Excel 2003 security options

Figure 1. Excel 2003 security options

Note   The Prior Trusted Sources tab is visible only if you added a trusted certificate in a previous version of Office, using the Office (not Microsoft Internet Explorer) trust storage location.

Additionally, specific Office applications silently load macros only from specific directories. For example, selecting the Trust all installed add-ins and templates check box will allow all macros that are stored in certain "trusted" personal or workgroup locations to run, whether or not they are code signed. Not all Office applications support the "trusted locations" option for macros.

Note   The term trusted locations applies only to macros, and refers to folders that users or administrators choose to trust. Not all Office applications support trusted locations, but certain folders are trusted by default. The trusted locations apply only when you select the Trust all installed add-ins and templates check box. When you select this check box, all macros that are stored in certain personal or workgroup "trusted locations" will load, whether or not they are code signed. To view some of these locations in Excel, open the Tools menu, click Options, and then click the General tab as shown in Figure 3. The box titled At startup, open all files in shows the trusted location.

Figure 2 shows the Trust all installed add-ins and templates check box, which is located on the Trusted Publishers tab of the Security dialog box.

The "Trust all installed add-ins and templates" option

Figure 2. The "Trust all installed add-ins and templates" option

To view trusted locations in Excel, open the Tools menu and click Options. Figure 3 shows the Options dialog box. Click the General tab; the trusted location appears in the At startup, open all files in field. In Figure 3, no trusted location is specified.

You can open all files at startup from a specific location

Figure 3. You can open all files at startup from a specific location

For more information about the Trust all installed add-ins and templates option, see the later section, Understanding the "Trust All Installed Add-ins and Templates" Option.

Table 1. Options in the Security dialog box, shown in Figures 1 and 2

Options General Description
Very High The Very High security level is new in Office 2003. If you select this security level and clear the Trust all installed add-ins and templates check box, all add-ins and macros are disabled, whether or not they are signed, and whether or not they are from trusted publishers.

If you select the Trust all installed add-ins and templates check box, all installed add-ins will run. However, whether or not a macro will be allowed to run depends on whether or not it is saved in a trusted location.

High If you select the High security level and clear the Trust all installed add-ins and templates check box, all unsigned add-ins and macros are disabled. If they are signed, and the publisher of the add-ins or macros is not in the Trusted Publishers list, users are prompted to either trust the publisher or disable the add-ins or macros. Users do not have the option to run the add-in or macro on a per-application-session basis. The only option is to either trust or not trust the publisher.

If you select the Trust all installed add-ins and templates check box, all installed add-ins will run, whether or not they are signed. Macros (signed and unsigned) will run if they are saved in a trusted location.

Medium If you select the Medium security level and clear the Trust all installed add-ins and templates check box, users are prompted to either enable or disable all unsigned add-ins and macros. If they are signed and the publisher of the add-ins or macros is not in the Trusted Publishers list, users are prompted to either trust the publisher or enable the add-ins or macros on a per-document-session basis.

If you select the Trust all installed add-ins and templates check box, all installed add-ins will run, whether or not they are signed. Macros (signed and unsigned) will run if they are saved in a trusted location. If they are not located in a trusted location, users are prompted to enable or disable the macros. Then they can run the macros only for the current application session.

Low If you select the Low security level, all add-ins and macros will load, whether or not you select the Trust all installed add-ins and templates check box, whether or not the add-ins or macros are signed, and whether or not the macros are saved in a trusted location. If you set your Excel security level to Low, it is like choosing not to have any security.
Trust all installed add-ins and templates If you select the Trust all installed add-ins and templates check box for a particular Office application, all installed and registered add-ins are considered trusted, whether or not they are signed. Additionally, if the application supports trusted locations, all installed macros in the trusted locations are considered trusted and will load, whether or not they are signed.

Table 2 describes the security setting options in detail. It explains how selecting or not selecting a specific option affects whether or not an add-in or macro will load.

Note   In Table 2, the Yes or No entries indicate that, whether or not the add-in or macro is digitally signed (or whether or not the add-in or macro is from a trusted publisher, or whether or not the macro is in a trusted location), Excel treats the add-in or macro the same for that particular security setting.

Table 2. Security setting options matrix

Security level Trust all installed add-ins and templates check box Add-in or macro is digitally signed Add-in or macro is from a trusted publisher Macro is in a trusted location Excel treatment of macros Excel treatment of add-ins
Very High Selected Yes or No Yes or No Yes Loads the macro silently. Loads the add-in silently.
  Selected Yes or No Yes or No No Does not load the macro. Users are alerted that the macro was disabled, as shown in Figure 4. Loads the add-in silently.
  Cleared Yes or No Yes or No  Yes or No Does not load the macro. Users are alerted that the macro was disabled, as shown in Figure 4. Does not load the add-in.
High Selected Yes or No Yes or No Yes Loads the macro silently. Loads the add-in silently.
  Selected Yes or No Yes or No No Does not load the macro. Users are alerted that the macro was disabled, as shown in Figure 5. Loads the add-in silently.
  Cleared Yes Yes Yes or No Loads the macro silently. Loads the add-in silently.
  Cleared Yes No Yes or No Prompts to disable macros or always trust publisher. Prompts to disable add-in or always trust publisher.
  Cleared No Not Applicable Yes or No Does not load the macro. Does not load the add-in.
Medium Selected Yes or No Yes or No Yes Loads the macro silently. Loads the add-in silently.
  Selected Yes or No Yes or No No Prompts to disable or enable the macro. Loads the add-in silently.
  Cleared Yes Yes Yes or No Loads the macro silently. Loads the add-in silently.
  Cleared Yes No Yes or No Prompts to disable or enable the macro. You can enable the macro for the current session or choose to always trust the publisher. Prompts to disable or enable the add-in. You can enable the add-in for the current session or choose to always trust the publisher.
  Cleared No Not Applicable Yes or No Prompts to disable or enable the macro. Prompts to disable or enable the add-in.
Low Selected Yes or No Yes or No  Yes Loads the macro silently. Loads the add-in silently.
Low Cleared Yes or No Yes or No Yes or No Loads the macro silently. Loads the add-in silently.

Alert message explaining why a macro with a Very High security setting was disabled

Figure 4. Alert message explaining why a macro with a Very High security setting was disabled (click to see larger image)

Alert message explaining why a macro with a High security setting was disabled

Figure 5. Alert message explaining why a macro with a High security setting was disabled (click to see larger image)

Understanding Excel 2003 Security Prompts

The way that Excel treats add-ins that are installed and registered, as well as the macros that are installed on a client computer, depends on the following variables:

Is the "Trust all installed add-ins and templates" check box selected?

If you select this check box, Excel loads add-ins silently—that is, without any security prompt. If you clear this check box, whether or not users are prompted depends on how they have configured Excel security settings, as described in Table 1 and Table 2.

Note   By default in Excel 2003, security is set to High and the Trust all installed add-ins and templates check box is selected.

For more information about the Trust all installed add-ins and templates option, see the topic in this article, Understanding the "Trust All Installed Add-ins and Templates" Option.

Is the add-in or macro digitally signed?

It is strongly recommended that developers sign their add-ins and macros using certification authorities such as VeriSign or GTE before distributing them. Security has become an integral part of most organizations' overall IT strategies. Many security-conscious users and administrators set their Office application security level to High, and then clear the Trust all installed add-ins and templates check box, which is highly recommended.

Digitally signing add-ins and macros has many benefits. By digitally signing the add-ins and macros you intend to publicly distribute, users can be assured of your identity and confident that the add-ins and macros were not altered since they were created. In addition, as described in Table 1 and Table 2, when you set the security level to High and clear the Trust all installed add-ins and templates check box, a signed and trusted add-in loads if it is from a trusted publisher or source, and a non-signed (which makes it non-trusted) add-in is disabled automatically. The only time users are prompted to either enable or disable a component with this security setting is when the certificate used by the software publisher to sign the add-in or macro is not in the Trusted Publishers list (see Figure 2).

Are the add-ins and macros from a trusted publisher?

Excel 2003 can identify sources that a user or an administrator has identified as trustworthy. Sources can include the user's own organization, for internally developed add-ins and macros, or another company, such as Microsoft. (To display the Trusted Publishers list, open the Tools menu, point to Macro, and then click Security. Then click the Trusted Publishers tab.) Depending on the security configurations shown in Table 1 and Table 2, Excel 2003 handles the add-ins and macros in one of the following ways:

  • Loads the add-in or macro into memory silently.

    The add-in or macro loads into memory without a security prompt or user intervention.

  • Shows a security prompt to enable or disable a signed add-in or macro.

    The user is asked to disable or enable the add-in or macro that was digitally signed, as shown in Figure 6.

    Users can disable add-ins or choose to trust a publisher

    Figure 6. Users can disable add-ins or choose to trust a publisher

    This message also alerts the user that the software publisher is not in the Trusted Publishers list. Additionally, the message includes a check box that allows the user to identify the source of the add-in or macro as one that should be trusted in the future. This is a generic security warning that applies to all digitally signed add-ins and macros.

  • Does not load the component into memory.

    This is done silently, but users are alerted that the add-in was disabled, as shown in Figures 4 and 5.

  • Shows a security prompt to enable or disable an unsigned add-in or macro.

    The user is asked to disable or enable the add-in or macro. Note that this security prompt is different from the one in Figure 6. This is because the add-in or macro is not digitally signed, which means it does not have a digital certificate that identifies the software publisher.

    The user may choose to enable the add-in or macro, in which case that specific add-in or macro loads. If the user chooses to disable the add-in, it does not load into memory. If the user chooses to disable the macro, it loads into memory but in a disabled state. Unless the user changes security settings, the message in Figure 7 will appear each time the supporting application opens.

    Users can disable or enable unsigned add-ins or macros

    Figure 7. Users can disable or enable unsigned add-ins or macros

Understanding the "Trust All Installed Add-ins and Templates" Option

The Trust all installed add-ins and templates check box (see Table 1) is commonly misunderstood. It is enabled by default, but Microsoft recommends that customers with very high security requirements disable it, and this is a good "defense in depth" approach.

If you have no need to run unsigned personal macros or unsigned COM add-ins, you should turn off this option. As shown in Tables 1 and 2, when you set the security level of an Office 2003 application to High, and clear the Trust all installed add-ins and templates check box, all add-ins and templates that are not code signed are automatically disabled. And if the add-ins and templates are code signed with a certificate not listed in the Trusted Publishers list, users are prompted to either enable or disable the add-ins or templates.

Selecting the Trust all installed add-ins and templates check box allows all COM add-ins that have been installed in the registry (which requires administrative privileges) or macros that are stored in personal or workgroup locations to run, whether or not they are code signed. It should also be noted that users do not need administrative privileges to install macros to certain template and startup folders. Here are two examples of these locations in Excel:

  • Documents and Settings\user name\Application Data\Microsoft\Excel\XLSTART
  • Program Files\Microsoft Office\Office11\XLSTART

If you select the Trust all installed add-ins and templates check box, your computer can be attacked if you download, register, and run a malicious COM add-in, or if you run a malicious template that someone else placed in a trusted location. Templates do not have to be registered in order for them to load.

The following procedure explains how to run personal macros or locally installed COM add-ins, in the case that you do not want to purchase a digital certificate, but you do want to disable the Trust all installed add-ins and templates option.

  1. Sign your personal macros or locally installed COM add-ins with a test certificate (by using selfcert.exe for macros, or Authenticode tools for add-ins). This way, you can keep your security settings at High, and you can disable the Trust all installed add-ins and templates option. For more information about how to generate a test certificate, refer to the Digital Code Signing Step-by-Step Guide.
  2. Set your Office application security level to Medium and clear the Trust all installed add-ins and templates check box. With this particular security setting, you are prompted to either disable or enable COM add-ins or macros when the application starts. It is strongly recommended that you do this only in a testing environment.

It is recommended that you also read 10 Immutable Laws of Security on the Microsoft TechNet Web site.

Additional Resources

If you want to know more about security, explore the following articles:

Acknowledgement

Many thanks to my colleagues, Ambrose Treacy, Lawrence Landauer, and Dan James for their guidance and help in writing this article.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.