Unleashing the power of Excel with VBA

Macro security

After a Word VBA macro was used as the delivery method for the Melissa virus, Microsoft changed the default security settings to prevent macros from running. Therefore, before we can begin discussing the recording of a macro, it’s important to look at how to adjust the default settings.

In Excel 2019, you can either globally adjust the security settings or control macro settings for certain workbooks by saving the workbooks in a trusted location. Any workbook stored in a folder that is marked as a trusted location automatically has 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 box 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:

1. Click Macro Security in the Developer tab.

2. Click Trusted Locations in the left navigation pane of the Trust Center.

3. If you want to trust a location on a network drive, select Allow Trusted Locations On My Network.

4. Click the Add New Location button. Excel displays the Microsoft Office Trusted Location dialog box (see Figure 1-2).

   

   FIGURE 1-2 You manage trusted folders in the Trusted Locations category of the Trust Center.

5. Click the Browse button. Excel displays the Browse dialog box.

6. 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 box.

7. If you want to trust subfolders of the selected folder, select Subfolders Of This Location Are Also Trusted.

8. Click OK to add the folder to the Trusted Locations list.

Using macro settings to enable macros in workbooks outside 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 box. 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 for 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—The operative words in this setting are “With Notification.” This means that you see a notification when you open a file with macros and you can choose to enable the content. If you ignore the notification, the macros remain disabled. This setting is similar to the Medium security setting in Excel 2003 and is the recommended setting. In Excel 2019, a message is displayed in the Message area indicating 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 The Enable Content option appears when you use Disable All Macros With Notification.

• 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 it’s 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 the Low macro security setting in Excel 2003. Although it requires the least amount of hassle, it also opens your computer to attacks from malicious Melissa-like viruses. Microsoft suggests that you not use this setting.

Using Disable All Macros With Notification

It is recommended that you set your macro settings to Disable All Macros With Notification. If you use this setting and open a workbook that contains macros, you see a security warning in the area just above the formula bar. If you are 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.