= True, as demonstrated in the following sample code:
ActiveCell.Font.Bold = True
The edited macro appears as follows:
Sub MyName() '' MyName Macro '' Keyboard Shortcut: Ctrl+Shift+N ActiveCell.Font.Bold = True ActiveCell.FormulaR1C1 = "Michael Alexander" End Sub
Test this new macro, and you'll see that it performs as it should.
Comparing absolute and relative macro recording
Now that you've read about the basics of the macro recorder interface, it's time to go deeper and begin recording a more complex macro. The first thing you need to understand before you begin is that Excel has two modes for recording: absolute reference and relative reference.
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. Open the Chapter 2 Sample.xlsm file and record a macro that counts the rows in the Branchlist worksheet. (See Figure 2.2.)
FIGURE 2.2 Your pretotaled worksheet containing two tables
NOTE
The sample dataset used in this chapter 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 that cell A1 is selected.
2 Select Record Macro from the Developer tab.
3 Name the macro AddTotal.
4 Choose This Workbook in the Store Macro In drop-down.
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), type = COUNTA(D2:D15), and then press Enter. This gives a count of branch numbers at the bottom of column D. The COUNTA function is used to catch any branch numbers stored as text.
8 Click Stop Recording on the Developer tab to stop recording the macro.
The formatted worksheet should look like something like the one in Figure 2.3.
FIGURE 2.3 Your post-totaled worksheet
To see your macro in action, delete the total row that 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 that you just recorded.
3 Click the Run button.
If all goes well, the macro plays back your actions perfectly and gives your table a total. 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 illustrated in Figure 2.4. The Macro dialog box will, by default, list the macros available in all open Excel workbooks (including any Add-ins that you may have installed). You can limit the list to only those macros contained in the active workbook by changing the Macros In setting to This Workbook.
FIGURE 2.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 line 2 and line 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 will examine 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. Thus, 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 that the Chapter 2 Sample.xlsm 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 toggle button from the Developer tab, as shown in Figure 2.5.FIGURE 2.5 Recording a macro with relative references
2 Before recording, make sure that cell A1 is selected.
3 Select Record Macro from the Developer tab.
4 Name the macro AddTotalRelative.
5 Choose This Workbook in the Store Macro In drop-down.
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), type = COUNTA(D2:D15), and then press Enter.
9 Click Stop
