Recording a Macro

Macros are written in a programming language called Visual Basic for Applications, or VBA; however, knowledge of this language isn't required to create and use simple macros. When a series of actions is recorded, Excel translates them into VBA code as they are performed.

When Excel records a macro, it retains the sequence of keystrokes and menu selections so they can be repeated as needed. This process is very much like recording a voice with a tape recorder and then listening to it later as it plays back. When the macro is run, Excel exactly repeats the recorded steps, mistakes and all.

When the workbook is opened, some of the columns are not wide enough to show all of their contents. The first macro we will record will fix this.

Turning on Excel's Recorder

Let's begin by enabling Excel's macro recorder.

Step1. To switch to the View tab, on the Ribbon,

Click the View tab

Macro options are to the far right of the View tab.

Step2. To access the macro commands, on the right side of the Ribbon,

ClickClick Record Macro...

NOTE for MacOS Users: On MacOS, to access the macro commands, on the right side of the Ribbon, Click.

You see the Record Macro dialog box:

Naming Macros

Macro names are quite flexible, but they must adhere to the following rules:

• Names cannot contain spaces.
• Names must start with a letter.
• Names cannot be the same name as a named range or cell reference, i.e. A1.

Let's name our macro AutoFit.

Step1. To enter the macro name, type:

AutoFit

Macros can be assigned keyboard shortcuts, but assigning a shortcut that is identical to an existing keyboard shortcut will overwrite that shortcut in the Windows version of Excel. For example, if you're using a Windows machine and assign the shortcut Control key+a to this macro, the select all keyboard shortcut would no longer function in Excel.

This is why we will not be assigning keyboard shortcuts to macros today.

NOTE for MacOS Users: You will encounter a warning message if you try to overwrite an existing keyboard shortcut.

We've decided on a name, let's decide where to store the code that Excel will create.

Storing Macros

Macros can be stored in three locations: in this workbook, in a new workbook, or in the personal macro workbook. Each of these storage options has its own set of strengths and limitations. Let's explore them now:

• This Workbook - Macros stored in the current workbook are only accessible when the workbook is open. Selecting this option is best if the macros you are recording will only be applicable to a particular workbook's data. It can also be useful if you are creating a workbook to store macros related to a specific task. For example, you can create a workbook that has a collection of macros you find helpful for working with IU's institutional data. That workbook can then be transported from machine to machine just like carpenters carry around the tools of their trade in a toolbox.
• New Workbook - Macros stored in a new workbook have the same restrictions and benefits of macros stored in the current workbook; they're just stored in a separate workbook. This option is best if you are starting to create a collection of macros to help with a series of related tasks or would like to keep a particular workbook macro-free. Just like with the This Workbook option, this option creates an easily transportable workbook full of macros.
• Personal Macro Workbook - Macros stored in the Personal Macro Workbook are accessible to any workbook opened in Excel by a specific user on a specific machine. The Personal Macro Workbook is hidden within a user's Windows or Mac profile. If it exists, it is opened every time that user launches Excel. Since this workbook is hidden within a user's profile, it is difficult to locate and transport.

A workbook's macros are available to all other open workbooks when it's open. Whether you use a separate workbook, the Personal Macro Workbook, or the current workbook, it's important to know where your macro is recorded so you can find it when you need it.

Since we are using an individual workbook and all the macros we record will only be useful to this workbook, let's store the macro in This Workbook.

Step1. To select This Workbook, in the "Store macro in:" field, if necessary,

Click, Click This Workbook

It is always a good idea to document the macro by providing a description stating exactly what the macro does.

Step2. To enter a description of the macro, in the Description field, type:

Automatically fit columns to their data.

We are done setting up our macro so let's begin recording.

Step3. To begin recording,

Click

An audio recorder-like stop button displays in the status bar of the worksheet:

This indicates that the macro has begun recording. Any keystroke or mouse click now taken will be recorded by Excel.

NOTE: The recorder does not record movement of the mouse.

Recording a Sequence of Actions

In order to record a macro that will automatically fit column widths to the data contained in that column's cells, we need to select the whole worksheet.

Selecting the Whole Worksheet

By selecting the whole worksheet, we can adjust all columns at the same time, not just the columns that currently contain data. This allows us to expand our worksheet without rewriting our macro. This is a concept known as scalability. Scalability is the ability of a system to handle expansion or contraction without having to redesign core functionality.

Step1. To select the entire worksheet,

Click

The whole worksheet is selected.

Step2. To resize all the columns at once,

Double-Click

We are almost done recording. Before we finish, it's good practice to select a cell where we would like our cursor to be when we're done running the macro. For our purposes, let's select B2.

Step3. To place the cursor in the first cell of the worksheet,

Click cell B2

Now that we have recorded the tasks we wanted to automate, we will turn off Excel's recorder.

NOTE: We select B2 here since A1 is often the default selected cell in a worksheet.

Finishing Recording the Macro

We will finish the macro by leaving cell B2 as the active cell.

Step1. To stop the recorder, on the left side of the status bar,

Click

The actions have been recorded as the content of the AutoFit macro.

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