- Aug 5, 2010
After VBA macros were used as the delivery method for some high-profile viruses, Microsoft changed the default security settings to prevent macros from running. Therefore, before we can begin discussing the recording of a macro, we need to show you how to adjust the default settings.
In Excel 2010, you can either globally adjust the security settings or control macro settings for certain workbooks by saving the workbooks in a trusted location. Any workbooks stored in a folder that is marked as a trusted location will automatically have its macros enabled.
You can find the macro security settings under the Macro Security icon on the Developer tab. When you click this icon, the Macro Settings category of the Trust Center is displayed. You can use the left navigation bar in the dialog to access the Trusted Locations list.
Adding a Trusted Location
You can choose to store your macro workbooks in a folder that is marked as a trusted location. Any workbook stored in a trusted folder will have its macros enabled. Microsoft suggests that a trusted location should be on your hard drive. The default setting is that you cannot trust a location on a network drive.
To specify a trusted location, follow these steps:
- Click Macro Security in the Developer tab.
- Click Trusted Locations in the left navigation pane of the Trust Center.
- If you want to trust a location on a network drive, select Allow Trusted Locations on My Network.
- Click the Add New Location button. Excel displays the Microsoft Office Trusted Locations dialog (see Figure 1.2).
Figure 1.2 Manage trusted folders on the Trusted Locations category of the Trust Center.
- Click the Browse button. Excel displays the Browse dialog.
- Browse to the parent folder of the folder you want to be a trusted location. Click the trusted folder. Although the folder name does not appear in the Folder Name box, click OK. The correct folder name will appear in the Browse dialog.
- If you want to trust subfolders of the selected folder, select Subfolders of This Location Will Be Trusted.
- Click OK to add the folder to the Trusted Locations list.
Although trusted locations are not new in Excel 2010, Microsoft has made the process of adding trusted locations more discoverable in Excel 2010.
Using Macro Settings to Enable Macros in Workbooks Outside of Trusted Locations
For all macros not stored in a trusted location, Excel relies on the macro settings. The Low, Medium, High, and Very High settings that were familiar in Excel 2003 have been renamed.
To access the macro settings, click Macro Security in the Developer tab. Excel displays the Macro Settings category of the Trust Center dialog. Select the second option, Disable All Macros with Notification. A description of each option follows:
- Disable All Macros Without Notification—This setting prevents all macros from running. This setting is for people who never intend to run macros. Because you are currently holding a book that teaches you how to use macros, it is assumed that this setting is not you. This setting is roughly equivalent to the old Very High Security setting in Excel 2003. With this setting, only macros in the Trusted Locations folders can run.
Disable All Macros with Notification—This setting is similar to Medium security in Excel 2003 and is the recommended setting. In Excel 2003, a Medium setting caused a box to be displayed when you opened a file containing macros. This box forced the person to choose either Enable or Disable. Many novice Excel users randomly choose from this box. In Excel 2010, the message is displayed in the Message Area that macros have been disabled. You can choose to enable the content by clicking that option, as shown in Figure 1.3.
Figure 1.3 Open a macro workbook using the Disable All Macros with Notification setting to enable the macros.
- Disable All Macros Except Digitally Signed Macros—This setting requires you to obtain a digital signing tool from VeriSign or another provider. This might be appropriate if you are going to be selling add-ins to others, but a bit of a hassle if you just want to write macros for your own use.
- Enable All Macros (Not Recommended: Potentially Dangerous Code Can Run)—This setting is similar to Low macro security in Excel 2003. Although it requires the least amount of hassle, it also opens your computer up to attacks from malicious Melissa-like viruses. Microsoft suggests that you do not use this setting.
Using Disable All Macros with Notification
It is recommended that you set your macro settings to Disable All Content with Notification. If you use this setting and open a workbook that contains macros, you will see a Security Warning in the area just above the formula bar. Assuming you were expecting macros in this workbook, click Enable Content.
If you do not want to enable macros for the current workbook, dismiss the Security Warning by clicking the X at the far right of the message bar.
If you forget to enable the macros and attempt to run a macro, Excel indicates that you cannot run the macro because all macros have been disabled. If this occurs, close the workbook and reopen it to access the message bar again.