Programming Excel with VBA and .NET

Macros and Security

When you open a workbook that contains code, Excel displays a security warning suggesting you might want to disable the code, as shown in Figure 1-29.

Microsoft included this warning because, once a user enables the macros in a workbook, that code has full access to the user’s system and can do some pretty nasty things (such as changing or deleting files) without the user knowing it. Microsoft deals with this problem differently in different programming tools, and in Excel Visual Basic they put the burden on the user for determining whether code should or should not be trusted.

Figure 1-29. Excel’s macro security warning is pretty dire

Unfortunately, users are often the least-qualified people to make this judgment. Who knows where ch01.xls came from or what it will do if I open it? The way to answer those questions is to add a digital signature. A digital signature identifies the author of the content or the macros contained in a workbook, template, or add-in. By digitally signing a workbook’s code, you add a unique identifier that says the code came from you (or your organization) and thus the user may have more confidence that the workbook won’t insert the word Wazoo in all your correspondence.

Tip

I once received a work-for-hire contract from Microsoft legal that occasionally declared Wazoo! I thought they were just checking to make sure I read the thing....

There’s a lot more information on security and digital signatures in Chapter 26, but for now I’ll tell you how to eliminate the warning in Figure 1-29 for Excel Visual Basic code you create and use on your own computer. Doing that involves two major steps:

1. Create a personal digital signature for signing your workbooks.

2. Sign your workbooks with that certificate.

These steps are detailed in the following procedures.

To create a personal digital signature:

1. From the Windows Programs menu, choose Microsoft Office → Microsoft Office Tools → Digital Certificate for VBA Projects. Windows runs SelfCert.exe and displays the Create Digital Certificate dialog box (Figure 1-30).

2. Type the name you want displayed within the signature and click OK. SelfCert.exe creates a local certificate and displays a success message.

Figure 1-30. Creating a personal digital signature

Tip

SelfCert.exe is provided with Office 2000 and later. If it is not installed on your system, run Office Setup and install Office Tools → Digital Signature for VBA Projects.

This certificate is valid on only the machine on which you created it. Therefore, its use is really limited to signing macros on your own machine to avoid the security prompt you get each time you open a workbook containing macros you’ve written.

To sign a Visual Basic project in a workbook, follow these steps:

1. From within the workbook, open the Visual Basic Editor.

2. Choose Tools → Digital Signature. Visual Basic displays the Digital Signature dialog box (Figure 1-31).

3. Click Choose. Visual Basic displays a dialog box containing all the digital signatures installed on your system (Figure 1-32).

4. Select the certificate to use, and click OK. Then click OK again to close the Digital Signature dialog box.

Once the code is signed, you may see the security warning in Figure 1-33 when you open a workbook, template, or add-in containing the code you just signed.

If you select the option to “Always trust macros from this publisher” and click Enable Macros, you won’t see this warning every time you open your own signed workbooks.

Figure 1-31. Signing a Visual Basic project

Figure 1-32. Choosing a signature