VBA Tips

While learning how to program, one can broadly divide the things you have to learn into two categories. First, you need to learn the semantics of a programming language, in this case “classic” Visual Basic (as opposed to VB.Net). Second, you need to learn the object models that you’ll use to achieve your programming objectives.

This week I’m going continue my coverage of the various Visual Basic Editor (VBE) windows by discussing the Object Browser. Next week I’ll explain the Locals windows. These windows are useful for investigating and learning various object models that you’ll use.

The Object Browser, shown in Figure 1, is useful for general object model investigation.

Figure 1: Viewing the Worksheet object
in the Object Browser. Click to enlarge

A simple exercise will take you through all of the useful capabilities of the Object Browser.

1. In Excel, open the VBE (Alt + F11) and display the Object Browser by selecting View/Object Browser (F2).

2. Change the Project/Library drop-down from to Excel.

3. In the Search Text drop-down, enter “worksheet” (without the quotation marks).

4. Click the Search button (the binoculars).

5. Select the first item in the Search Results list.

6. Notice that Worksheet is selected in the Classes list.

7. Find and select Visible in the Members of “Worksheet” list.

8. Notice the use of icons to signify the difference between libraries (the books icon next to Excel in the Search Results), classes (the icons used in the Classes list), properties (the hand and index card icon), and methods (the flying rectangle). You’ll also come across icons for events (lightning bolt) and enumerations.

9. Select the Unprotect method in the Members of “Worksheet” list.

10. Press F1. Notice that a help window appears displaying help for the Unprotect method.

11. Select the UsedRange property in the Members of “Worksheet” list. UsedRange is a property that is represented by a range object.

12. To see what you can do to a range object, click the green “Range” link located underneath the list of Classes.

To summarize, this exercise demonstrated the multiple things the Object Browser is capable of. In addition to a comprehensive listing of all programmable elements in a library, you can search for items, navigate to related help topics, and navigate to related classes and libraries.

Next week we’ll look at the Locals window. Then we’ll be ready to start coding!