Excel VBA 24-Hour Trainer. Tom Urtis

Figure 2.7

      Using the Macro Recorder

      The easiest way to create a macro is to record your worksheet actions using a valuable tool called the Macro Recorder. All you need to do is turn on the Macro Recorder, perform the actions that comprise the task you want to automate, and then turn off the Macro Recorder when you have finished your task. While the Macro Recorder is turned on, every action you do – selecting a cell, entering a number, formatting a range, pretty much everything – is recorded and represented as VBA code in a new macro. As you see later, when you run the macro created by the Macro Recorder, your task is completed automatically, just as if you had done it manually.

The Macro Recorder comes in handy for repetitive (and sometimes mundane) common tasks that you'd rather not have to keep manually doing over and over. For example, say you manage a table of data every day, such as the one shown in Figure 2.8, that shows how many items your company sold in its East, West, North, and South regions.

Figure 2.8

The everyday task at hand is to sort the table primarily by Region, then by Item, then by Count. Your boss wants the Item and Region columns to switch places, so that Region occupies column A and Item occupies column B. To improve readability, the numbers in the Count column must be formatted with the thousands comma separator, and the headers for Region, Item, and Count must be bolded. Figure 2.9 shows the finished table, the way your boss wants it.

Figure 2.9

      This is normally a six-step process, which is quite boring, but it's part of your job responsibilities.

      To complete the task you might do this:

      1. Insert a new column at column A.

      2. Select the Region column, cut it, and paste it to empty column A, to the left of the Item column.

      3. Delete the now-empty column from where the Region column was cut.

      4. Select range A1:C13 and sort in ascending order by Region, Item, and Count.

      5. Select range C2:C13 and format the numbers with the thousands comma separator.

      6. Select range A1:C1 and format those cells as Bold.

      Not only are these steps monotonous, but also a risk for making honest mistakes due to eventual human error. The good news is that if you perform the necessary steps perfectly for the Macro Recorder, the task can be reduced to a simple mouse click or keyboard shortcut, with VBA doing the grunt work for you.

      NOTE Anytime you create a macro, it's wise to plan ahead about why you are creating the macro, and what you want the macro to do. This is especially important with complex macros, because you want your macros to operate efficiently and accurately, with just the code that's necessary to get the job done properly. By avoiding excessive code, your macros will run faster and be easier to edit or troubleshoot. For example, get your workbook ready beforehand to avoid unnecessary coded actions. Have the worksheet that you'll be working on active, with the range of interest already visible. Mistakes are recorded too! Practice the steps first, so your macro's recorded code is not lengthier than it needs to be.

Because you know what manual steps are required for this daily task, you are ready to create your macro. The first thing to do is turn on the Macro Recorder. In Excel versions 2003 or before, click the Record Macro button on the Visual Basic toolbar, as shown in Figure 2.10. For later Excel versions, click the Record Macro button in the Code section of the Developer tab on the Ribbon, as shown in Figure 2.11.

Figure 2.10

Figure 2.11

What you see next looks much like Figure 2.12. A small Record Macro dialog box displays, with default information that only needs your approval by clicking OK to start recording your macro. Resist the temptation to accept the defaults, because now's the time to get into a few good habits.

Figure 2.12

      The Macro Recorder is an excellent teaching tool, and hardly a day goes by when I do not use it in some way. VBA is just too voluminous a programming language to memorize its every keyword and nuance. Often as not, I'll record a macro just to look at the code it produces to learn the proper syntax of a task dealing with some larger macro I am working on. You will find yourself using the Macro Recorder in the same way; it's a terrific source for learning VBA code, as Excel developers of any skill level will attest.

      For this example, the macro you are creating is one you will want to keep and use often. A little customization is strongly recommended to help you down the road, when you'll want to remember what the macro does, why you created it, and what optional keyboard shortcut you assigned to run it.

      In the Record Macro dialog box, give the macro a meaningful name. Macro names cannot contain spaces, and they cannot begin with a numeral. Because you are the person doing the sorting, and you don't want to make the macro name too long, naming it mySort gives the macro more meaning than the default name of Macro1.

      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.

Figure 2.13

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.