Excel VBA — How Absolute and rel­a­tive ref­er­ences in macros | Intro­duc­tion to macros and VBA 05

Today, we will talk about absolute and rel­a­tive ref­er­ences in macros as well as dif­fer­ent ways to run our macro. 

Today, we are going to record a macro, where we will put the TODAY func­tion. How­ev­er this time, we will press ENTER instead of the Ctrl + Enter short­cut. We can start with cell A5. Let’s use the Record but­ton locat­ed in the sta­tus bar tab this time instead of using the Devel­op­er tab (Fig. 1)

Fig. 1 Record button
Fig. 1 Record button

Let’s call our macro ‘Today2’. We don­t’t need any short­cut but let’s try to add a short­cut here by click­ing the Ctrl + D com­bi­na­tion and see what hap­pens lat­er on. We want to save our macro in the Per­son­al Macro Work­book. When we press OK, Excel won’t allow us to save our macro, because we already have a macro with this short­cut. Let’s delete the short­cut then. When we use a short­cut that already exists in Excel, macro short­cut for macro will be more impor­tant than the default short­cut, e.g. Ctrl key. Thus, we should­n’t use such kind of short­cuts. Let’s press OK (Fig. 2)

Fig. 2 Record Macro window
Fig. 2 Record Macro window

Now, we just write TODAY in cell A5. It’s impor­tant that the Use Rel­a­tive Ref­er­ences isn’t high­light­ed (Fig. 3)

Fig. 3 Use Relative References not highlighted
Fig. 3 Use Rel­a­tive Ref­er­ences not highlighted

After we fin­ished writ­ing TODAY, let’s press ENTER. After writ­ing the for­mu­la, we can see that Excel went down one cell by default. Now, we just press STOP on the sta­tus bar (Fig. 4)

Fig. 4 Stop on the status bar
Fig. 4 Stop on the sta­tus bar

After we stopped our macro, how can we run it now? We can go to the Devel­op­er tab and press the View Macros com­mand or just click the Alt + F8 key short­cut (Fig. 5)

Fig. 5 View Macros command
Fig. 5 View Macros command

In the win­dow that has appeared, we can see all avail­able macros, which are in All Open Work­books. Now, we have only two. We can see that before the macro name, we have the file name. We can choose macros from many files (Fig. 6)

Fig. 6 File name and files

For us, it’s impor­tant to run the Today2 macro, so let’s select it and press the Run but­ton (Fig. 7)

Fig. 7 Run button
Fig. 7 Run button

Excel has writ­ten the TODAY func­tion in our select­ed cell, which is C5 and has gone to cell A6 (Fig. 8)

Fig. 8 Excel in cell A6
Fig. 8 Excel in cell A6

Let’s try a sim­i­lar thing with the firs macro. Let’s press Alt + F8 short­cut, select the Today2 macro and press RUN. Now, let’s see what exact­ly our macro has done. Let’s press Alt + F11. We should see our first macro and our new macro Today2, as well as some com­ments (Fig. 9)

Fig. 9 Our macros
Fig. 9 Our macros

Let’s delete the com­ments as we don’t need them and let’s locate the lines so that they are clear­er to read. Now, we have two lines of the code. The first line of the Today2 macro is very sim­i­lar to the Today macro. How­ev­er, instead of ‘Selec­tion’, we have ‘Active­Cell’. It means that our for­mu­la is writ­ten in the active cell. After the line that con­tains our macro, Excel will always go to cell A6, We can see it in the next line of ‘Range (“A6”). Select. ‘Select’ means the action, i.e. that we have select­ed the cell (Fig. 10)

Fig. 10 Code lines
Fig. 10 Code lines

This is how macros are work­ing. If we select a range, the TODAY func­tion will be placed only in the active cell. Then, Excel will go to cell A6.

Let’s try a sim­i­lar macro. The only dif­fer­ence is that this time we will be using rel­a­tive ref­er­ences. So let’s do it once again: click on the Record Macro but­ton (1), let’s call it Today3 (2), This Work­book (3) and press OK (4) (Fig. 11)

Fig. 11 Recording a new macro
Fig. 11 Record­ing a new macro

We’re writ­ing every­thing once again: TODAY func­tion in cell A7 (1), then Enter (2), then the Stop Record­ing but­ton (3) (Fig. 12)

Fig. 12 The process of recording
Fig. 12 The process of recording

This time we were using rel­a­tive ref­er­ences. Let’s press Alt + F11. Where is our macro? It’s not in the PERSONAL.XLSB file but in our Macro file, where we record. It means that we have to find Mod­ules (1), where Mod­ule 1 should be added (2). Let’s dou­ble-click it and we can see our macros (Fig. 13)

Fig. Module 1 with our new macro
Fig. Mod­ule 1 with our new macro

We don’t need com­ments so let’s delete them again. Let’s also adjust the size of the win­dows so that we can see it clear. We have the TODAY 2 and TODAY 3 func­tions here. We’ve found our macro (Fig. 14)

Fig. 14 Both macros
Fig. 14 Both macros

There is one more way to find our macros. Let’t go to the Devel­op­er (1) tab and go to the View Macros but­ton (2). In the win­dow that appeared we can see that we have many options there like Run, Step into, Edit, Delete and Options (3). For our macro, the Options but­ton is the most sig­nif­i­cant one, because here we can see short­cuts for our macro (4) (Fig. 15)

Fig. 15 Macro options
Fig. 15 Macro options

If we delete D, this macro will delete the short­cut if I press OK. How­ev­er, we’re not going to do that. Let’s close the Macro Options win­dow and let’s focus on the Today3 macro. Since this file is still open, we don’t see any names before. Let’s go to the Edit option of this macro (Fig. 16)

Fig. 16 Edit option
Fig. 16 Edit option

This option leads us to the exact win­dow our macro is stored. The first line in the Today3 macro is exact­ly the same as in Today2 macro. How­ev­er, the sec­ond line is total­ly dif­fer­ent. First of all, both lines start with ‘Active­Cell’. Then, we have Off­set (1, 0) — by one row and 0 columns. It means that we went one cell down. Then, VBA tells us that we select­ed Range A1. In fact, we did­n’t select A1 or any oth­er cells. Some­times, it’s hard to under­stand VBA due to some inputs. It’s true that we did­n’t select A1, but when we went one cell down from the active cell, we select­ed a range that is the same size as range A1 cell. 

Once again. First we start with Active Cell, then there is a dot, which means that we take anoth­er step, then choos­ing the Off­set, one row, 0 columns, then we have the Range A1 and what we do with this range, which is select­ing the range (Fig. 17)

Fig. 17 Today3 macro
Fig. 17 Today3 macro

Let’s go back to our work­sheet and select cell A8, then go once again to our macro by click­ing Alt + F11. This time, we press Play (Run Sub) in our VBA edi­tor or F5 as a short­cut (Fig. 18)

Fig. 18 Run Sub command
Fig. 18 Run Sub command

What we notice here is that Excel has writ­ten the Today func­tion in cell A8 and went one cell below (Fig. 19)

Fig. 19 TODAY function and once cell below
Fig. 19 TODAY func­tion and once cell below

Let’s select a range this time and press F5 (Fig. 20)

Fig. 20 Range selected
Fig. 20 Range selected

What we’re notic­ing is that the val­ue has been writ­ten only in the active cell, although we had select­ed the whole range. Then, Excel went one cell down (Fig. 21)

Fig. 21 TODAY function and one cell down
Fig. 21 TODAY func­tion and one cell down

Sum­ming up, there is a small dif­fer­ence between rel­a­tive and absolute ref­er­ences, but still, we have to con­sid­er whether we want to use the first one or the second. 

https://www.youtube.com/watch?v=j0013EqeqNo&t