Excel VBA 24-Hour Trainer. Tom Urtis
Чтение книги онлайн.
Читать онлайн книгу Excel VBA 24-Hour Trainer - Tom Urtis страница 5
Название: Excel VBA 24-Hour Trainer
Автор: Tom Urtis
Издательство: John Wiley & Sons Limited
Жанр: Зарубежная образовательная литература
isbn: 9781119288299
isbn:
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.
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.
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.
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.
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.
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.
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 СКАЧАТЬ