Excel VBA 24-Hour Trainer. Tom Urtis

Чтение книги онлайн.

СКАЧАТЬ icon near the upper-left corner of the Excel window (just to the left of the File menu item), and select View Code, which takes you to that workbook's module in the VBE.

      • In versions 2007, 2010, and 2013, you can click the Visual Basic Editor icon on the Developer tab.

      • In any version of Excel, you can right-click a worksheet tab and select View Code, which takes you to that worksheet's module in the VBE.

      • Take another look at the Object Browser and click around its classes and members. The VBA object model is a vast library of information that no one would attempt to memorize, but the idea here is to get a feel for the interwoven relationships among objects' classes, properties, and methods.

      • In the Project Explorer window, if you double-click an object such as a worksheet, workbook, or module name, you go directly to that object's Code window. But also notice the pop-up menu when you right-click an object's name in the Project Explorer. Go ahead and click any of those menu items to get the gist of where they lead you and what purpose they serve.

      • Get a bit of practice in with the Immediate window. If you were to enter some value into cell A1, and then format cell A1 in bold font, you can enter these expressions in the Immediate window and press Enter for each one:

      • ? Range(“A1”).Value returns whatever value you entered into A1.

      • ? Range(“A1”).Font.Bold returns True if you bolded A1, or False if you did not.

      • ? Range(“A1”).ClearContents returns True and clears the contents of cell A1.

      REFERENCE There is no video or code download to accompany this lesson.

Lesson 4

      Working in the VBE

      In Lesson 3, you took a bird's eye view of the Visual Basic Editor, and you became familiar with the names and locations of its most frequently used windows. In this lesson, you navigate through those VBE windows for the purpose of demonstrating how to handle the kinds of maintenance tasks you will often encounter in the VBE.

      Toolbars in the VBE

      The first thing you may have noticed about the VBE interface is that there is no Ribbon. The traditional VBE menu bar is pretty much the same interface for all versions of Excel since 1997.

Because you will be spending more time in the VBE, you'll want convenient access to the toolbar icons relating to the work you'll be doing. If you have not already done so, press Alt+F11 to get into the VBE, and show the Edit and Standard toolbars whose icons will soon come in handy. From the menu bar at the top of the VBE, click ViewToolbarsEdit and again ViewToolbarsStandard, as depicted in Figure 4.1.

Figure 4.1

      Macros and Modules

      In Lesson 2, you used the Macro Recorder to create a macro named mySort. You learned how to assign a shortcut key to the macro, and how to enter a brief description of what the macro does. You also learned about a couple of ways to run the macro, by using either the shortcut key or the Macro dialog box. One thing you have not been shown yet is the macro itself, or even how to find it.

      Locating Your Macros

      When the Macro Recorder created the mySort macro in Lesson 2, it also created a module in which to store the macro. If this module happens to be the first module of the workbook, as was the case for mySort, the Macro Recorder names the new module Module1 by default. If the Macro Recorder creates another module after that and the workbook still holds a module named Module1, the Macro Recorder assigns the default name of Module2, and so on.

In the Project Explorer window, expand the bolded VBAProject title (my Project workbook name is MacroExamples.xlsm) and expand the yellow Modules folder to show the module named Module1. To see the VBA code in that module, you can double-click the module name, or you can right-click the module name and choose View Code, as shown in Figure 4.2.

Figure 4.2

The mySort macro appears in the Code window for Module1. Based on the steps you took while recording the mySort macro in Lesson 2, Figure 4.3 shows the exact code that was produced by the Macro Recorder in Excel version 2003.

      NOTE If you record (or manually compose, as you see in later lessons) a macro in a version of Excel after 2003, and you run that macro in a 2003 version, you might experience an error in that code's execution, depending on what the code is trying to do. VBA code plays well together among versions after 2003, but those later versions of Excel contain newer features, such as Sparklines and an updated object model for charts and pivot tables, that a 2003 version would not recognize. VBA code produced by the Macro Recorder in version 2003 usually works just fine in later versions, but be aware that backward compatibility has its limitations when running code in a 2003 version that was produced in a later version.

Figure 4.3

      Understanding the Code

All macros start with a Sub statement (Sub is short for Subroutine, commonly referred to as a macro) that includes the name of the macro, followed by a pair of parentheses. For the example macro you see in Figures 4.3 and 4.4, the Sub statement is simply Sub mySort().

Figure 4.4

      Because this macro was recorded, there is a series of comment lines below the Sub statement that the Macro Recorder wants you to know about. For example, you see the macro name, the description of the macro you entered into the Record Macro dialog box, and the notation that the shortcut Ctrl+Shift+S has been assigned to this macro.

      Comment lines start with an apostrophe, are green in color to help you identify them, and are not executed as VBA code, as opposed to the other lines of VBA code that actually do something when the macro is running.

      NOTE The comments you see in a recorded macro directly reflect the information entered in the Record Macro dialog box. For example, if you assign a shortcut key, or you enter text in the Description field of the Record Macro dialog box as shown in Lesson 2, Figure 2.13, that information will be seen as comments in your recorded macro's code, as shown in Figure 4.3.

      The remaining lines in the macro are VBA statements, and they represent every action that was taken while the Macro Recorder was on:

      1. The first thing you did was select column A.

      2. Next, you inserted a new column at column СКАЧАТЬ