Macro security in Excel

VBA can do nearly anything a user can, deleting files included. That makes a workbook full of macros from an unknown source a real risk, so Excel quarantines macros by default. Open a macro-enabled file from outside your trusted locations and you get a yellow SECURITY WARNING. Macros have been disabled bar. Click Enable Content to run them, and only if you trust the source. Files downloaded from the internet get the stronger Mark of the Web treatment and may be blocked outright until you unblock them (right-click the file ▸ Properties ▸ Unblock).

You set the policy in the Trust Center, at File ▸ Options ▸ Trust Center ▸ Trust Center Settings ▸ Macro Settings. The sane default, "Disable VBA macros with notification", disables macros but lets you enable them file by file. You can also nominate your own working folders as Trusted Locations, whose files run macros without prompting. The one setting a professional never ships to anyone else is "Enable all macros". It defeats the whole safeguard.

Which file format should you save macros in?

Then there's the file-format trap, the beginner's most common self-inflicted wound. The ordinary .xlsx format can't store macros. That's by design, so a normal spreadsheet is guaranteed macro-free and safe. If you write a macro and then save as .xlsx, Excel warns you ("the following features cannot be saved in macro-free workbooks: VB project"). Click through and it deletes all your code without another word. The macros are simply gone. To keep them, you have to save in a macro-enabled format:

• .xlsm is the Excel Macro-Enabled Workbook, and it's the normal choice. It's the same XML format as .xlsx, plus a VB project. Use File ▸ Save As ▸ Excel Macro-Enabled Workbook (*.xlsm).
• .xlsb is the Excel Binary Workbook. It holds macros too. Its binary format opens and saves faster and smaller for very large models, though it's slightly less interoperable. It's a good choice for heavy internal tools.
• .xlam is a macro-enabled add-in, for packaging macros you want to reuse across many workbooks. That comes in packaging a Personal Macro Workbook.

In code, you can spell the format out so there's never any doubt:

ThisWorkbook.SaveAs Filename:="Report", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled   ' this constant means .xlsm

Burn this rule in. The moment a workbook contains a macro, its home is .xlsm (or .xlsb), never .xlsx. More macros have been lost to an absent-minded "Save As ▸ Excel Workbook" than to any bug.

How do you run a macro in Excel?

Once a Sub is written, you can run it five ways:

1. Press F5 with the cursor inside it in the VBE, or click the green Run triangle.
2. Press Alt+F8 in Excel (Developer ▸ Macros), pick it and click Run.
3. Put a button on the sheet. Use Developer ▸ Insert ▸ Button (Form Control), draw it and assign the macro.
4. Add it to the Quick Access Toolbar. File ▸ Options ▸ Quick Access Toolbar ▸ choose Macros, add yours, and it becomes a one-click icon.
5. In the Immediate window, type the Sub's name and press Enter.

Method 1 is for developing. Methods 3 and 4 are for the people who'll use your tool.