Things to Know about Objects | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

The preceding sections introduced you to objects (including collections), properties, and methods . But I've barely scratched the surface.

Essential concepts to remember

In this section, I note some additional concepts that are essential for would-be VBA gurus. These concepts become clearer when you work with VBA and read subsequent chapters:

Objects have unique properties and methods.

Each object has its own set of properties and methods. Some objects, however, share some properties (for example, Name ) and some methods (such as Delete ).
You can manipulate objects without selecting them.

This might be contrary to how you normally think about manipulating objects in Excel. The fact is that it's usually more efficient to perform actions on objects without selecting them first. When you record a macro, Excel generally selects the object first. This is not necessary and may actually make your macro run more slowly.
It's important that you understand the concept of collections.

Most of the time, you refer to an object indirectly by referring to the collection that it's in. For example, to access a Workbook object named Myfile , reference the Workbooks collection as follows :
```
 Workbooks("Myfile.xlsx") 
```
This reference returns an object, which is the workbook with which you are concerned .
Properties can return a reference to another object.

For example, in the following statement, the Font property returns a Font object contained in a Range object. Bold is a property of the Font object, not the Range object.
```
 Range("A1").Font.Bold = True 
```
There can be many different ways to refer to the same object.

Assume that you have a workbook named Sales , and it's the only workbook open . Then assume that this workbook has one worksheet, named Summary . You can refer to the sheet in any of the following ways:
```
 Workbooks("Sales.xlsx").Worksheets("Summary") Workbooks(1).Worksheets(1) Workbooks(1).Sheets(1) Application.ActiveWorkbook.ActiveSheet ActiveWorkbook.ActiveSheet ActiveSheet 
```
The method that you use is usually determined by how much you know about the workspace. For example, if more than one workbook is open, the second and third methods are not reliable. If you want to work with the active sheet (whatever it may be), any of the last three methods would work. To be absolutely sure that you're referring to a specific sheet on a specific workbook, the first method is your best choice.

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.

RECORD YOUR ACTIONS

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.

USE THE HELP SYSTEM

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

USE THE OBJECT BROWSER

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:

Press F2.
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.

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:

Excel itself
MSForms (used to create custom dialog boxes)
Office (objects common to all Microsoft Office applications)
Stdole (OLE automation objects)
VBA
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:

Select the library of interest. If you're not sure which object library is appropriate, you can select <All Libraries>.
Enter Comment in the drop-down list below the library list.
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.