Section 27.3. Macro Security | Excel 2007[c] The Missing Manual

27.3. Macro Security

Excel's macro language is surprisingly powerful. In fact, the VBA language packs enough power for expert gurus and hackers who want to design malicious worksheet viruses. Unfortunately, macros aren't limited to moving from cell to cell , entering information, formatting data, and so on. Instead, macros can include fullfledged VBA code (which you'll use in the next chapter), and evil VBA code can delete files and lobotomize your operating system.

Note: Excel macro viruses are adept at spreadingthey work by copying themselves from an infected workbook to other currently open workbooks. But only a few Excel viruses exist, and almost all of them are non-destructive. They may annoy you, but they aren't likely to trash your computer. You can find a catalog of Excel viruses at www.viruslibrary.com/virusinfo/ExcelMacroViruses.htm.

To keep your machine clean, the best solution is to avoid using macros in Excel spreadsheets that you don't trust. (And if you do get infected, anti-virus software can help you out.) Happily, Excel's got your backwhenever you open a macroenabled workbook, Excel automatically disables all the macros it contains. In other words, if you close a workbook that contains a macro, and then open it later, you can't run any macros. Excel disables macros automatically regardless of which file format you're using.

You'll notice that something's amiss as soon as you open a file that contains macros. When Excel disables the macros in a workbook, it pops up a message bar that explains what happened (Figure 27-4).

Note: The message bar can be hidden. If you think Excel's disabled your macros but you don't see the message bar, choose View

Show/Hide

Message Bar.

Figure 27-4. The message bar gives you an intimidating alert, along with a button named Options. To allow macros to run, click Options and then, from the dialog box that appears, choose "Enable this content". Then click OK. Unfortunately, you'll need to repeat this procedure every time you open the workbook file. Your other option is to click "Open the Trust Center" (see Figure 27-5) to adjust the security settings for a more permanent solution.

Figure 27-5. When you click Options (as shown in Figure 27-4), Excel explains the problem with this intimidating message box. Choose "Enable this content", and then click OK. Excel reloads your workbook in macro-enabled mode, and you're good to go.

Depending on Excel's settings, the security warning may not appear at all . Excel quietly disables your macros, and the person using your workbook won't understand why certain features don't work.

Clicking Enable Content for the one thousandth time gets annoying. Really.

The solution? Master the Trust Center, which lets you tell Excel exactly what to do when it faces down a macro-enabled workbook.

Note: You may wonder why Excel doesn't wait until you try to run a macro before it warns you. Some macros can be set to run even if you don't explicitly use the View

Macros

View Macros command. These macros run automatically in response to certain Excel events (like opening a document). To further complicate life, nefarious macros can sometimes hide or password-protect their code, making it difficult to find out that a problem even exists.

27.3.1. The Trust Center

So, what if you don't want to face the message bar every time you open a workbook? Excel gives you three options to make it easier to work with files that contain unsafe macros:

You can lower the Excel security settings so that macros are allowed. This approach isn't a great idea because it allows all code to runeven potentially malicious code in other files.
You can tell Excel to trust the files in certain folders on your computer (or on other computers). This option is the most convenient way to go.
You can tell Excel to trust workbooks that have been created by a trusted publisher . This option's the most secure, but in order to set it up, you need to pay another company to get a security certificate. For that reason, big companies with money to burn are usually the only ones who opt for this route.

All these actions take place in the same window: the Trust Center (Figure 27-6). To get to it, from the Microsoft Office Security Options dialog box (Figure 27-5), click the link titled "Open the Trust Center" (lower-left corner). Or use the following more roundabout approach:

Choose Office button Excel Options .
Click the Trust Center Settings button .

The Trust Center has several sections:

Trusted Publishers lets you tell Excel to trust workbooks that are digitally signed by certain people. To use this feature, your company needs to buy a digital certificate from a company like VeriSign (www.verisign.com). Then, when you open a signed workbook, Excel contacts the company that issued the certificate and checks that it's valid. If it's valid, everything's kosher, the workbook is trusted, and Excel allows all macros. Digital certificates are outside the scope of this book, but you can learn about them at www.verisign.com.au/repository/tutorial/digital/intro1.shtml.

Figure 27-6. The Trust Center window is divided into several sections. (You move from one section to another using the list box on the left.) The Macro Settings section lets you decide how Excel reacts to macros. You can choose to enable or disable macros, and you can choose whether Excel should notify you when it disables something.

Note: If you dig around long enough, you'll discover that Microsoft has a tool (known as MakeCert.exe ) for generating your own digital certificates. However, this tool is only for testing purposes, as the certificates it generates don't work on anyone else's computer. Bewaresome Excel books and Web sites could lead you astray.

Trusted Locations lets you pick out the places on your hard drive where you store your workbooks. That way, Excel knows to trust your workbook files but not anyone else's. You'll learn how to set up a trusted location in the following section.
Add-ins lets you adjust whether Excel add-ins (mini programs that extend the features in Excel) should be supported even if a supported publisher didn't create them. Ordinarily, Excel allows all add-ins. (After all, if you don't trust a specific add-in, don't install it!) Only people in corporate environments, where they need to lock down Excel severely to prevent any chance of a problem, use this setting.
ActiveX Settings lets you adjust how Excel treats ActiveX controls. ActiveX controls are graphical widgets (like super-fancy buttons ) that developers create (or buy), and then plop into workbooks and other documents. People don't often use ActiveX controls with Excel.
Macro Settings lets you configure how Excel deals with macros. You can make protection more rigorous (so that you don't let in any macros, unless they're from a trusted publisher), or less (so that all macros are allowed, no matter what they may do). By far the best choice is leaving this option at the standard setting: "Disable all macros with notification."
Message Bar lets you set whether Excel shows the message bar when it blocks macros in a suspicious workbook. It's best to keep this feature on, so you know when your macros have been put out of commission.
External Content lets you configure how Excel deals with formulas that link to other workbooks (Section 8.3.3) and data connections that pull information out of other sources, like databases (Section 24.1.1). Ordinarily, Excel is a bit cautious with both these ingredients . When you open a workbook that uses these features, Excel turns off its links and data connections, and shows the security warning in the message bar. (You can then click Options, and Enable Content to tell Excel to switch these features back on.) If clicking the Enable Content button is sucking too much time out of your life, the best way to remedy this inconvenience is to use a trusted location.
Privacy Options lets you tweak a few options that aren't related to macros at all. You can choose whether Excel checks the Web for updated Help content, and whether it sends troubleshooting information to Microsoft when a problem occurs (so that Microsoft can spot bugs and learn how to improve Excel in the future). If you're paranoid about Internet spies, you may want to turn off some of these options. Most of the time, these settings are for conspiracy theorists only.

27.3.2. Setting Up a Trusted Location

Wouldn't it be nice to have a way to distinguish between your workbooks, which contain perfectly harmless code, and other workbooks, which may not be so nice? Excel 2007 adds a new feature to make this distinction easy. Excel lets you designate a specific folder on your hard drive as a trusted location. If you open a workbook stored in this location, it's automatically trusted. And if one of these workbooks contains macros, data connections, or links, Excel switches on all these features right away.

Note: Of course, it's still up to you to make sure that you place only your workbooks in the trusted location. If you put a potentially dangerous workbook in the trusted location, you don't have any protection when you open it.

Here's how you can set up a new trusted location:

Open the Trust Center window .

If you're not there already, follow the steps in Section 27.3.1.
Select the Trusted Locations section .

You see a window that lists all your trusted locations (Figure 27-7). When you first install Excel, it creates a few trusted locations so it can store templates, addins, and other important files that it uses.

Figure 27-7. In this example, there's only one user -added trusted location (which is selected here). It's the FunkyExcelFiles folder in the hard drive's My Documents section.
If you want to trust a specific folder on your company or home network, turn on the "Allow trusted locations on my network" checkbox before you go any further .

You're taking a bit more of a risk when you turn this setting on, because a network location is out of your control. A hacker could sneak a virus-laden workbook into that location without your noticing. However, if you're reasonably certain that the network's secure (and the other people who use the folder aren't likely to download workbooks from the Web and place them there), you probably don't need to worry.

Click "Add new location" .

Excel asks you to fill in a few pieces of information, as shown in Figure 27-8.

Figure 27-8. To configure a trusted location, you need to specify the path (click Browse to hunt it down). You can also choose whether all this folder's subfolders should be trusted automatically, and you can fill in an optional description that appears in the list of trusted locations.

Click OK to add the location to the list .

You can configure the location or remove it at any time by selecting it in the list, and then using the clear-as-a-bell Remove and Modify buttons shown in Figure 27-7.

POWER USERS' CLINIC
Authenticode Macro Signing

If your company frequently creates and distributes workbooks with macros, you may want to use an advanced option called Authenticode signing. With Authenticode, you sign all your macros with a digital signature. When someone opens the workbook, their computer checks the signature, and verifies that it's from a known, trusted source. If it's not from a trusted source, the Security Warning dialog box appears (if Excel's using the standard Medium security level), or Excel turns the macro off (if it's using High or Very High security).

Authenticode macro signing is problematic because the person who opens the document needs a way to verify that the digital signature's trusted. You need to create a digital certificate for the person who writes the macro, and register this digital certificate with everyone who needs to use macros created by this person.

This process can be complicated, and large organizations that use macros heavily go through it. Authenticode signing is far outside the scope of this book, but you can download a document that explains this feature and the technology it uses from http://office.microsoft.com/home. Just search for macro security whitepaper .