Excel VBA Tutorial – #2 Macro recording (including the difference between absolute and relative)
Summary: Programming sure can make your life much easier. But to write useful Excel VBA code, you have to know hundreds of commands, right? Or is there a way to reduce the boring (sometimes difficult) command-learning process? Yes there is – and that’s why I teach you about the Macro Recording function in this article!
Download the Sample Workbook
Download the sample file with VBA code
Macro_Recording.xlsm (80 Kb)
Click on a link to jump to that section…
#1 – About Macro Recording
In the previous tutorial we were introduced to the Visual Basic Editor and wrote our first Excel macro.
Now it’s time to move on and learn about another promising and useful built-in function of Excel: Macro Recording. It does what its name suggests– records your actions in Excel to macros.
What kind of actions does it record, you ask? Everything! All things you do in Excel from the start of recording until you stop it.
After that, the recorded actions are stored in the Visual Basic Editor, and you can modify them or copy them to another workbook.
This is a much easier way to learn programming than memorizing all the commands you would ever need. To learn how it works, we are going to use the Developer tools in Excel. Let’s get started!
#2 – Start Recording!
Open a new workbook and navigate to the Developer Ribbon.
If you don’t have the Developer Ribbon showing, follow these instructions.
Here, you see the button “Macros” right next to “Visual Basic”. This is like a directory; it contains our recorded or manually programmed macros.
Now it is empty, because we opened a whole new workbook. We will come back to this after we record our macro.
The next button right of “Macros” is the “Record Macro” button – this is what we are going to try out now.
Click on it, and type in a desired Macro name for your macro in the dialog box (the window that pops up) – I will name it “Recording1” now.
There are some more parameters you can set up for the recording:
- “Shortcut key”: once the macro is recorded, using this shortcut key replays the macro. This is just a comfort thing: if you use a macro often, you can use its shortcut key instead of searching it from the “Macros” list. Here we type “m” for a shortcut key.
- ”Store macro in” should not be modified. Let’s just stay with the method of storing our macro in “This Workbook”. The “Personal Macro Workbook” is a special kind of Excel workbook, and we do not need it right now. “New Workbook” would open a new workbook to save the recording in. You can learn more about the Personal Macro workbook here.
- “Description”: You can write here notes or anything that makes it easier to identify your macro in the VB Editor.
If you have set the above mentioned parameters, you should see a similar window. Click OK, and start working on Sheet1.
#3 – Let’s Record Actions!
I am going to write numbers starting from 1 to 3 in the first three cells of column A.
Here you can see what I typed in:
If you have also typed in the same, press the “Stop Recording” button. Notice that this button was the “Record Macro” before the recording was started.
Now we are finished with our first recorded macro in Excel. Let’s check out how this recording looks like in the VB Editor.
Open the Editor, and navigate to the Modules directory, to find the recording in “Module1”.
Small aside on Modules vs Subroutines
But wait, why is it called “Module1” when we named our macro “Recording1”?
It is because the recording is NOT a Module – a macro is a Subroutine (a “Sub”) stored in a module.
One module can have lots of Subs in itself, because the module is a different type of object.
You can imagine it like the relationship between a column and the cells in the column. There are a lot of cells in a column, and if you rename a cell, the column name doesn’t change.
So, this is an important principle: we can’t record without Modules – if you start to record a macro in an empty workbook, Excel will first automatically insert a new module (“Module1”) where it can store your recorded macro (“Recording1” for example).
Macro is just a friendlier name for Subroutine.
#4 – Analyzing the Code
So, now that we cleared up the difference between Modules and Subroutines, let’s look at our macro in Module1.
You can see its name after the prefix “Sub”.
Under the title, there are green lines with apostrophes – they are handled as comments, and not as commands. The computer just skips them while running the macro. They are also marked green automatically, to make the reading and debugging more comfortable. The Keyboard Shortcut is also noted as a comment.
After the green comment lines comes the most interesting part of our code. The black lines are the real, executable commands. The computer will execute these commands in Excel when you start the macro.
How do they work? Let’s examine them line-by-line:
- FormulaR1C1 = “1”: The active cell (which is actually selected on the sheet) should get the value of 1. Why does it look so complicated? What does FormulaR1C1 do? I will explain this in the next section (“References – relative and absolute”)
- Range(“A2”).Select: selecting cell “A2”. This is self-explanatory, only the object “Range” is new for us. Ranges are areas in Excel. They can be used as reference for single cells or many cells. Or you can also select whole rows and columns using the object “Range”.
- The following lines (until “End Sub”) do the same as explained above, the only difference is in the cell coordinates and the inserted values.
- Question: Why isn’t there a line in the code to select cell “A1”? Shouldn’t it be there in the first place? Yes, you are right, it should. But the answer is simple: when I started the recording, Cell(A1) was already selected.
Tip: Use the Macro Recorder to Write Code
As you can see, Macro Recording is pretty straight-forward. It only records the taken actions, and therefore it is quite useful for “writing” code instead of typing everything from scratch.
If you are going to use macro recording often in your daily work, you’ll see that the recording can always be modified to be even more effective.
It also records mouse wheel movements, like this: ActiveWindow.SmallScroll Down:=80. You can just delete such lines without any worry, as they won’t affect program execution and actually speed the macro up, because Excel won’t need to repaint the screen.
I’ll give you more handy tips like this throughout the tutorial series, because I want to show you the ropes, and help you get the most out of VBA programming with the least time and energy invested.
#5 – References – Absolute and Relative
Okay, we now understand how macro recording works.
There is still one topic I haven’t covered yet… so it’s time to get to it: range references.
And first we’ll talk about R1C1…
We encountered a strange command with “ActiveCell.FormulaR1C1” in the recorded code. What does it have to do with references?
Well, R1 means “Row 1” and C1 means “Column 1”.
It is used to reference the cell in a different notation than “A1”. All you have to know is that you can change Excel’s settings, so that the columns are labelled with numbers instead of alphabetically.
If you would like to go deeper in the topic of R1C1 notation and references, here’s a comprehensive article for it.
As we saw in the code, if you select a cell in the sheet and type in a value, it will be recorded just like ActiveCell.FormulaR1C1 = “Your Value”.
Of course, you have to select the desired cell beforehand. That’s no rocket science. But it can be frustrating (and it also slows down the macro) to select every single cell you want work with.
Like in our example recording we typed in 3 values in 3 different cells – and we used 3 cell selection commands to do this. That is just not effective.
And what happens, if we want to insert the “1”, “2” and “3” values not in column A, but in B?
Can we run the same recorded macro and populate cells B1, B2 and B3?
Try it – start the recorded macro with Cell(B1) selected. To do this, click on the button “Macros”:
Next, select Recording1, and click on “Run”!
Before I do that as well, I will briefly tell you about the options you have in this dialog box:
- Run: obviously executes the selected macro.
- Step into: starts the macro execution but step-by-step! I use this function often, when I want to check each commands result. You can step to the next command using the “F8” key.
- Edit: brings you to VBEditor.
- Delete: deletes the selected macro.
- Options: here you can modify the shortcut key and the description of the macro.
What happened using absolute references?
OK I ran the macro as well and got this result.
Cell B1 has the value 1. But cells B2 and B3 are still blank.
What is the reason for this? Well, let’s see.
Activecell (the selected cell) was Cell(B1) when I started the macro. So it got the value “1”.
After that, things went bad for me.
Because I recorded the macro by selecting absolute cell references A2 and A3 for the values “2” and “3”, we got the result that cells A2 and A3 were written with the values “2” and “3” and not cells B2 and B3. (If I had deleted all the data of the sheet before running the macro, A1 would be empty now)
So we can conclude that if we run our macro anywhere on the sheet, the selected cell will get the value “1”, while A2 and A3 will be populated with “2” and “3”.
How could we use a recorded macro to insert values anywhere in the sheet? (And not just in the cells used during recording)… for example, in cells B1, B2 and B3… or cells C1, C2 and C3.
We have to use relatives instead of absolutes!
To do this, you have to click on this button:
What does this do?
It affects all new macro recordings and changes the cell selection method – the Macro Recorder selects new cells relative to the previously active cell.
Picture it like finding your car in a big parking lot.
You can of course remember the exact location of your car – but this lot is huge, and maybe you tend to forget these kind of things where you parked.
Then you can just rely on the fact that there is a red Ferrari parked at the end of your line. So when you want to go home, you just search for your own car relative to that red Ferrari (assuming it’s still there J ), and not in the whole parking lot.
Recording with relative references
OK, now let’s use this red Ferrari method in our sheet as well. We record a new macro like this:
- Select Cell(B1)
- Click on “Use Relative References”
- Click on “Record Macro”
- Add these arguments » (see screenshot)
- Enter the values from 1 to 3 in column B
- Click on Stop Recording when finished.
Comparing relative references to absolutes
Now we did (almost) the same as with the absolute references in Recording1.
The only difference is that we used relative references and the values are entered in column B.
So we assume that the recorded code should look similar to the first recording.
Go to the VBEditor, and examine the code:
The first command line is the same.
Then comes the difference with this – ActiveCell.Offset(1, 0).Range(“A1”).Select.
Relative referencing uses offsetting to the previously selected cell.
We can say that the previously selected cell is the “red Ferrari”, and the later selected cell is our car.
How far is our car to the Ferrari?
This is determined by the parameters in the brackets: (1,0). The first parameter stands for the rows, and the second for the columns.
Offset(1, 0) means our new cell is 1 row under the previously selected cell. The offsetting rule is also applicable on columns as well. Furthermore, it can be a negative value if we are going “backwards”.
Testing relative referenced code
Now select any cell on the sheet and run macro Recording2.
The result has met our requirements! We can now use one macro with the same effect anywhere on the sheet.
This should help you a lot, if you have similar tasks in your daily processes, where you don’t want absolute references.
#6 – Summary
Macro recording is one of the best Excel features. It is a favorite of mine, and I recommend it to everyone who wants to start learning VBA.
I still rely on it when I have to use rare commands, which I know, but do not know the precise syntax of. With this method you can be sure that your recorded code will be written in a way that Excel can execute it.
Don’t be afraid to play with Macro Recording! You can just delete your recording anytime, and record something else instead, whatever suits your needs more!
And remember to choose between relative and absolute referencing depending on what you need.
«« Previous Tutorial | Next Tutorial (coming soon) »»
#7 – About the Author
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.
Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!