Macro Security Settings

Excel's default security settings do not allow any macro activity. Before you begin exploring macros in Excel and the Macro Recorder, you will need to tell Excel which security settings to use to control what happens when you open a workbook that contains macros (or one that will contain macros). If you use antivirus software that works with Microsoft Office 2007 and you open a workbook that contains macros, the virus scanner will check the workbook for viruses before opening it.

You can make changes to the macro security settings in the Trust Center:

1. Click the Microsoft Office button, which looks like the following:

2. Click the Excel Options button, select Trust Center, click the Trust Center Settings button, and finally click the Macro Settings item.

Alternatively, on the Developer ribbon, click the Macro Security button in the Code Group section. (Depending on the network security level at your organization, you may or may not have rights to change these settings).

Note Macro setting changes made in Excel's Macro Settings section apply to Excel only; they do not affect any other Microsoft Office applications.

Table 1-1 lists the Excel macro security settings and explains each setting.

Table 1-1. Macro Security Settings



Disable all macros without notification Disable all macros with notification

Disable all macros except digitally signed macros

Enable all macros (not recommended, potentially dangerous code can run)

Trust access to the VBA project object model

Use this setting if you don't trust the source of a workbook containing macros.

This is the default setting. Use it when you want macros to be disabled, but you want to get security alerts if there are macros present. You can decide when to enable those macros.

This is the same as the "Disable all macros with notification" option, except that when the macro is digitally signed by a trusted publisher, the macro can run if you have trusted the publisher.

Use this setting to allow all macros to run.

This setting is for developers only.

Caution The "Enable all macros" setting makes your computer vulnerable to potentially malicious code. It is not recommended that you use this setting permanently. For the examples in this book, we use this setting, but it is highly recommended that you choose another option in your production code.

Trusted Publishers

This section lists the currently trusted certificates that can be used by developers to sign documents and add-ins. When you open a digitally signed document, the digital signature appears on your computer as a certificate. The certificate names the VBA project's source, plus additional information about the identity and integrity of that source. A digital signature does not necessarily guarantee the safety of a project, and you must decide whether you trust a project that has been digitally signed. If you know you can always trust macros from a particular source, you can add that macro developer to the list of trusted sources when you open the project.

Trusted Locations

This is where you can define trusted locations. These are folders on your PC or network where files with macros can be stored. Excel will trust any document in a folder designated as trusted and will run any macros in those files.

Caution Be careful when defining trusted locations! Documents in trusted locations can run without being checked by the Trust Center security system. If you add or change a location, make sure the new location is secure.

The Remove Button

If you added a certificate to your list of trusted publishers when you first opened a VBA project signed with that certificate, and later choose not to trust that publisher, you can use the Remove button to remove the certificate from your list of trusted publishers. The next time a project signed with that certificate is opened, the virus protection behavior corresponding to the setting on the Security Level tab will occur.

The Remove button in the Trusted Locations section lets you remove locations from the list in the same manner.

Lowering the Security Level

Before you can begin recording and playing back macros, you must lower the macro security level. By default, all macro activity is disabled.

To temporarily set the security level to enable all macros, do the following:

1. On the Developer ribbon, in the Code group, click Macro Security, as shown in

Figure 1-1. Code options on the Developer ribbon

2. If the Developer tab is not available, do the following to display it: a. Click the Microsoft Office button (shown in the following image).

b. Click Excel Options.

c. In the Popular category, under "Top options for working with Excel," select the "Show Developer tab in the Ribbon" check box, and then click OK.

Figure 1-1

Visual Macros Basic

Record Macro li^j Use Relative References t\ Macro Security

Visual Macros Basic


3. Under Macro Settings, click "Enable all macros (not recommended, potentially dangerous code can run)," and then click OK, as shown in Figure 1-2.

Warning To help prevent potentially dangerous code from running, it is recommended that you return to any of the settings that disable all macros after you finish working with macros.

Once this is done, you can record your macro.

Once this is done, you can record your macro.

Figure 1-2. Excel Trust Center Macro Settings options
0 0


Post a comment