(Getting Started with Excel
Macros)
Recording macros with absolute references
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
Branch list worksheet. (See Figure 2.)
TIP The sample
dataset used in this Part can be found on this book's companion website. See
this book's Introduction for more on the companion website.
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:
6.
Select cell
A16 and type Total in the cell.
7.
Select the
first empty cell in Column D (D16) and type = 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.
8.
Click Stop
Recording on the Developer tab to stop recording the macro.
The
formatted worksheet should look something like the one in Figure 3.
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. 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 4.
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 two and four 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.
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 companion 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 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.
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.
No comments:
Post a Comment