In this screencast I am going to show you the difference between absolute and relative
referencing when recording VBA macros, and I have two examples that will show you this.
First example, we want to be able to create a macro so that I can click a cell and then
I can run the macro and it's going to put in five days of the week, Monday through Friday
in five adjacent cells.
So I am going to first make sure that this relative references is off, because now we
are using absolute referencing, and I am going to start a macro, this will be called "Day
Titles," and I am going to click here and I am just going to put in the five days of
the week, and I am going to stop recording.
Now if I try to rerun this, so let's say I rerun it and I want the weekdays to be placed
there, I'll click here, Alt+F8, and I am going to run that macro.
But what you'll notice is that it didn't do anything.
That's because we're in the absolute referencing mode, it just rewrote over A1 through E1 because
those are absolute references.
I'll show you now how we can circumvent that.
I am going to go ahead and delete these cells, and we're going to change this to use relative
references.
So I am going to start here in cell A1, I am going to record macro, I am going to call
this "Day Titles Relative."
Now it's recording, I am going to put in Monday, and the rest of the days.
Now I am going to do "Stop Recording."
If I try to run this, the relative one, it will now put those five days of the week in
whatever active cell that I want.
So if I do Alt+F8, run that relative, it will work.
So this is how you can go from absolute referencing, which is always going to be the same cells,
to relative based upon where that active cell is.
The second example, I am going to go ahead and delete everything, and I am going to clear
my macros, so I got some data here, and what we want to do is set up a macro that's going
to take a selection in general and we can just sort down.
So there's already a built in sort function, sort largest to smallest.
We just want to make a macro key for just any selection in general.
You wouldn't really do this because it's already built in but I am just trying to show a point
here.
So what I am going to do, i am going to record a macro, I am going to unselect that relative
references to show you what happens when we're in absolute referencing mode.
So I am going to record macro, I'll call this "Sort Down," and I am going to highlight this,
and I am going to go to "Sort", and filter largest to smallest, and then we want to stop
recording.
So now I want to see if it will work on this selection, so I am going to do Alt+ F8, I
am going to run that sort down, and you notice because we're in absolute referencing mode
it just went to the same cells, it went to A1 to A8, and in fact if I open up using Alt+F11
the Visual Basic Editor you see that it's always going to select that range, and it's
just going to sort those cells.
But, if I want to create a macro that's going to sort any selection in general we have to
use relative referencing mode.
So I am going to switch this up a little bit, so I just re-randomize these numbers.
So I am going to select these, and that's going to be my selection.
I am going to make sure relative referencing is on, so I've clicked that, it's green, and
I am going to record a macro, and I am going to rename this "Sort Down."
I will rewrite over the old one, so now it's recording , and I am just going to go home.
Sort filter largest to smallest, and it did that, and now I can stop recording.
We have to make one small change in our macro, the code.
So I do Alt+F11, and it brings up the code that was written, and instead of just the
active cell range we just have to change that to selection.
Whenever you're working with relative referencing and you're working with a raise you have to
go in and change the range that it's selected to selection, so it's going to use any selection
in general.
So I go back to, using the Alt+F11 back here, and I am going to highlight this.
We do Alt+F8 to bring up our macros, and I am going to sort down, and when I run that
it sorts that selection, and you notice that the selection is in a different location than
when I recorded, and it' a different size.
So this is how you can just do any selection in general.
So that is it for this screencast.