Introducing the Excel Object Model

In This Chapter

Introducing the concept of objects
Finding out about the Excel object hierarchy
Understanding object collections
Referring to specific objects in your VBA code
Accessing or changing an object’s properties
Performing actions with an object’s methods
Everyone is familiar with the word object. Well, folks, forget the definition you think you know. In the world of programming, the word object has a different meaning. You often see it used as part of the expression object-oriented programming, or OOP for short. OOP is based on the idea that software consists of distinct objects that have attributes (or properties) and can be manipulated. These objects are not material things. Rather, they exist in the form of bits and bytes.
In this chapter, I introduce you to the Excel object model, which is a hierarchy of objects contained in Excel. By the time you finish this chapter, you’ll have a reasonably good understanding of what OOP is all about — and why you need to understand this concept to become a VBA programmer. After all, Excel programming really boils down to manipulating Excel objects. It’s as simple as that.
The material in this chapter may be a bit overwhelming. But please take my advice and plow through it, even if you don’t fully grasp it. The important concepts presented here will make lots more sense as you progress through the topic.

Excel Is an Object?

You’ve used Excel for quite a while, but you probably never thought of it as an object. The more you work with VBA, the more you view Excel in those terms. You’ll understand that Excel is an object and that it contains other objects. Those objects, in turn, contain still more objects. In other words, VBA programming involves working with an object hierarchy.
At the top of this hierarchy is the Application object — in this case, Excel itself (the mother of all objects).


Climbing the Object Hierarchy

The Application object contains other objects. Following is a list of some of the more useful objects contained in the Excel Application:

Addin

CommandBar Window Workbook Worksheet Function
Each object contained in the Application object can contain other objects. For example, the following is a list of objects that can be contained in a Workbook object:

Chart

Name ‘ VBProject Window Worksheet
In turn, each of these objects can contain still other objects. Consider a Worksheet object (which is contained in a Workbook object, which is contained in the Application object). Some of the objects that can be contained in a Worksheet object follow:
Comment Hyperlink
Name
Outline
PageSetup
PivotTable
Range
Put another way, if you want to do something with a range on a particular worksheet, you may find it helpful to visualize that range in the following manner:
Range contained in Worksheet contained in Workbook contained in Excel
Is this beginning to make sense?
Before you come down with a bad case of object overload, it’s important to understand that you never need to use most of the objects available to you. In fact, most VBA work involves only a few objects. Even better, you can almost always find the relevant object by recording a macro while doing something with that object.

Wrapping Your Mind around Collections

Collections are another key concept in VBA programming. A collection is a group of objects of the same type. And to add to the confusion, a collection is itself an object.
Here are a few examples of commonly used collections:
Workbooks: A collection of all currently open Workbook objects
Worksheets: A collection of all Worksheet objects contained in a particular Workbook object
Charts: A collection of all Chart objects (chart sheets) contained in a particular Workbook object
Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object
You may notice that collection names are all plural, which makes sense (at least I hope).

Visualizing objects

Excel’s Help system displays the complete Excel object model graphically. Locating this diagram varies, depending on the version of Excel you have. For Excel 2003, follow these steps:
1. Activate the VBE.
2. Type Object Model in the Help box (located to the right of the menu bar).
The Search Results appear in the task pane.
3. Click Microsoft Excel Object model. You see the diagram shown here.
tmp69-15_thumb

Referring to Objects

I presented the information in the previous sections to prepare you for the next concept: referring to objects in your VBA code. Referring to an object is important because you must identify the object that you want to work with. After all, VBA can’t read your mind — that feature is slated for Excel 2012.
You can work with an entire collection of objects in one fell swoop. More often, however, you need to work with a specific object in a collection (such as a particular worksheet in a workbook). To reference a single object from a collection, you put the object’s name or index number in parentheses after the name of the collection, like this:
tmp69-16_thumb
Notice that the sheet’s name is in quotation marks. If you omit the quotation marks, Excel won’t be able to identify the object.
If Sheet1 is the first (or only) worksheet in the collection, you can also use the following reference:
tmp69-17_thumb
In this case, the number is not in quotation marks. Bottom line? If you refer to an object by using its name, use quotation marks. If you refer to an object by using its index number, use a plain number without quotation marks.
Another collection, called Sheets, contains all the sheets (worksheets and Chart sheets) in a workbook. If Sheet1 is the first sheet in the workbook, you can reference it as
tmp69-18_thumb
Navigating through the hierarchy
If you want to work with the Application object, it’s easy: you start by typing Application. Every other object in Excel’s object model is under the Application object. You get to these objects by moving down the hierarchy and connecting each object on your way with the dot (.) operator. To get to the Workbook object named “Topic.xls”, start with the Application object and navigate down to the Workbooks collection object.
tmp69-19_thumb
To navigate further to a specific worksheet, add a dot operator and access the Worksheets collection object.
tmp69-20_thumb
Not far enough yet? If you really wanted to get the value from cell A1 on the first Worksheet of the Workbook named “Topic1.xls”, you need to navigate one more level to the Range object.
tmp69-21_thumb
When you refer to a Range object in this way, it’s called a fully qualified reference. You’ve told Excel exactly which range you wanted, on which worksheet and in which workbook, and have left nothing to the imagination. And that’s a good thing. Imagination is good in people, but not so good in computer programs.

Simplifying object references

If you had to fully qualify every object reference you make, your code would get quite long, and may be more difficult to read. Fortunately, Excel provides you with some shortcuts that can improve the readability (and save you some typing). For starters, 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
tmp69-22_thumb
That’s a pretty good improvement. But wait, there’s more. If Topic1.xls is the active workbook, you can omit that reference too. Now we’re down to
tmp69-23_thumb
Now we’re getting somewhere. Have you guessed the next shortcut? That’s right, if the first worksheet is the currently active worksheet, then Excel will assume that reference and allow us to just type
tmp69-24_thumb
Contrary to what some people may think, Excel does not have a Cell object. A cell is simply a Range object that consists of just one element.
The shortcuts described here are great, but they can also be dangerous. What if you only think topic1.xls is the active workbook? You could get an error, or worse, get the wrong value and not even realize it’s wrong. For that reason, it’s best to fully qualify your object references.
In Chapter 14, I discuss 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 ‘ 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-of Burners. The VentilationFan has its own set of properties (Turned On, RPM, and so forth).
Besides properties, Excel’s objects also have methods, which perform an operation on an object. For example, the Clear Contents method erases the contents of a Range object. An object in a fast-food restaurant also has methods. You can easily envision a Change Thermostat method for a Stove object, or a Switch On method for a VentilationFan object.
In Excel, methods sometimes change an object’s properties. The Clear Contents method for a Range changes the Range’s Value property. Similarly, the Change Thermostat 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.

Object properties

Every object has properties. You can think of properties as attributes that describe the object. An object’s properties determine how it looks, how it behaves, and even whether it is 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 may 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. See Figure 4-1.
tmp69-25_thumb
This message box displays a Range object's Value property.
Figure 4-1:
This message box displays a Range object’s Value property.
MsgBox is a useful function; you often use it to display results while Excel executes your VBA code. I tell you more about this function in Chapter 15. The code in the preceding example displays the current setting of a cell’s Value property. What if you want to change the setting for that property? The following macro changes the value displayed in cell A1 by changing the cell’s Value property:
tmp69-27_thumb
After Excel executes this procedure, cell A1 on Sheet1 of the active workbook contains the value 934. (By the way, if the active workbook does not have a sheet named Sheet1, executing that macro will display an error message.)
Each object has its own set of properties, although some properties are common to many objects. For example, many (but not all) objects have a Visible property. Most objects also have a Name property.
Some object properties are read-only, which means that you can see the property’s value, but you can’t change it.
As I mention earlier in this chapter, a collection is also an object. This means that a collection also has properties. For example, you can determine how many workbooks are open by accessing the Worksheets collection’s Count property. The following VBA procedure displays a message box that tells you how many workbooks are open:
tmp69-28_thumb

Object methods

In addition to properties, objects have methods. A method is an action you perform with an object. A method can change an object’s properties or make the object do something.
This simple example uses the Calculate method on a Range object to calculate the formula in cell A1 on Sheet1:
tmp69-29_thumb
Most methods also take one or more arguments. An argument is a value that further specifies the action to perform. You place the arguments for a method after the method, separated by a space. Multiple arguments are separated by a comma.
The following example activates Sheet1 (in the active workbook) and then copies the contents of cell A1 to cell B1 using the Range object’s Copy method. In this example, the Copy method has one argument — the destination range for the copy operation:
tmp69-30_thumb
Notice that I omit the worksheet reference when I refer to the Range objects. I could do this safely because I used a statement to activate Sheet1 (using the Activate method).
Because a collection is also an object, collections have methods. The following macro uses the Add method for the Workbooks collection:
tmp69-31_thumb
As you may expect, this statement creates a new workbook. In other words, it adds a new workbook to the Workbooks collection.

Object events

In this section, I briefly touch on one more topic that you need to know about: events. Objects respond to various events that occur. For example, when you’re working in Excel and you activate a different workbook, an Activate event occurs. You could, for example, have a VBA macro that is designed to execute whenever an Activate event occurs.
Excel supports many events, but not all objects can respond to all events. And some objects don’t respond to any events. The concept of an event becomes clear in Chapter 11 and also in Part IV.

Finding Out More

You find out more about objects, properties, and methods in the chapters that follow this one. You may also be interested in three other excellent tools:
VBA’s Help system The Object Browser ‘ Auto List Members

Using VBA’s Help system

The VBA Help system describes every object, property, and method available to you. This is an excellent resource for finding out about VBA and is more comprehensive than any topic on the market.
If you’re working in a VBA module and want information about a particular object, method, or property, move the cursor to the word you’re interested in and press F1. In a few seconds you see the appropriate help topic, complete with cross-references and perhaps even an example or two.
Figure 4-2 shows a screen from the online Help system — in this case, for a Worksheet object.
Click Properties to get a complete list of this object’s properties.
Click Methods to get a listing of its methods.
Click Events to get a listing of the events it responds to.
An example from VBA's Help system.
Figure 4-2:
An example from VBA’s Help system.

Using the Object Browser

The VBE includes another tool, known as the Object Browser. As the name implies, this tool lets you browse through the objects available to you. To access the Object Browser, press F2 when the VBE is active (or choose View Object Browser). You see a window like the one shown in Figure 4-3.
Browsing for objects with the Object Browser.
Figure 4-3:
Browsing for objects with the Object Browser.
The drop-down list at the top contains a list of all currently available object libraries. Figure 4-3 shows All Libraries. If you want to browse through Excel’s objects, select Excel from the drop-down list.
The second drop-down list is where you enter a search string. For example, if you want to look at all Excel objects that deal with links, type link into the second field and click the Search button. (It has a pair of binoculars on it.) The Search Results window displays everything in the object library that contains the text link. If you see something that looks like it may be of interest, press F1 for more information.

Next post:

Previous post: