How to record a macro in Excel
Step-by-step tutorial for beginners to record, view, run and save a macro. You will also learn some inner mechanics of how macros work in Excel.
Macros are a great way to automate repetitive tasks in Excel. If you find yourself doing the same things over and over again, record your moves as a macro and assign a keyboard shortcut to it. And now, you can have all the recorded actions performed automatically, with a single keystroke!
How to record a macro in Excel
Like other VBA tools, Excel macros reside on the Developer tab, which is hidden by default. So, the first thing you need to do is to add Developer tab to your Excel ribbon.
To record a macro in Excel, carry out these steps:
- On the Developer tab, in the Code group, click the Record Macro button.
Alternatively, click the Record Macro button on the left side of the Status bar:
If you prefer working with the keyboard rather than the mouse, press the following key sequence Alt, L, R (one-by-one, not all the keys at a time).
- In the Record Macro dialog box that appears, configure the main parameters of your macro:
- In the Macro name box, enter the name for your macro. Try to make it meaningful and descriptive, so later on you will be able to quickly find the macro in the list.
In macro names, you can use letters, numbers, and underscores; the first character must be a letter. Spaces are not allowed, so you should either keep a name single-worded starting each part with a capital letter (e.g. MyFirstMacro) or separate words with underscores (e.g. My_First_Macro).
- In the Shortcut key box, type any letter to assign a keyboard shortcut to the macro (optional).
Both uppercase or lowercase letters are allowed, but you'd be wise to use uppercase key combinations (Ctrl + Shift + letter) because macro shortcuts override any default Excel shortcuts while the workbook containing the macro is open. For example, if you assign Ctrl + S to a macro, you will lose the ability to save your Excel files with a shortcut. Assigning Ctrl + Shift + S will keep the standard saving shortcut.
- From the Store macro in drop-down list, choose where you want to store your macro:
- Personal Macro Workbook – stores the macro to a special workbook called Personal.xlsb. All the macros stored in this workbook are available whenever you use Excel.
- This Workbook (default) - the macro will be stored in the current workbook and will be available when you reopen the workbook or share it with other users.
- New Workbook – creates a new workbook and records the macro to that workbook.
- In the Description box, type a short description of what your macro does (optional).
Though this field is optional, I'd recommend you always provide a brief description. When you create a lot of different macros, it will help you quickly understand what each macro does.
- Click OK to start recording the macro.
- In the Macro name box, enter the name for your macro. Try to make it meaningful and descriptive, so later on you will be able to quickly find the macro in the list.
- Perform the actions that you want to automate (please see the recording macro example).
- When finished, click the Stop Recording button on the Developer tab:
Or the analogous button on the Status bar:
Example of recording a macro in Excel
To see how it works in practice, let's record a macro that applies some formatting to the selected cells. For this, do the following:
- Select one or more cells that you want to format.
- On the Developer tab or Status bar, click Record Macro.
- In the Record Macro dialog box, configure the following settings:
- Name the macro Header_Formatting (because we are going to format column headers).
- Place the cursor in the Shortcut key box, and press Shift + F keys simultaneously. This will assign the Ctrl + Shift + F shortcut to the macro.
- Choose to store the macro in this workbook.
- For Description, use the following text explaining what the macro does: Makes text bold, adds fill color, and centers.
- Click OK to start recording.
- Format the pre-selected cells the way you want. For this example, we use the bold text formatting, light blue fill color and center alignment.
Tip. Do not select any cells after you've started recording the macro. This will ensure that all the formatting applis to the selection, not a specific range.
- Click Stop Recording on either the Developer tab or the Status bar.
That's it! Your macro has been recorded. Now, you can select any range of cells in any sheet, press the assigned shortcut (Ctrl+ Shift + F), and your custom formatting will be immediately applied to the selected cells.
How to work with recorded macros in Excel
All main options Excel provides for macros can be accessed via the Macro dialog box. To open it, click the Macros button on the Developer tab or press the Alt+ F8 shortcut.
In the dialog box that opens, you can view a list of macros available in all open workbooks or associated with a particular workbook and make use of the following options:
- Run - executes the selected macro.
- Step into - allows you to debug and test the macro in the Visual Basic Editor.
- Edit - opens the selected macro in the VBA Editor, where you can view and edit the code.
- Delete - permanently deletes the selected macro.
- Options – allows changing the macro's properties such as the associated Shortcut key and Description.
How to view macros in Excel
The code of an Excel macro can be viewed and modified in the Visual Basic Editor. To open the Editor, press Alt + F11 or click the Visual Basic button on the Developer tab.
If you see the VB Editor for the first time, please do not feel discouraged or intimidated. We are not going to talk about the structure or syntax of the VBA language. This section will just give you some basic understanding of how Excel macros work and what recording a macro actually does.
The VBA Editor has several windows, but we'll focus on the two main ones:
Project Explorer - displays a list of all open workbooks and their sheets. Additionally, it shows modules, user forms and class modules.
Code Window - this is where you can view, edit and write VBA code for each object displayed in the Project Explorer.
When we recorded the sample macro, the following things occurred in the backend:
- A new module (Moduel1) was inserted.
- The VBA code of the macro was written in the Code window.
To see the code of a specific module, double-click the module (Module1 in our case) in the Project Explorer window. Normally, a macro code has these parts:
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:
How to run a recorded macro
By running a macro, you tell Excel to go back to the recorded VBA code and execute the exact same steps. There are a few ways to run a recorded macro in Excel, and here are the fastest ones:
- If you've assigned a keyboard shortcut to the macro, press that shortcut.
- Press Alt + 8 or click the Macros button on the Developer tab. In the Macro dialog box, select the desired macro and click Run.
It is also possible to run a recorded macro by clicking your own button. Here are the steps to make one: How to create a macro button in Excel.
How to save macros in Excel
Whether you recorded a macro or wrote VBA code manually, to save the macro, you need to save the workbook as macro enabled (.xlms extension). Here's how:
- In the workbook containing the macro, click the Save button or press Ctrl + S.
- In the Save As dialog box, choose Excel Macro-Enabled Workbook (*.xlsm) from the Save as type drop-down list, and then click Save:
Excel macros: what is and what isn't recorded
As you have just seen, it's quite easy to record a macro in Excel. But to create effective macros, you need to understand what's going on behind the scenes.
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.
Useful tips for recording macros in Excel
Below you will find a few tips and notes that can potentially save you a lot of time and nerves making your learning curve smoother and macros more efficient.
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).
Notes:
- When using relative references, be sure to select the initial cell before you start recording a macro.
- Relative referencing does not work for everything. Some Excel features, e.g. converting a range to a table, require absolute references.
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.
I hope this tutorial has given you some insight on how to record a macro in Excel. Anyway, I thank you for reading and hope to see you on our blog next week!
You may also be interested in
Table of contents
