Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 75

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.