LITMIR.BIZ

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

this macro to the shortcut key Ctrl+Shift+N.You do this by entering uppercase N in the edit box labeled Shortcut Key.

      3 Click OK.This closes the Record Macro dialog box and begins recording your actions.

      4 Select cell B3 on your worksheet, type your name into the selected cell, and then press Enter.

      5 Choose Developer ⇒ Code ⇒ Stop Recording (or click the Stop Recording button in the status bar).

      Examining the macro

      The macro was recorded in a new module named Module1. To view the code in this module, you must activate the Visual Basic Editor. (See Chapter 2 to find out more about the Visual Basic Editor.) You can activate the VB Editor in one of three ways:

       Press Alt+F11.

       Choose Developer ⇒ Code ⇒ Visual Basic.

       Choose Developer ⇒ Code ⇒ Macros, select a macro, and click Edit.

      In the VB Editor, the Project window displays a list of all open workbooks and add-ins. If the Project Explorer isn’t visible, choose View ⇒ Project Explorer. This list is displayed as a tree diagram, which you can expand or collapse. The code that you recorded previously is stored in Module1 in the current workbook. When you double-click Module1, the code in the module appears in the Code window.

      The macro should look something like this:

       Sub MyName()'' MyName Macro'' Keyboard Shortcut: Ctrl+Shift+N' Range("B3").Select ActiveCell.FormulaR1C1 = "Dick Kusleika" Range("B4").SelectEnd Sub

      The macro recorded is a Sub procedure named MyName. The statements tell Excel what to do when the macro is executed.

      Notice that Excel inserted some comments at the top of the procedure. These comments are some of the information that appeared in the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren’t really necessary, and deleting them has no effect on how the macro runs. If you ignore the comments, you'll see that this procedure has three VBA statements, the second of which is:

       ActiveCell.FormulaR1C1 = "Dick Kusleika"

      The first statement is selecting cell B3. The last statement is pressing Enter after you enter your name, which moves the active cell down one row. The middle statement, the one that does all the work, causes the name you typed while recording to be inserted into the active cell.

      Editing the macro

      After you record a macro, you can make changes to it. The macro you recorded in the previous section always inserts your name into cell B3. Edit the macro so that it enters your name in whatever cell you happen to be in when you run it. To do that, delete the first and third lines of the macro. The edited macro appears as follows:

       Sub MyName()'' MyName Macro'' Keyboard Shortcut: Ctrl+Shift+N' ActiveCell.FormulaR1C1 = "Dick Kusleika"End Sub

      This macro inserts text into the active cell because the first Select statement was removed. That same cell remains active because the second Select statement was removed.

      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.

Comparing Absolute and Relative Macro Recording

      Excel has two modes for recording — absolute reference and relative reference. These modes affect what statements Excel generates when you select cell while recording. In this section, I discuss the two modes and when to use them.

      Recording macros with absolute references

      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.

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