Macro Security in Excel - dummies

By Conrad Carlberg

Microsoft had a shock a few years ago when it turned out that someone with too much time on his hands could write VBA code that would make Excel (or any Office application, for that matter) go nuts. This little monster is called a macro virus. The first one that showed up displayed a message box that said “I think this makes my point.”

They can be nasty, and you want to protect yourself. An Excel macro’s reach extends far beyond Excel. It can delete files, rename files, and just generally do damage.

It helps to know that more recent versions of Excel save a workbook that has a macro with the extension .xlsm or .xlam rather than .xlsx.

Excel has four levels of macro protection, and you can choose the one you want to use.

If you never share Excel workbooks with anyone else (that means co-workers, clients, your Uncle Joe), you might be able to save some time this way:

  1. Click the Ribbon’s File tab.
  2. Choose Options from the navbar.
  3. Click Trust Center in the Excel Options navbar.
  4. Click Trust Center Settings.
  5. Select the Enable All Macros option button.

Microsoft doesn’t recommend this option because, if you ever do get a workbook from somewhere else, you could be in trouble: If the workbook has a macro virus, you won’t be warned.

If you do share workbooks with other people, or open workbooks that other people have created, you can arrange to be warned that a workbook has potentially hazardous macros by clicking any one of the three Disable option buttons:

  • If you choose to disable macros with notification, you will see warnings that editing and content are disabled. You can override that status and enable the macros if you choose to do so.
  • If you choose to disable macros without notification, you will not see warnings and the name of the macros will not appear via the Ribbon’s Developer tab.
  • You can choose to disable macros unless they have been digitally signed via an application that identifies the source of the digital signature.

It’s entirely possible to open workbooks that you think will have any macros disabled, only to find that the macros are enabled. There are various overrides such as trusted publishers and trusted locations — it’s likely that your copy of Excel regards you as a trusted publisher. Don’t be surprised to see a workbook pass by the sentry unscathed. Just sayin’.

Opening the workbook but disabling the macros is a good option if you think you know where your workbooks come from, but you’re not certain of the good intentions of the source.