How to save workbooks that contain Macros

If your workbook contains one or more VBA Macros, you must save the file with an XLSM extension.

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

For example, we want to create e VBA macro that will insert the current date into the active cell, then applies a date format, makes the cell bold, sets the text color to white, sets the background color to black. We named the Macro as CurrentDate. Read this article to know How to create VBA Macros in Excel using Macro Recorder. The Macro is recorded like the following code:

Sub CurrentDate()
' CurrentDate Macro
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
    Selection.Font.Bold = True
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

The first time you want to save this workbook (containing macro CurrentDate), the file format defaults to XLSX — and this format can’t contain macros. Unless you change the file format to XLSM, Excel displays the warning shown in the following figure.

How to save workbooks that contain Macros

Excel will warn you if your workbook contains macros and you attempt to save it in a non-macro
file format.

You need to click No, and then choose Excel Macro-Enabled Workbook (*.xlsm) from the Save As type drop-down list in the Save As dialog box.

How to save workbooks that contain Macros

Choose Excel Macro Enabled Workbook (*.xlsm) to save macro containing workbook.

Note: Alternatively, you can save the workbook in the old Excel 97–2003 format (which uses an XLS extension) or the new Excel binary format (which uses an XLSB extension). Both of these file formats can contain macros.

Happy Excelling 🙂



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

1 Comment

Leave a reply