Saving Workbooks That Contain Macros

If you store one or more VBA macros in a workbook, the file must be saved with macros enabled. This is a file with an XLSM extension (or XLAM extension if it's an add-in). This file format is not the default format, so you need to make sure that you save the file with the correct extension.

For example, assume that you create a new workbook containing one or more macros. The first time you save the workbook, the file format defaults to XLSX, which is a file format that cannot contain macros. Unless you change the file format to XLSM, Excel displays the warning shown in Figure 22-5. You need to click No, and then choose Excel Macro-Enabled Workbook (*.xlsm) from the Save as Type drop-down.

Figure 22-5: Excel warns you if your workbook contains macros and you attempt to save it in a non-macro file format.

Caution 

Be careful because Excel makes it very easy to accidentally delete all your macros with a single button click. If you accidentally click Yes instead of No, Excel deletes the macros from the saved workbook. The macros are still available in the copy that you're working on, however. So if you catch your mistake, it's still not too late to resave the workbook with an XLSM extension.