Unleashing the power of Excel with VBA
- By Bill Jelen and Tracy Syrstad
- 4/10/2019
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.
NOTE
Although you and I are not afraid to use macros, I have encountered people who freak out when they see the .xlsm file type. They actually seem angry that I sent them an .xlsm file that did not have any macros. Google’s Gmail has joined this camp, refusing to show a preview of any attachments sent in the .xlsm format.
If you encounter someone who seems to have a fear of the .xlsm file type, remind them of these points:
Every workbook created in the past 30 years could have had macros, but in fact, most did not.
If someone is trying to avoid macros, she should use the security settings to prevent macros from running anyway. The person can still open the .xlsm file to get the data in the spreadsheet.
With these arguments, I hope you can overcome any fears of the .xlsm file type so that it can be your default file type.