align="center">
Considering generic collection objects
The Workbooks object contains Workbook objects and the Worksheets object contains Worksheet objects. It all seems very clean and simple. But there also collection objects that contain objects that are similar but not exactly the same. The example of this type of object that you’ll see most often is the Sheets collection object. The Sheets object contains both Worksheet objects and Chart objects. Sometimes you want to work with every sheet in a workbook regardless of its type. In that case, you would use the Sheets object to get at both types of sheets. The following example demonstrates how to access a worksheet named Sheet1 using either the Worksheets object or the Sheets object:
Application.Workbooks.Item(1).Worksheets.Item("Sheet1")Application.Workbooks.Item(1).Sheets.Item("Sheet1")
Simplifying object references
All the examples in this section so far have been fully qualified references. A fully qualified reference starts with the Application object and traverses the hierarchy all the way down to the object you want. They are unambiguous, but if you were required to fully qualify every object reference you make, your code would be quite long, more difficult to read, and your keyboard would wear out too fast. Fortunately, Excel provides some shortcuts that can improve the readability and save you some typing.
For starters, objects have default properties. If you want to use the default property, you don’t have to type and VBA will know what you mean. The star of the default property show is the Item property. Item is the default property for every collection object making the following code equivalent:
Application.Workbooks.Item("Book1.xlsx").Worksheets.Item(2).Range("A1").ValueApplication.Workbooks("Book1.xlsx").Worksheets(2).Range("A1").Value
While it’s important to understand the object hierarchy and how the Item property works, you almost never see Item used in code.
The next shortcut is that the Application object is always assumed. There are only a few cases when it makes sense to type it. Omitting the Application object reference shortens the example from the previous section to
Workbooks("Book1.xlsx").Worksheets(2).Range("A1").Value
That’s a pretty good improvement. But wait, there’s more. If you’re sure that Book1.xlsx is the active workbook, you can omit that reference, too. Now you’re down to
Worksheets(2).Range("A1").Value
Finally, you’re getting somewhere. Have you guessed the next shortcut? That’s right. If you know the second worksheet is the currently active worksheet, Excel assumes that reference and allows you to just type
Range("A1").Value
The shortcuts described here are great, but they can be dangerous. What if you only think Book1.xlsx is the active workbook? You could get an error, or worse, you could get the wrong value and not even realize it’s wrong. For that reason, it’s often best to qualify your object references enough to be safe. You almost never need the Application object in your reference, but you may need the Workbook object.
Chapter 14 discusses the With-End With structure, which helps you fully qualify your references but also helps to make the code more readable and cuts down on the typing. The best of both worlds!
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:
Read or modify an object’s properties.
Call a method of action to be used with an object.
Here’s an analogy comparing Excel to a fast-food chain that may help you understand the relationships among objects, properties, and methods in VBA.
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 a workbook and close a workbook, and all the open workbooks are known as Workbooks (a collection of Workbook objects). Similarly, the management of a fast-food chain can add a restaurant and close a restaurant, 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, chart sheets, 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.
Excel’s objects have properties. For example, a Range object has properties such as Value and Address, 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 NumberofBurners. The VentilationFan has its own set of properties (TurnedOn, RPM, and so on).
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, “Use the Grill method on a Burger object with the Onion property set to False.”
Setting object properties
Every object has properties. You can think of properties as characteristics that describe
