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.
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.
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 🙂