Section 6.10.Set Macro Security

6.10. Set Macro Security

Excel controls whether workbook macros are allowed to run through security settings. Users may choose to prohibit all macros, allow only signed macros from known sources, allow macros of the user's choosing, or allow all macros. These settings correspond to the Very High, High, Medium, and Low security settings on the Security dialog box (Figure 6-28).

6.10.1. How to do it

To set macro security:

1. Open a workbook.

2. Choose Tools Macro Security. Excel displays the macro security setting (Figure 6-28)

Figure 6-28. Choosing macro security settings

These settings are driven by trustthe user must choose whether or not to trust a publisher or a workbook. There is no way for the user to prohibit certain operations, such as reading or writing to the registry or erasing data files. Users only discover if their trust is misplaced after the damage is done.

For this reason, it is a good idea to encourage users to be suspicious of macros arriving in workbooks. It is a better idea to deploy macros as digitally signed templates or add-ins and to distribute those files from a secure network location.

6.10.2. How to distribute macros

The following scenario demonstrates how to distribute macros in a secure fashion:

1. Set up a public network share. For example, \\Wombat1\Public\Templates.

2. Set Windows security on the Templates folder to allow read-only access to all network users and read/write access to the Administrator (in this case, you).

3. Digitally sign templates and add-ins using a CA-issued digital certificate.

4. Copy the templates and add-ins to the public Templates folder.

5. Add the public Templates location to the alternate startup path for each Excel user.

6. For each user, open one of the signed templates in Excel and select Always trust macros from this publisher on the Security Warning.

7. Select the High or Very High macro security options for each Excel user.

To set the alternate startup path in Excel, set the At startup open all files in: text box in the Options dialog box as shown in Figure 6-29.

To set the alternate startup path in code, use the following line:

    Application.AltStartupPath = "\\wombat1\public\templates"

Now, when users start Excel, templates and add-ins from \\Wombat1\Public\Templates are available automatically. If a file changes, the user will get the latest version. And since the files are digitally signed by a trusted publisher, users won't see the macro security warning every time they open a file.

---

Tip: SmartTags are provided through a type of add-in, so macro security settings apply to them as well as the other types of files that can contain code (workbooks, templates, etc.).

---

Figure 6-29. Setting the alternate startup path to a secure network location

6.10.3. How to set ActiveX control security

Excel workbooks may contain ActiveX controls that execute code or respond to macros. ActiveX controls may be digitally signed and are marked by the publisher as to whether they are safe to initialize and safe to script. In this case, safe means that the control will not harm the user's system.

Whether Excel will download or run any new ActiveX control is determined by security settings in Internet Explorer. To see these settings in Internet Explorer:

1. Choose Tools Internet Options and click on the Security tab.

2.