Excel VBA 24-Hour Trainer. Tom Urtis
Чтение книги онлайн.
Читать онлайн книгу Excel VBA 24-Hour Trainer - Tom Urtis страница 6
Название: Excel VBA 24-Hour Trainer
Автор: Tom Urtis
Издательство: John Wiley & Sons Limited
Жанр: Зарубежная образовательная литература
isbn: 9781119288299
isbn:
In Figure 2.12, notice the small box to the right of Ctrl+ in the Shortcut Key section. You can place any letter of the alphabet in that field, which, when pressed with the Ctrl key, will be one method (and a convenient one at that) by which you can run the macro.
NOTE A shortcut key is not mandatory; in fact, most of your macros will not have one or need one. But if you do want to assign a shortcut key, get into the good habit of assigning it with the Ctrl+Shift combination rather than with just the Ctrl key. Excel has assigned all 26 letters of the alphabet to serve as built-in shortcuts with the Ctrl key for various tasks, and you will do well to avoid overriding that native functionality. For example, Ctrl+C is the key combination to copy text. However, if you assign the shortcut key Ctrl+C to your macro, you will override the default for that key combination, and will not be able to use Ctrl+C to copy text in the workbook containing the macro.
To take advantage of the Shortcut Key option, click in the Shortcut Key field, press the Shift key, and also press an alphabet key such as the letter S. You will have created the keyboard shortcut Ctrl+Shift+S, which will not interfere with any of Excel's significant built-in keyboard shortcuts.
Most macros you record are stored in the workbook you are working with. For now, you can keep the default selection of This Workbook in the Store Macro In field.
Finally, in the Description field, enter a brief but meaningful explanation of what the macro does. When you are finished making these minor changes to the Record Macro dialog box, it looks similar to Figure 2.13. Go ahead and click OK, which turns on the Macro Recorder, and you can proceed to manually perform the steps you want to automate.
In versions 2003 and earlier, you will see a tiny floating toolbar while the Macro Recorder is on. That is the Stop Recording toolbar, with a Stop Recording button you click when you are finished recording your actions. When you have completed the steps to your task, turn off the Macro Recorder in version 2003 by clicking the Stop Recording button, as shown in 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.
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 View
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.
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.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.
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. СКАЧАТЬ