Excel VBA 24-Hour Trainer. Tom Urtis

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

      The Code Window

      The Code window is where the code for macros and VBA procedures are located. The VBE provides separate code windows for each module. A good way to think of this is, for every object (worksheet, module, and so on) you see listed in the Project Explorer, the VBE has provided a Code window. Note that the drop-down in the upper right-hand corner of Figure 3.3 displays the name of the macro that is currently showing in the Code window (mySort). As you create multiple macros, you can use this drop-down to quickly move from one macro to another.

      The Properties Window

      The Properties window is located in the left vertical pane near the bottom of the VBE. If you do not see the Properties window in your VBE, press F4, or from the VBE menu bar click ViewProperties Window. This window displays a list of the properties and their assigned values of whatever object is selected in the Project Explorer window. For example, in Figure 3.3, Sheet1 has been selected and the Properties window shows you, among other details, that the Name property for the selected object is Sheet1.

      The Immediate Window

      The Immediate window is located at the bottom of the VBE, usually below the Code window, as shown in Figure 3.3. If you do not see the Immediate window in your VBE, press Ctrl+G, or from the VBE menu bar click ViewImmediate Window. The name “Immediate” has nothing to do with urgency, but rather with the notion that you can query a line of code and immediately obtain its returned result, without having to run a macro to see what that code line does. This comes in handy for code debugging tactics in Lesson 20, but for now I just wanted to point out the Immediate window to familiarize you with its name and location.

      Understanding Modules

      I touched on modules earlier, but they are worth another mention. A module is a container for your code. A single module may hold one or many macros, depending on the workbook and your preference for how you manage your code. For smaller projects with maybe two or three macros, just one module is sufficient. If you develop larger projects with dozens of macros, it's a good idea to organize them among several modules by theme or purpose.

      Several types of modules exist:

      • Standard modules: These are the kind you have seen already, which hold macros you create from scratch on your own or from the Macro Recorder.

      • UserForm modules: These belong to a custom user interface object called a UserForm, which is covered in Lessons 21, 22, and 23.

      • Class modules: These contain the kind of VBA code that enables you to create your own objects programmatically. Creating your own classes is very cool, and you learn about that in Lesson 24.

      • Worksheet modules: These hold VBA code that looks and acts like macros, but to make things interesting Microsoft refers to that code as a procedure instead of as a macro. Worksheet-level procedures are tied to various actions called 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.

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.

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 СКАЧАТЬ