Recording macros with relative references
In the context of Excel macros, relative means relative to the currently active cell. So you should use
caution with your active cell choice — both when you record the relative reference macro and when
you run it.
First, make sure the Chapter 1 Sample File.xlsx file is open. (This file is available on this book’s com-
panion website.) Then, use the following steps to record a relative reference macro:
1. Select the Use Relative References option from the Developer tab, as shown in Figure 1-5.
2. Before recording, make sure cell A1 is selected.
3. Select Record Macro from the Developer tab.
4. Name the macro AddTotalRelative.
5. Choose This Workbook for the save location.
6. Click OK to start recording.
7. Select cell A16 and type Total in the cell.
8. Select the first empty cell in Column D (D16) and type = COUNTA(D2:D15).
9. Click Stop Recording on the Developer tab to stop recording the macro.
Figure 1-5: Recording a macro with relative references.
At this point, you have recorded two macros. Take a moment to examine the code for your newly-
created macro.
Select Macros from the Developer tab to open the Macro dialog box. Here, choose the
AddTotalRelative macro and click Edit.
Again, this opens the Visual Basic Editor to show you the code that was written when you recorded
your macro. This time, your code looks something like the following:
Sub AddTotalRelative()
ActiveCell.Offset(15, 0).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “Total”
ActiveCell.Offset(0, 3).Range(“A1”).Select
ActiveCell.FormulaR1C1 = “=COUNTA(R[-14]C:R[-1]C)”
End Sub
Notice that there are no references to any specific cell ranges at all (other than the starting point
"A1"). Let’s take a moment to take a quick look at what the relevant parts of this VBA code really
mean.
Notice that in line 2, Excel uses the Offset property of the active cell. This property tells the cursor
to move a certain number of cells up or down and a certain number of cells left or right.
The Offset property code tells Excel to move 15 rows down and 0 columns across from the active
cell (in this case, A1). There’s no need for Excel to explicitly select a cell as it did when recording an
absolute reference macro.
To see this macro in action, delete the total row for both tables and do the following:
1. Select cell A1.
2. Select Macros from the Developer tab.
3. Find and select the AddTotalRelative macro.
4. Click the Run button.
5. Now select cell F1.
6. Select Macros from the Developer tab.
7. Find and select the AddTotalRelative macro.
8. Click the Run button.
Notice that this macro, unlike your previous macro, works on both sets of data. Because the macro
applies the totals relative to the currently active cell, the totals are applied correctly.
For this macro to work, you simply need to ensure that
➤ You’ve selected the correct starting cell before running the macro.
➤ The block of data has the same number of rows and columns as the data on which you
recorded the macro.
Hopefully, this simple example has given you a firm grasp of macro recording with both absolute and relative references.
