Читать книгу Excel VBA 24-Hour Trainer - Tom Urtis - Страница 4

This lesson leads you through the process of composing a macro to sort and format a range of data. But even before the first line of programming code is written, you need to set up shop by giving yourself easy access to the VBA-related tools you'll be using. The following housekeeping items usually need to be done only once, and it's worth taking the time to do them now if you haven't already done so.

Accessing the VBA Environment

At the time of this writing, Excel is at a unique stage in its ongoing evolution because four of its versions are being used with significant popularity in the Microsoft Office suite of applications. Version 2003 (also known as version 11) was the final Excel version with the traditional menu bar interface of File, Edit, View, and so on. Then came version 2007 (also known as version 12), blazing the trail for Office's new Ribbon interface. Three years later, version 2010 (also known as version 14) was the next release from Redmond. Most recently, version 2013 (also known as version 15) has taken its place among the community of Excel versions that are being used around the world.

As with other tasks you typically do in Excel, the actions you take to create, view, edit, or run VBA code usually start by clicking the on-screen icon relating to that task. Exactly what those VBA-related icons look like, and what you need to do to make them easily accessible to you, depends on the particular version of Excel you are working with.

WHY IS THERE NO VERSION 13?

You probably noticed that the version numbers went from 12 in 2007 to 14 in 2010, making the number 13 conspicuously absent as a version number. This was not an accident; Microsoft purposely skipped the number 13. You'll often notice in elevators of high-rise office buildings and hotels that the floor buttons go from 12 to 14, without a floor number 13. Microsoft recognizes that its Office applications are used globally, and in some cultures, 13 is thought to be an unlucky number. It made good business sense to avoid issues of possible reluctance from consumers upgrading to “Office 13,” or blame for inevitable version bugs by people who believe that 13 is an unlucky number.

To save yourself time and extra mouse clicks, start by making sure that the VBA-related icons you'll be using most frequently are already displayed whenever you open Excel. The following steps are shown for each of today's four most popular versions.

Version 2003 continues to be used by a measurable percentage of individuals and employers worldwide. For versions of Excel up to and including 2003, from your worksheet menu, click ViewToolbarsVisual Basic, as shown in Figure 2.1. This displays the Visual Basic toolbar, as shown in Figure 2.2, which you can dock just as you do with your other toolbars.

Figure 2.1

Figure 2.2

For versions of Excel after 2003 (that is, starting with Excel 2007), the Ribbon user interface has replaced the menu interface, resulting in a different look to the VBA-related icons and a different set of steps required to see them.

In versions 2007, 2010, and 2013, these VBA icons are located on the Developer tab. By default, the Developer tab is not automatically displayed along with the other Ribbon tabs. You need to complete a set of one-time steps to show the Developer tab and to keep it visible whenever you open Excel. Although the steps to do this are easy, they are different for each version.

In Excel 2007, do the following:

1. Click the round Office button near the top-left corner of your screen.

2. Click the Excel Options button located at the bottom of that menu, as shown in Figure 2.3.

3. In the Excel Options dialog box, click the Popular item at the upper left, and select the Show Developer tab in the Ribbon option, as shown in Figure 2.4.

Figure 2.3

Figure 2.4

In Excel versions 2010 and 2013, showing the Developer tab is a bit different. A new Ribbon tab named File has supplanted the Office button. Use the following steps to make the Developer tab visible:

1. Click the File tab and then click the Options button, as shown in Figure 2.5. The Options dialog box opens.

2. Click the Customize Ribbon item at the left, which displays two vertical lists, as shown in Figure 2.6. Notice that the list on the right has a drop-down menu above it called Customize the Ribbon.

3. Select the Main Tabs item from the Customize the Ribbon drop-down.

4. In the list of Main Tabs, select Developer and click OK. You will see the Developer tab in your Ribbon, as shown in Figure 2.7.

Figure 2.5

Figure 2.6

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.

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.