Excel VBA 24-Hour Trainer - Tom Urtis

Скачать книгу

events, such as selecting a range or entering a value in a cell.

      • Workbook module: Not to be outdone, the workbook itself has its own module, named by default as ThisWorkbook, where code is maintained for handling workbook-level events.

      The point is, several types of modules exist, but the concept is the same – modules hold code for the object(s) they serve.

      Using the Object Browser

      One useful tool the VBE offers is the Object Browser. This section gives some background on the Object Browser and how you can use it to familiarize yourself with locating objects and their associated properties and methods.

      The ability to program Excel is based on tapping into any of several libraries of objects in the Microsoft Office objects model. For example, there is an Office library, a VBA library, and of course, an Excel library. Some libraries have hundreds of objects, and each object has many properties, methods, and, in some cases, associated events. The interwoven collection of object libraries and their keyword kin is enormous. Fortunately, there is the Object Browser to guide your search for information about objects and their properties for whatever library you are interested in.

To see the Object Browser in the VBE, press the F2 key or click ViewObject Browser. Figure 3.4 shows the Object Browser – it covers the area normally occupied by the Code window.

image

Figure 3.4

      To get a feel for the Object Browser, click the drop-down arrow next to <All Libraries> and select Excel. When you do that, in the Classes pane you see the classes belonging to Excel. Click the Application class and you see the larger Members pane display the properties and methods relating to the Application object. Click the ActiveWorkbook member and look at the bottom of the Object Browser. You see that ActiveWorkbook is a property that itself is a Workbook object.

After you follow the preceding steps, the Object Browser looks like Figure 3.5; the arrows point to what you clicked. If you click the green Workbook link at the bottom, the Object Browser takes you to the Workbook class and displays the properties and methods for Workbook.

image

Figure 3.5

      With a class or member item selected, you can click the yellow question mark icon at the top of the Object Browser to be taken to the Help file for that selected item.

      The Object Browser has a Search feature in the drop-down field to the left of the binoculars icon. If you type a term you are interested in and click the binoculars icon, the associated members of that term will be displayed for the selected library.

      To exit the Object Browser, click the lower of the two X close buttons near the top-right corner of the VBE.

      Exiting the VBE

      To exit the VBE and return to the worksheets, you can either press Alt+Q, or click the topmost X close button at the top-right corner of the VBE.

      Try It

      Because this lesson is an introduction to the Visual Basic Editor environment, there are no programming techniques to try, but you can get a jump on your familiarity with the VBE by considering these items:

      • You have several ways to get into the VBE, but which way works best for you? As you've seen, Alt+F11 works on all Excel versions, but if you are more of a mouse user than a keyboard user, you have several options depending on what's easiest for you:

      • In version 2003 you can click ToolsMacroVisual Basic Editor, or you can keep the Visual Basic toolbar visible, and click the Visual Basic Editor icon. You can also right-click the workbook 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.

image

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,

Скачать книгу