Excel’s default recording mode is in absolute reference. As you may know, the term absolute reference is often used in the context of cell references found in formulas. When a cell reference in a formula is an absolute reference, it does not automatically adjust when the formula is pasted to a new location.
The best way to understand how this concept applies to macros is to try it out. Open the Chapter 1 Sample File.xlsx file and record a macro that counts the rows in the Branchlist worksheet. (See Figure 1-2.)
FIGURE 1-2: Your pre-totaled worksheet containing two tables.
Follow these steps to record the macro:
1. Before recording, make sure cell A1 is selected.
2. Select Record Macro from the Developer tab.
3. Name the macro AddTotal.
4. Choose This Workbook for the save location.
5. Click OK to start recording.
At this point, Excel is recording your actions. While Excel is recording, perform the following steps:
1. Select cell A16 and type Total in the cell.
2. Select the first empty cell in Column D (D16) and enter = COUNTA(D2:D15).
This gives a count of branch numbers at the bottom of column D. You need to use the COUNTA function because the branch numbers are stored as text.
3. Click Stop Recording on the Developer tab to stop recording the macro.
The formatted worksheet should look something like the one in Figure 1-3.
FIGURE 1-3: Your post-totaled worksheet.
To see your macro in action, delete the total row you just added and play back your macro by following these steps:
1. Select Macros from the Developer tab.
2. Find and select the AddTotal macro you just recorded.
3. Click the Run button.
If all goes well, the macro plays back your actions to a T and gives your table a total. Now here’s the thing: No matter how hard you try, you can’t make the AddTotal macro work on the second table (G1:I15 in Figure 1-3). Why? Because you recorded it as an absolute macro.
To understand what this means, examine the underlying code. To examine the code, select Macros from the Developer tab to get the Macro dialog box you see in Figure 1-4.
FIGURE 1-4: The Excel Macro dialog box.
Select the AddTotal macro and click the Edit button. This opens the Visual Basic Editor to show you the code that was written when you recorded your macro:
Sub AddTotal()
Range("A16").Select
ActiveCell.FormulaR1C1 = "Total"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=COUNTA(R[-14]C:R[-1]C)"
End Sub
Pay particular attention to lines 2 and 4 of the macro. When you asked Excel to select cell range A16 and then D16, those cells are exactly what it selected. Because the macro was recorded in absolute reference mode, Excel interpreted your range selection as absolute. In other words, if you select cell A16, that cell is what Excel gives you. In the next section, you take a look at what the same macro looks like when recorded in relative reference mode.
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. 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 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