Learning more about objects and properties

If this is your first exposure to VBA, you're probably a bit overwhelmed by objects, properties, and methods. I don't blame you. If you try to access a property that an object doesn't have, you get a runtime error, and your VBA code grinds to a screeching halt until you correct the problem.

Fortunately, there are several good ways to learn about objects, properties, and methods. READ THE REST OF THE BOOK

Don't forget, the name of this chapter is "Introducing Visual Basic for Applications." The remainder of this book covers many additional details and provides many useful and informative examples.


The absolute best way to become familiar with VBA, without question, is to simply turn on the macro recorder and record some actions that you perform in Excel. This is a quick way to learn the relevant objects, properties, and methods for a task. It's even better if the VBA module in which the code is being recorded is visible while you're recording.


The main source of detailed information about Excel's objects, methods, and procedures is the Help system. Many people forget about this resource.


The Object Browser is a handy tool that lists every property and method for every object available. When the VBE is active, you can bring up the Object Browser in any of the following three ways:

■ Choose the View Object Browser command from the menu.

■ Click the Object Browser tool on the Standard toolbar. The Object Browser is shown in Figure 7-14.

h 1*4 VUn '• ~ iMdl OrWf ¡U fMk U L-. IfUM HUf ■ T* ^

I" ■, - *1 * t h 1*4 VUn '• ~ iMdl OrWf ¡U fMk U L-. IfUM HUf ■ T* ^

Figure 7-14: The Object Browser is a great reference source.

The drop-down list in the upper-left corner of the Object Browser includes a list of all object libraries that you have access to:

■ MSForms (used to create custom dialog boxes)

■ Office (objects common to all Microsoft Office applications)

■ Stdole (OLE automation objects)

■ The current project (the project that's selected in the Project Explorer) and any workbooks referenced by that project

Your selection in this upper-left drop-down list determines what is displayed in the Classes window, and your selection in the Classes window determines what is visible in the Members Of window.

After you select a library, you can search for a particular text string to get a list of properties and methods that contain the text. You do so by entering the text in the second drop-down list and then clicking the binoculars (Search) icon. For example, assume that you're working on a project that manipulates cell comments:

1. Select the library of interest. If you're not sure which object library is appropriate, you can select <All Libraries>.

2. Enter Comment in the drop-down list below the library list.

3. Click the binoculars icon to begin the text search.

The Search Results window displays the matching text. Select an object to display its classes in the Classes window. Select a class to display its members (properties, methods, and constants). Pay attention to the bottom pane, which shows more information about the object. You can press F1 to go directly to the appropriate help topic.

The Object Browser might seem complex at first, but its usefulness to you will increase over time. EXPERIMENT WITH THE IMMEDIATE WINDOW

As I describe in the sidebar earlier in this chapter (see "About the Code Examples"), the Immediate window of the VBE is very useful for testing statements and trying out various VBA expressions. I generally keep the Immediate window visible at all times, and I use it frequently to test various expressions and to help in debugging code.


0 0

Post a comment