Excel VBA 24-Hour Trainer. Tom Urtis

Figure 2.14

If you are working in a later version of Excel, click the Stop Recording button on the Developer tab in the Ribbon, as shown in Figure 2.15. Clicking the Stop Recording button ends the recording session, and you have created your macro.

Figure 2.15

      HEY, MY STOP RECORDING BUTTON DISAPPEARED!

      If you are using Excel version 2003 or earlier, the Stop Recording toolbar might seem to suddenly disappear from time to time. This is almost always due to unwittingly closing that toolbar by clicking the X close button on its title bar instead of the Stop Recording button. It has happened to the best of us. To show the Stop Recording toolbar again, start to record a new macro, then from the worksheet menu click ViewToolbarsStop Recording. Click the Stop Recording button to end the macro, and the next time you record a macro, the Stop Recording toolbar will be its normal visible self.

      If you are working in version 2007 or later, no worries. The Stop Recording button on the Ribbon does not disappear; it only reverts to Record Macro when clicked.

      Running A Macro

      You have many ways to run a macro, most of which are demonstrated in later lessons. As you will see, the method(s) you choose for running your macros may depend on complex reasons such as the workbook design, or may be based on a simpler factor such as what feels most intuitive and convenient for you. To wrap up this lesson, following are a couple of commonly used options for running your macros.

      The Macro Dialog Box

When you create recorded macros, their names will appear listed in a dialog box called, appropriately enough, the Macro dialog box. To show the Macro dialog box in version 2003 or earlier, click the Run Macro button on the Visual Basic toolbar, as shown in Figure 2.16. The title of that button, Run Macro, is something of a misnomer because you are not actually running a macro when you've clicked the button. All you're doing is displaying the Macro dialog box, from which you can run a macro but also edit and examine macros.

Figure 2.16

In versions later than 2003, the button to click is more logically labeled Macros, as shown in Figure 2.17.

      NOTE Regardless of the Excel version, pressing Alt+F8 displays the Macro dialog box – no mouse clicks needed.

Figure 2.17

Figure 2.18 shows the Macro dialog box with the one and only mySort macro listed. As you create more macros in this workbook, their names are listed in the Macro dialog box in alphabetical order. To run your macro, select its name in the list and click the Run button, as indicated by the arrows. You could also run the macro by double-clicking its name in the list.

Figure 2.18

      Shortcut Key

      Recall that you assigned the shortcut key Ctrl+Shift+S to this macro at the start of the macro recording process. Because you did that, you do not need to bother with the Macro dialog box if you don't want to; you can run the mySort macro simply by pressing Ctrl+Shift+S.

      Try It

      In this lesson, you practice creating a recorded macro.

      Lesson Requirements

      To get the sample workbook file, you can download Lesson 2 from the book's website at www.wrox.com/go/excelvba24hour.

      Hints

      Name your macros with a word or concise phrase that is easy to read and gives an idea about what the macro does. For example, a macro named Print_Expense_Report is more descriptive than Macro5.

      Step-by-Step

      Start with a worksheet on which some cells contain numbers that were manually entered, and other cells contain numbers produced by formulas, such as in the downloadable budget workbook shown in the video for this lesson. I have a number of steps in this “Try It” lesson to help demonstrate the value of a macro that can automatically perform tedious, recurring manual tasks with a simple keyboard shortcut or click of a button.

      Create a macro that fills the manually entered numeric cells with one color, and the formula-containing numeric cells with another color:

      1. Click the Record Macro button to turn on the Macro Recorder.

      2. In the Record Macro dialog box, name the macro My_Macro and assign it the shortcut Ctrl+Shift+W.

      3. Click OK to start recording your My_Macro macro.

      4. Click the button above row 1 and to the left of column A to select the all the worksheet cells.

      5. Show the Format Cells dialog box. Right-click any selected cell and select Format Cells from the menu, or press the Alt+O+E keyboard shortcut.

      6. In the Format Cells dialog box, click the Fill tab. Click the No Color button and click OK to remove the fill colors from all cells.

      7. With all the worksheet cells still selected, press the F5 key to show the Go To dialog box. Click the Special button.

      8. In the Go To Special dialog box, select the option button for Constants, leave the Numbers check box selected, and deselect the check boxes for Text, Logicals, and Errors. Click OK.

      9. Repeat Step 5 to show the Format Cells dialog box.

      10. In the Format Cells dialog box, click the Fill tab, select a color from the palette, and click OK.

      11. Repeat Step 4 to select all the worksheet cells.

      12. Repeat Step 7 to show the Go To Special dialog box.

      13. In the Go To Special dialog box, select the option button for Formulas, leave the Numbers check box selected, and deselect the check boxes for Text, Logicals, and Errors. Click OK.

      14. Repeat Step 5 to show the Format Cells dialog box.

      15. In the Format Cells dialog box, click the Fill tab, select a color from the palette that is different from the color you selected for Constants in Step 10, and click OK.

      16. Select any cell on the worksheet to deselect all the selected special cells.

      17. Turn off the Macro Recorder by clicking the Stop Recording button.

      18. Before running your new macro to see it in action, repeat Steps 4, 5, and 6 to remove the fill color from all cells.

      19. Show the Macro dialog box to run your macro. You can either click the Developer tab on the Ribbon and then click the Macros icon in the Code panel, or you can press the Alt+F8 keyboard shortcut.

      20. To run your My_Macro macro from the Macro dialog box, select its name in the list box and click the Run button, or double-click its name in the list box.

      21. To run your My_Macro macro using your keyboard, press the Ctrl+Shift+W shortcut keys you assigned in Step 2.

      REFERENCE Please select the video for Lesson 2 online at www.wrox.com/go/excelvba24hour. You will also be able to download the code and resources for this lesson from the website.