Excel VBA Programming For Dummies. Dick Kusleika
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 the object. An object’s properties determine how it looks, how it behaves, and even whether it’s visible. Using VBA, you can do two things with an object’s properties:
Examine the current setting for a property.
Change the property’s setting.
For example, a single-cell Range object has a property called Value. The Value property stores the value contained in the cell. You can write VBA code to display the Value property, or you can write VBA code to set the Value property to a specific value. The following macro uses the VBA built-in MsgBox function to bring up a box that displays the value in cell A1 on Sheet1 of the active workbook (see Figure 4-1):
Sub ShowValue() Contents = Worksheets("Sheet1").Range("A1").Value MsgBox ContentsEnd Sub
FIGURE 4-1: This message box displays a Range object’s Value property.
The code in the preceding example displays the current setting of a cell’s Value property.