Configuring Macro Security

Macros execute code, and code can serve evil ends as well as good ones. If your system is permitted to run all macros, regardless of their source, you might inadvertently run a macro that will damage your system in some way. Because VBA macros included in Microsoft Office documents (typically attached to e-mail messages) have occasionally served as virus vectors in recent years, Microsoft no longer permits VBA code to run by default. You have to take steps to enable macro execution.

Like other security configuration settings, the settings that permit or deny macro execution are in the Trust Center. To get there, click the Microsoft Office Button, and then click Excel Options. In the Excel Options dialog box, select the Trust Center category, and then click Trust Center Settings.

For more information about the Trust Center, see "The Trust Center" on page 103.

How you configure macro security depends on how you expect to use macros, the degree to which you are concerned about potentially malicious macro code, and perhaps the security policies of your organization. If your organization's IT staff has disabled access to the Trust Center, as well as macro execution, this discussion is moot (unless you can convince someone to relax the rules). Assuming that's not the case, your first stop in the Trust Center should be the Macro Settings category, where you will see something like the following:

The four options in the Macro Settings area of this dialog box determine how Excel handles macro code in files that are not stored in a trusted location. The default setting disallows such macros but causes a notification bar to appear whenever you open a file that contains a proscribed macro. The notification bar looks like this:

If you know for sure that whatever macros the file might contain are benign, you can overrule the security cop by clicking Options, reading the message that appears, and then selecting Enable This Content. If you use macros regularly, however (for example, if you create them to increase your own productivity), you probably don't want to deal with the notification bar every time you open a workbook containing macros. You might instead be tempted to change the Macro Settings option in the Trust Center to Enable All Macros (Not Recommended, Potentially Dangerous Code Can Run). As the parenthetical comment suggests, however, this is not an ideal approach to macro security.

A better approach is to designate the folders you use regularly, as well as those from which you are likely to open macro-laden files created by trustworthy others, as trusted locations. Excel permits all macro content in files stored in such locations. To configure a trusted location, return to the Trust Center, and select the Trusted Locations category. You will see a list comparable to the following:

The top area in this dialog box lists trusted locations you create, as well as those provided as defaults by the Microsoft Office Setup program. Below that list, under the heading Policy Locations, you might see additional trusted locations established by your IT staff. To set up a new trusted location, click Add New Location. In the dialog box that appears, you can specify the path of the new location, indicate whether you also want to trust subfolders of that location, and add a description of the location. Excel will provide a date and time stamp for you.

By using the trusted-locations mechanism, you can create no-questions-asked zones for the macros you create and use, without disabling the defenses Excel uses against external threats. If you leave in place the default Macro Settings option-Disable All Macros With Notification-Excel will inform you if you happen to open a file from a nontrusted location that contains a macro. When that occurs, you can make a judgment call about whether to allow the banned content. You can also easily add the new file's folder to your listed of trusted locations by clicking Trust Center in the notification bar.

Having configured the security options to your satisfaction, you still have one more decision to make before you can begin creating your own macros. The Excel default workbook format (.xlsx) does not support macros. To save a workbook containing one or more macros, you need to use one of the following formats:

• Excel Macro-Enabled Workbook (.xlsm)

• Excel Binary Workbook (.xlsb)

• Excel 97-2003 Workbook (.xls)

If you plan to use macros regularly, or even occasionally, you should consider changing the default to one of these macro-supporting formats. To do this, return to the Excel Options dialog box once more (click the Microsoft Office Button, and then click Excel Options), select the Save category, and select a format in the Save Files In This Format drop-down list.

For more information about file format options, see "Understanding the 'XL' Formats" on page 56.

If you prefer not to change the default file format to Excel Macro-Enabled Workbook, you can always save in that format on a case-by-case basis when you create a file that uses a macro. Excel will warn you when you try to save a file with macros in a non-macro-enabled format.