How to record a macro in Excel

Macro name

In VBA, any macro starts with Sub followed by the macro name and ends with End Sub, where "Sub" is short for Subroutine (also called Procedure). Our sample macro is named Header_Formatting(), so the code starts with this line:

If you wish to rename the macro, simply delete the current name and type a new one directly in the Code window.

Comments

Lines prefixed with an apostrophe (') and displayed in green by default are not executed. These are comments added for information purposes. The comment lines can be safely removed without affecting the functionality of the code.

Usually, a recorded macro has 1 - 3 comment lines: macro name (obligatory); description and shortcut (if specified before recording).

Executable code

After comments, there comes the code that executes the actions you've recorded. Sometimes, a recorded macro may have a lot of superfluous code, which may still be useful for figuring out how things work with VBA :)

The below image shows what each part of our macro's code does:

What is recorded

Excel's Macro Recorder captures quite a lot of things - almost all mouse clicks and keypresses. So, you should think over your steps carefully to avoid excess code that may result in unexpected behavior of your macro. Below are a few examples of what Excel records:

• Selecting cells with the mouse or keyboard. Only the last selection before an action gets recorded. For instance, if you select the range A1:A10, and then click cell A11, only the selection of A11 will be recorded.
• Cell formatting such as fill and font color, alignment, borders, etc.
• Number formatting such as percentage, currency, etc.
• Editing formulas and values. Changes are recorded after you press Enter.
• Scrolling, moving Excel windows, switching to other worksheets and workbooks.
• Adding, naming, moving and deleting worksheets.
• Creating, opening and saving workbooks.
• Running other macros.

What cannot be recorded

Despite many different things that Excel can record, certain features are beyond the capabilities of the Macro Recorder:

• Customizations of the Excel ribbon and Quick Access toolbar.
• Actions inside Excel dialogs such as Conditional Formatting or Find and Replace (only the result gets recorded).
• Interactions with other programs. For example, you cannot record copy/pasting from an Excel workbook into a Word document.
• Anything that involves the VBA Editor. This imposes the most significant limitations - many things that can be done at the programming level cannot be recorded:
  • Creating custom functions
  • Displaying custom dialog boxes
  • Making loops such as For Next, For Each, Do While, etc.
  • Evaluating conditions. In VBA, you can use the IF Then Else statement to test a condition and run some code if the condition is true or another code if the condition is false.
  • Executing code based on events. With VBA, you can use many events to run a code associated with that event (such as opening a workbook, recalculating a worksheet, changing selection, and so on).
  • Using arguments. When writing a macro in the VBA Editor, you can supply input arguments for a macro to perform a certain task. A recorded macro cannot have any arguments because it's independent and is not connected to any other macros.
  • Understanding logic. For example, if you record a macro that copies specific cells, say in the Total row, Excel will only record the addresses of the copied cells. With VBA, you can code the logic, i.e. copy the values in the Total row.

Although the above limitations set many boundaries for recorded macros, they are still a good starting point. Even if you have no idea of the VBA language, you can quickly record a macro, and then analyze its code.

Use relative references for macro recording

By default, Excel uses absolute referencing to record a macro. That means your VBA code would always refer to exactly the same cells that you selected, no matter where you are in the worksheet when running the macro.

However, it is possible to change the default behavior to relative referencing. In this case, VBA won't hardcode cell addresses, but will work relatively to the active (currently selected) cell.

To record a macro with relative referencing, click the Use Relative References button on the Developer tab. To return to absolute referencing, click the button again to toggle it off.

For example, if you record setting up a table with the default absolute referencing, your macro will always recreate the table in the same place (in this case, Header in A1, Item1 in A2, Item2 in A3).

If you record the same macro with relative referencing, the table will be created wherever you put the cursor before running the macro (Header in the active cell, Item1 in the below cell, and so on).

Select ranges by using keyboard shortcuts

When you select a cell or a range of cells using the mouse or arrow keys, Excel writes the cell addresses. Consequently, whenever you run a macro, the recorded operations will be performed exactly on the same cells. If this is not what you want, use shortcuts for selecting cells and ranges.

As an example, let's record a macro that sets a specific format (d-mmm-yy) for the dates in the table below:

For this, you record the following operations: press Ctrl + 1 to open the Format Cells dialog > Date > choose format > OK. If your recording includes selecting the range with the mouse or arrow keys, Excel will produce the following VBA code:

Running the above macro would select the range A2:B4 every time. If you add some more rows to your table, they won't be processed by the macro.

Now, let's see what happens when you select the table using a shortcut.

Put the cursor in the top-left cell of the target range (A2 in this example), start recording and press Ctrl + Shift + End. As the result, the first line of the code will look like this:

This code selects all cells from the active cell to the last used cell, meaning that all new data will be included in the selection automatically.

Alternatively, you can use Ctrl + Shift + Arrows combinations:

• Ctrl + Shift + Right arrow to select all used cells to the right, followed by
• Ctrl + Shift + Down arrow to select all used cells down.

This will generate two code lines instead of one, but the result will be the same - all cells with data down and to the right of the active cell will be selected:

Record a macro for selection rather than specific cells

The above method (i.e. selecting all used cells beginning with the active cell) works great for performing the same operations on the entire table. In some situations, however, you may want the macro to process a certain range rather than the whole table.

For this, VBA provides the Selection object that refers to the currently selected cell(s). Most things that can be done with a range, can also be done with selection. What advantage does it give to you? In many cases, you don’t need to select anything at all while recording - just write a macro for the active cell. And then, select any range you want, run the macro, and it will manipulate the entire selection.

For example, this one-line macro can format any number of selected cells as percentages:

Plan carefully what you record

The Microsoft Excel Macro Recorder captures almost all your activity, including the mistakes you make and correct. For example, if you press Ctrl + Z to undo something, that will also be recorded. Eventually, you might end up with a lot of unnecessary code. To avoid this, either edit the code in the VB Editor or stop recording, delete a deficient macro and start recording anew.

Back up or save workbook before running a macro

The result of Excel macros cannot be undone. So, before the first run of a macro, it makes sense to create a copy of the workbook or at least save your current work to prevent unexpected changes. If the macro does something wrong, simply close the workbook without saving.

Keep recorded macros short

When automating a sequence of different tasks, you may be tempted to record them all in a single macro. There are two main reasons not to do this. Firstly, it's hard to record a long macro smoothly without mistakes. Secondly, large macros are difficult to comprehend, test and debug. Therefore, it's a good idea to split a large macro into several parts. For example, when creating a summary table from multiple sources, you can use one macro to import information, another to consolidate data, and a third one to format the table.