Excel Macros For Dummies. Alexander Michael

Скачать книгу

single statement causes the name you typed while recording to be inserted into the active cell.

Testing the macro

      Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:

      ❯❯ Press Alt+F11.

      ❯❯ Click the View Microsoft Excel button on the VB Editor toolbar.

      When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell.

      

In the preceding example, notice that you selected the cell to be altered before you started recording your macro. This step is important. If you select a cell while the macro recorder is turned on, the actual cell that you selected is recorded into the macro. In such a case, the macro would always format that particular cell, and it would not be a general-purpose macro.

Editing the macro

      After you record a macro, you can make changes to it (although you must know what you’re doing). For example, assume that you want your name to be bold. You could re-record the macro, but this modification is simple, so editing the code is more efficient. Press Alt+F11 to activate the VB Editor window. Then activate Module1 and insert the following statement before the End Sub statement:

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

      

The sample dataset used in this chapter can be found on this book’s companion website at www.dummies.com/go/excelmacros.

      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

Скачать книгу