Saving the Workbook

Now that we have recorded a macro, it is time to save our workbook.

Understanding Excel File Formats

Excel workbooks come in a few different configurations. Today, we need to understand two of the formats, Excel Workbooks (.xlsx) and Excel Macro-Enabled Workbooks (.xlsm). An Excel Workbook (.xlsx) is what most people think of when they think of an Excel file. It contains worksheets, the data on the worksheets, and any formulas to gain insight from the data. Excel Macro-Enabled Workbooks (.xlsm) contain all the same capabilities as Excel Workbooks (.xlsx), but they also have the capability to store VBA code (macros).

There are a couple reasons for having both file formats. First, if you open an.xlsx file, you know upfront that there are not macros within. Second, if you open a macro enabled workbook, you can still view the data without enabling the macros. This is accomplished by having two separate parts within an Excel file, Excel data and macro data.

To see how this works, consider the following diagram:

As you can see,.xlsx files cannot save macro data. You can create macros within an.xlsx file, but they cannot be saved within that format because the macro data portion of the file format is not present. Since our workbook is still the.xlsx format, we cannot retain the macros we have created.

By contrast, an.xlsm file can save macros. As you can see from the previous diagram,.xlsm files do have an available macro data portion.

We will begin saving this workbook. When try to save a workbook that contains macros as a file type that cannot store macros, Excel gives us the option to continue saving as a macro-free workbook (.xlsx) or to change the file type to a macro-enabled workbook (.xlsm).

Let's save now.

Step3. To begin saving the workbook, on the Quick Access Toolbar,

Click

You see this dialog box:

NOTE for MacOS Users: You will see this dialog box:

Step4. To save the file as a macro-enabled workbook,

Click, Click Current Folder (Excel Macros)

NOTE for MacOS Users: To save the file as a macro-enabled workbook, Click.

We see the Save As dialog box.

Step5. To change the file format, in the Save As Type text box,

Click, Click Excel Macro-Enabled Workbook (*.xlsm)

NOTE: We may choose No from the warning, but if we don't change this file format, we will still save a file without preserving the macro information.

Step6. To keep the original filename and the new file format,

Click

NOTE for MacOS Users: To select the Excel Macros folder, in the Save As dialog box, Click, Click Excel Macros, Click.

Our file is now saved as a macro-enabled workbook (*.xlsm).

It would be nice if this macro was run when this workbook is opened. Let's see how this is done.