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!
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:
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 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).
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.
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.
Or the analogous button on the Status bar:
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:
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.
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.
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:
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:
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:
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.
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).
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:
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:
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.
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:
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.
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:
Despite many different things that Excel can record, certain features are beyond the capabilities of the Macro Recorder:
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.
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.
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).
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:
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:
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:
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.
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.
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!
Table of contents