In this lesson we'll discuss the differences between absolute references and relative references.
These are two different ways of looking at cell navigation in our X a workbook and they greatly affect
how the macro recorder is going to interpret all of our navigation.
The default setting in Excel is absolute references which means that macros will know to the actual
address of the cell that we are navigating to.
For example if I have absolute references enabled which is default and I click in a macro in a cell
B1 the VBA recorder will interpret that as a absolute reference to sell be one.
You can think of the word absolute as meaning definite or final or fixed.
If somebody tells you I know I'm 100 percent absolutely right you know that they're 100 percent certain
of something.
And in this case it's the exact same kind of idea.
The place where we're going to end up with the cell location the range is certain it's guaranteed it's
always the same thing.
If we go into a cell be one the macro recorder will treat that as an actual reference to cell be one
in comparison macros recorded with relative references are relative.
They look at navigation as relative to a starting cell compared to a starting position.
While absolute references are more concerned with the final destination where you end up relative references
are more concerned with how you get there.
So the way the macro recorder looks at navigation is in a sequence of steps like move to cells to the
right and move three cells down.
It doesn't matter what the final cell is.
It matters the journey of steps to get there which allows macros that are recorded with relative references
to be used and a lot more locations because they're not so constrained.
They don't have so many limitations because they're not bound to ending up in any specific location.
The easiest way to demonstrate the difference between these two is to actually record a macro with both.
So in this lesson we're going to record two macros one with absolute references and one with relative
references.
It's going to be the simplest kind of macro.
We're going to put our cell cursor into a cell a one click into there and in both of our macros as soon
as we start recording we're going to navigate one cell over to be one.
Enter a value in the cell and then just end the macro completely.
And that's it.
So let's begin with absolute references.
I'm going to click record macro.
I'm going to give this macro a name of.
Move right.
Absolute.
This is an example of absolute references.
Let's click OK right here.
And we are actively recording.
So I'm going to press the right arrow key on my keyboard to go into a cell be one I'm going to type
in a value here.
Let's say hello and I'm going to press control.
Plus enter to enter the value in the cell without moving.
So control center.
OK looks good to me.
So I'm going to click stop recording and we have our very first macro.
Next up is a macro using relative references.
So I'm going to delete my value in Cell B.
I'm going to navigate back into a cell A-one this time I'm going to enable the use relative reference
setting right here you'll know that it's activated because it's going to have a gray background under
the setting.
So we're all good here we can now record a macro with relative references.
Let's call this macro move right relatives click OK here we are now recording once again exact same
procedure right arrow key to go to cell be one.
I'm going to enter the value Hello control play center.
OK it's entered in the cell and now I can stop recording.
Let's take a look at both of these macros in our actual VBA editor so I'm going to open up Visual Basic
you can do that right here by clicking this key.
And right here under modules we're going to have module one that's where all of our macro code will
be collected.
Let's open that up these little lines in green are what's known as comments.
They are completely ignored by VBA.
They're just used to leave notes behind for programmers and the macro recorder will make a couple of
them for us but they're negligible.
So we can go ahead and delete them just to make these macros a bit more compact and readable.
Let's delete the ones there as well as the ones in the second macro below.
Let's also buy yourself some space so we can compare these macros.
And here we have it.
So I know that some of this syntax is still going to be a bit unfamiliar but hopefully you'll get the
general gist of what's going on here.
At the very top we have our absolute macro the one that with absolute references.
And just as I mentioned earlier I notice that all of the cell references the range references are definite
they're absolute They're fixed.
The very first line of code here selects the range that encapsulates cell be one.
Right.
A literal reference to be one.
The second line of code which is actually identical between the two macros simply writes the value hello
to that cell.
The more important line is the very first one we're selecting cell BE1 explicitly in comparison in the
relative macro.
We are starting at the active cell which represents the currently activated cell the one that they use
are selected and we're navigating with this offset method.
We're going to dive into that a little bit later.
But what it does is it is essentially navigates it moves to somewhere else in the spreadsheet.
And this is 0 1 syntax means 0 rows down one column over.
So we're moving over relative to our original cell one column over this syntax right here.
Range.
A One is a little bit confusing because this is a relative macro it's actually a little bit of what
I call that extra useless VBA code that the recorder generates for us.
We can actually go ahead and remove this it's actually a little bit confusing and the macro will still
work regardless.
This is much better.
So we're starting at an active cell.
We're moving over one column to the right.
We're selecting that cell and then in that selected cell we're writing the exact same value.
Hello.
So this one is relative from where we start.
And this one will always end up and cell be one.
Now let's take a look at how these macros will actually work in our Excel workbook.
I'm going to go ahead and delete the value and sell be one.
And the important thing here is that I'm actually going to navigate some random cell in the workbook.
It doesn't really matter where.
So let's say D6.
For me it doesn't matter where you go.
I'm in-cell D6.
I just want to make sure I want to turn off this reference.
It won't really matter because the macro has already been recorded.
I just want to go back to Axel's default.
Let's go ahead and run on two macros in a row.
So I'm going to open up my macros dialog box.
Here it is.
And let's execute move right.
Absolute.
When I execute this we're starting and cell D6.
But what happens here.
Well we have a value of Hello written in cell BE1.
Why.
Well because our macro is absolute so when we recorded it remember we literally referenced cell 1.
That's how it interpreted our movement one cell over.
It said to itself oh.
They moved into cell B1 and then they wrote the value Hello in there.
That's an absolute reference.
That's how I'm going to record it.
Let's go ahead and delete this value and sell one.
Let's go back into another cell it's going to go into D6.
Again just to kind of have an equal experiment set up.
But this time when I execute a macro I'm going to execute move right relative.
Let me just move this box a little bit out of the way here and execute this.
And now we're going to end up in cell E6.
Why.
How come when we recorded the original macro it was cell B1.
But now we're in E6 the answer of course is because of relative references relative references don't
care about the final destination they're recording relative to where we started from.
So when we started and sell a one relative references interpreted cell one as one cell to the right.
So when we started the macher from cell D6 it once again applied the same methodology one cell to the
right then right the value hello and thus the value ended up in cell E6.
To summarize absolute references target a specific cell a specific location while relative references
target a cell relative to a starting position.
And as much as it may seem like a small difference it actually has a huge impact on how successful a
macro can run.
You really have to know which option you should use if you're going to be using the macro recorder.
That's all there is to cover in this lesson and hope you're excited to tackle your very next challenge.