Programming Excel with VBA and .NET

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 26-19). To set macro security, from the Tools menu, choose Macro, then choose Security.

Figure 26-19. Choosing macro security settings

These settings are driven by trust—the user must choose whether 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 datafiles. Users discover if their trust is misplaced only 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.

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