Save as Macro-enabled workbook
Starting with Excel 2007, the default file extension for Excel workbooks is .xlsx, but it cannot contain macros. If your workbook contains macros and then you save the workbook as an .xlsx file, all VBA code is automatically removed. Fortunately, when saving a workbook with macros as an .xlsx file, Excel warns you that it will delete the contents of your macros.
If you want to keep the macros, you must save the file as an Excel macro-enabled workbook. This will give your file the .xlsm extension. The idea is that all workbooks with .xlsx file extensions are automatically considered safe, and you can identify .xlsm files as potential threats.
In Excel, select File > Save As, then type “HelloWorld” as the workbook name and click the Save button.
When you click the Save button, Excel will display a warning:
The following features cannot be saved in macro-free workbooks - VB project.
If you click Yes, all of your macros will be deleted, if you want to save your macros, click No button, and choose a macro-enabled file type in the File Type list.
Change the file type to Excel Macro-Enabled Workbook (*.xlsm), then click the Save button to save the complete workbook.
Understanding which file types allow macros
| Format | Extension | Description |
|---|---|---|
| Excel Workbook | .xlsx | The default XML-based file format for Excel 2007 or later. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm). |
| Excel Macro-Enabled Workbook | .xlsm | The XML-based and macro-enabled file format for Excel 2007 or later. Stores VBA macro code or Excel 4.0 macro sheets (.xlm). |
| Excel Binary Workbook | .xlsb | The binary file format (BIFF12) for Excel 2007 or later. |
| Template | .xltx | The default file format for an Excel template for Excel 2007 or later. Cannot store VBA macro code or Excel 4.0 macro sheets (.xlm). |
| Excel Macro-Enabled Template | .xltm | The macro-enabled file format for an Excel template for Excel 2007 or later. Stores VBA macro code or Excel 4.0 macro sheets (.xlm). |
| Excel 97- Excel 2003 Workbook | .xls | The Excel 97 - Excel 2003 Binary file format (BIFF8). |
| Excel 97- Excel 2003 Template | .xlt | The Excel 97 - Excel 2003 Binary file format (BIFF8) for an Excel template. |
| Microsoft Excel 5.0/95 Workbook | .xls | The Excel 5.0/95 Binary file format (BIFF5). |
| XML Spreadsheet 2003 | .xml | XML Spreadsheet 2003 file format (XMLSS). |
| XML Data | .xml | XML Data format. |
| Excel Add-In | .xlam | The XML-based and macro-enabled Add-In format for Excel Excel 2007 or later. An Add-In is a supplemental program that is designed to run additional code. Supports the use of VBA projects and Excel 4.0 macro sheets (.xlm). |
| Excel 97-2003 Add-In | .xla | The Excel 97-2003 Add-In, a supplemental program that is designed to run additional code. Supports the use of VBA projects. |
| Excel 4.0 Workbook | .xlw | An Excel 4.0 file format that saves only worksheets, chart sheets, and macro sheets. You can open a workbook in this file format in Excel 2010, but you cannot save an Excel file to this file format. |
| Works 6.0-9.0 spreadsheet | .xlr | Spreadsheet saved in Microsoft Works 6.0-9.0. Note: This format is supported in Excel Starter only. |