Unleashing the power of Excel with VBA
- By Bill Jelen and Tracy Syrstad
Understanding which file types allow macros
Excel 2019 offers support for four file types. Macros are not allowed to be stored in the .xlsx file type, and this file type is the default file type! You have to use the Save As setting for all of your macro workbooks, or you can change the default file type used by Excel 2019.
The available files types are as listed here:
Excel Workbook (.xlsx)—Files are stored as a series of XML objects and then zipped into a single file. This creates significantly smaller file sizes. It also allows other applications (even Notepad!) to edit or create Excel workbooks. Unfortunately, macros cannot be stored in files with an .xlsx extension.
Excel Macro-Enabled Workbook (.xlsm)—This is similar to the default .xlsx format, except macros are allowed. The basic concept is that if someone has an .xlsx file, he will not need to worry about malicious macros. However, if he sees an .xlsm file, he should be concerned that there might be macros attached.
Excel Binary Workbook (.xlsb)—This is a binary format designed to handle the larger 1-million-row grid size introduced in Excel 2007. Legacy versions of Excel stored their files in a proprietary binary format. Although binary formats might load more quickly, they are more prone to corruption, and a few lost bits can destroy a whole file. Macros are allowed in this format.
Excel 97-2003 Workbook (.xls)—This format produces files that can be read by anyone using legacy versions of Excel. Macros are allowed in this binary format; however, when you save in this format, you lose access to any cells outside A1:IV65536. In addition, if someone opens the file in Excel 2003, she loses access to anything that used features introduced in Excel 2007 or later.
To avoid having to choose a macro-enabled workbook in the Save As dialog box, you can customize your copy of Excel to always save new files in the .xlsm format by following these steps:
Click the File menu and select Options.
In the Excel Options dialog box, select the Save category from the left navigation pane.
Open the Save Files In This Format drop-down menu and select Excel Macro-Enabled Workbook (*.xlsm). Click OK.