Читать книгу Excel Macros For Dummies - Dick Kusleika - Страница 17

In the example in the preceding section, you selected cell B3 while recording a macro and Excel dutifully recorded a statement that selects cell B3. This is an example of an absolute reference and it’s the default mode when recording macros. 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 1 Sample File.xlsx file and record a macro that counts the rows in the Branchlist worksheet. (See Figure 1-2.)

The sample dataset used in this chapter can be found on the book’s companion website.

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:Select cell A16 and type Total in the cell.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.

6 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.

To see your macro in action, delete the total row you just added and play back your macro by following these steps:

1 Click Macros on 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 the macro using absolute references.

To understand what this means, examine the underlying code. To examine the code, click Macros on the Developer tab to open the Macro dialog box, as shown in Figure 1-4.

FIGURE 1-3: Your post-totaled worksheet.

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.