Press F12 (or go to File → Save As → Browse), open the Save as type list, choose Excel Macro-Enabled Workbook (*.xlsm) and click Save. The file keeps its VBA code, and the macros are ready to enable the next time it opens.
Saving a workbook that contains code as a normal .xlsx removes the code. Excel warns that “the following features cannot be saved in macro-free workbooks”; clicking Yes saves the file without its macros, so click No and choose .xlsm instead.
Excel Binary Workbook (*.xlsb) also keeps macros and is usually smaller and quicker to open for large files. To save every new workbook as .xlsm, go to File → Options → Save and change Save files in this format.
From VBA, use ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "Report.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled. The extension and the FileFormat value must agree, or SaveAs stops with error 1004.
Macros in a file downloaded from the internet or received by email are blocked, even in .xlsm. Right-click the file, choose Properties, tick Unblock and click OK, then open it and click Enable Content.
What is a Macro-Enabled Workbook?
In Excel, when you write a macro or add VBA codes in a file, you need to save that file as a macro-enabled type in the .xlsm format. With this file type, Excel stores all the codes in the file. And you can run those codes from the file as well.
Here you have a file with the codes, and now, we will look at how to save this file as a macro-enabled file type in Excel.
Steps to Save a Macro-Enabled Workbook
- First, go to the File tab and click on the “Save As” option.
- After that, specify the location where you want to save your file.
- Next, from the “Save as type” select “Excel Macro-Enabled Workbook”.
- In the end, click OK to save the file.
Below you can see the file we have saved in our system as a .xlsm extension.
Points to Remember
- Once you create a macro-enabled file, you need to activate the macro (File ⇢ Options ⇢ Trust Center ⇢ Trust Enter Settings ⇢ Macros) to run a code.
- You can use Personal Macro Workbook to save all your codes in one place.
- Receiving a macro-enabled workbook from someone else (from a different system) could be a possible threat.
Related Tutorials
- Copy an Excel File (Workbook) using VBA
- VBA Activate Workbook (Excel File)
- VBA Close Workbook (Excel File)
- VBA Combine Workbooks (Excel Files)
- VBA Create New Workbook (Excel File)
- VBA Delete Workbook (Excel File)
- VBA Open Workbook (Excel File)
- VBA Protect/Unprotect Workbook (Excel File)
- VBA Rename Workbook (Excel File)
- VBA Save Workbook (Excel File)
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.