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()"
    Range("A1").Select
    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 Macr 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! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

1 Comment

      Leave a reply