What Is an Excel Macro-Enabled Workbook?

By Ken Burnside

With Excel 2007, Microsoft brought a new format to Excel files -- .xlsx, which contains all of the data in the spreadsheet in a compressed file with XML-derived data structures inside of it. Think of .xlsx as being like a compressed file with several other files inside of it. It also brought out a new file format for macro-enabled workbooks, with the extension *.xlsm. If you're new to Excel, or new to the new formats, knowing what each type of data file has in it can be a trifle confusing.

Macro-Enabled Workbooks

Microsoft Excel uses a programming language, Visual Basic for Applications (VBA), to provide automation in Excel spreadsheets. When these programs are attached to a spreadsheet in Excel 2007, they're attached as a separate file within the compressed file structure. When this occurs, the extension is changed to *.xlsm. An advantage of this format over earlier Excel formats is the VBA code can be removed without launching the file. This can be done by changing the file's extension to .zip, opening it and deleting the "vbaProject.bin" file from the "xl" subdirectory within it.

Macro Security

When opening a macro-enabled workbook, by default, you'll see a notification above the formula bar saying "Security Risk: Macros have been disabled" with a button marked "Options." Pressing the button will pull up a dialog box called "Security Alert -- Macro," which will tell you about the trustworthiness of the source of the macro, and will give you a choice of "Help protect me from unknown content" or "Enable this content." If you trust the source of the macro-enabled workbook, choose the latter and then click "OK."

Changing Security Settings

You can change Excel's macro security behavior by clicking the "Developer" tab of Excel, and clicking the "Code" group. There will be an option for "Macro Security" in the group there. Your options for "Macro Security" include disabling all macros, with no notification -- this is useful for ensuring macros never run -- the default of disabling macros with a notification, as described above, enabling digitally signed macros to run automatically and enabling all macros, which is not recommended.

Other Macro-Enabled Workbook Formats

Most macro-enabled workbooks use the .xlsm format. There are two additional macro-enabled Excel files. The first type, .xltm, is a macro-enabled Excel template file. A template file is a premade Excel file that has several formulas and formatting instructions already prepared; an example of an Excel template would be an hourly work-log program. Macro-enabled template files mean all sheets made from the template will also have those macros recorded. The second type, with the extension .xlam, is used for specialized Excel add-in files that expand the range of functions and types of problems Excel can handle. Think of .xlam files as being sort of like browser plugins for Excel.