Excel VBA Tutorial – #2 Macro recording (including the difference between absolute and relative)

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!

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

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.

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:

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.

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”:

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.

OK, now let’s use this red Ferrari method in our sheet as well. We record a new macro like this:

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

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.

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