2. Macro Security

2.1 Security Issues with Macros

Because of the way macros are stored, they often are included as part of a workbook created in Excel. This does not usually present a problem when workbooks are not exchanged, i.e., when a user never opens a workbook someone else has worked on. This is now rarely the case, since users are constantly exchanging computer files. Because the user has not maintained exclusive control over exchanged workbooks, someone else could have inserted malicious macro code into the workbooks (either deliberately or inadvertently). Such malicious code is similar to computer viruses we have all sadly become familiar with; in fact they are known as "macro viruses." To establish some protection against macro viruses, Excel provides macro security features.

Macro security can be confusing because it is set up to take effect when you open a workbook. If you create a new workbook and record a macro, you will be able to run the macro regardless of the security settings. However, once you close the workbook and reopen it macro security will be in effect, as discussed below.

2.2 Macro Security in Version 2003

Excel version 2003 sets macro security by levels: very high, high, medium, and low. I recommend setting the security level to medium; with that setting you are prompted whether to allow the macros in the workbook to be run or not if the program determines the macro might contain potentially unsafe code. Macro security settings are set by clicking on Tools/Macro/Security, and selecting one of the levels. In version 2003 there is only one kind of file for Excel workbooks, which all have the extension ".xls". All version 2003 workbooks can contain macros.

2.3 Macro Security in Version 2007/2010

In addition to macro security settings, Excel 2007/2010 has two different kinds of files for workbooks. The default file type, with the extensions ".xlsx", cannot contain macros. If you create macros in this type of workbook, when you save you will be warned that you cannot save macros in a "macro-free" workbook. "Macro-enabled" workbooks can contain macros; they have the entension ".xlsm". If you created macros in a "macro-free" workbook and want to save them, you will need to do a Save As, and save the file as a macro-enabled workbook.

To set the macro security level (and to work with the VBA editor) in version 2007/2010, you will need to include the Developer tab on the ribbon if it is not already there. To do so, in version 7 click on the Office button, then Excel Options/Popular/Top Options for Working with Excel/Show Developer Tab in Ribbon. In version 2010, click on File/Options/Customize Ribbon. Under Customize the Ribbon select Main Tabs, then check the box next to Developer. Click OK, and the Developer tab will appear on the ribbon.

On the Developer tab, in the Code group, click on Macro Security. I recommend the option "Disable All Macros with Notification." With that setting, when you open a workbook containing potentially dangerous macros you will be prompted whether to allow the macros in the workbook to be run or not.