Diving into Object Properties and Methods

Although knowing how to refer to objects is important, you can't do anything useful by simply referring to an object (as in the examples in the preceding sections). To accomplish anything meaningful, you must do one of two things:

1 Read or modify an object's properties 1 Specify a method of action to be used with an object

Another slant on McObjects, McProperties, and McMethods

Here's an analogy that may help you understand the relationships between objects, properties, and methods in VBA. In this analogy, I compare Excel with a fast-food restaurant chain.

The basic unit of Excel is a Workbook object. In a fast-food chain, the basic unit is an individual restaurant. With Excel, you can add workbooks and close workbooks, and all the open workbooks are known as Workbooks (a collection of Workbook objects). Similarly, the management of a fast-food chain can add restaurants and close restaurants, and all the restaurants in the chain can be viewed as the Restaurants collection (a collection of Restaurant objects).

An Excel workbook is an object, but it also contains other objects such as worksheets, charts, VBA modules, and so on. Furthermore, each object in a workbook can contain its own objects. For example, a Worksheet object can contain Range objects, PivotTable objects, Shape objects, and so on.

Continuing with the analogy, a fast-food restaurant (like a workbook) contains objects such as the Kitchen, DiningArea, and Tables (a collection). Furthermore, management can add or remove objects from the Restaurant object. For example, management may add more tables to the Tables collection. Each of these objects can contain other objects. For example, the Kitchen object has a Stove object, VentilationFan object, Chef object, Sink object, and so on.

So far, so good. This analogy seems to work. Let me see if I can take it further.

Excel's objects have properties. For example, a Range object has properties such as Value and Name, and a Shape object has properties such as Width, Height, and so on. Not surprisingly, objects in a fast-food restaurant also have properties. The Stove object, for example, has properties such as Temperature and Number-ofBurners. The VentilationFan has its own set of properties (TurnedOn, RPM, and so forth).

Besides properties, Excel's objects also have methods, which perform an operation on an object. For example, the ClearContents method erases the contents of a Range object. An object in a fast-food restaurant also has methods. You can easily envision a ChangeThermostat method for a Stove object, or a SwitchOn method for a VentilationFan object.

In Excel, methods sometimes change an object's properties. The ClearContents method for a Range changes the Range's Value property. Similarly, the ChangeThermostat method on a Stove object affects its Temperature property. With VBA, you can write procedures to manipulate Excel's objects. In a fast-food restaurant, the management can give orders to manipulate the objects in the restaurants. ("Turn the stove on and switch the ventilation fan to high.")

The next time you visit your favorite fast-food joint, just say, "I'll have a Burger object with the Onion property set to False."

With literally thousands of properties and methods available, you can easily be overwhelmed. I've been working with this stuff for years and I'm still overwhelmed. But as I've said before and I say again: You'll never need to use most of the available properties and methods.

0 0

Post a comment