Discovering the Excel Object Model
In Excel, you deal with workbooks, worksheets, and ranges on a daily basis. You likely think of each
of these “objects” as all part of Excel, not really separating them in your mind. However, Excel thinks
about these internally as all part of a hierarchical model called the Excel object model. The Excel object
model is a clearly defined set of objects that are structured according to the relationships between
them.
In this section, we give you a brief overview of the object model, as well as some of the other termi-
nology and concepts you will encounter in the upcoming 101 macros.
Understanding objects
In the real world, you can describe everything you see as an object. When you look at your house, it is
an object. Your house has rooms; those rooms are also separate objects. Those rooms may have closets.
Those closets are likewise objects. As you think about your
house, the rooms, and the closets, you may
see a hierarchical relationship between them. Excel works in the same way.
In Excel, the Application object is the all-encompassing object — similar to your house. Inside the
Application object, Excel has a workbook. Inside a workbook is a worksheet. Inside that is a range.
These are all objects that live in a hierarchical structure.
To point to a specific object in VBA, you can traverse the object model. For example, to get to cell A1
on Sheet 1, you can enter this code:
Application.Activeworbook.Sheets(“Sheet1”).Range(“A1”).Select
In most cases, the object model hierarchy is understood, so you don’t have to type every level.
Entering this code also gets you to cell A1 because Excel infers that you mean the active workbook,
and the active sheet:
Range(“A1”).Select
Indeed, if you have you cursor already in cell A1, you can simply use the ActiveCell object,
negating the need to actually spell out the range.
Activecell.Select
Understanding collections
Many of Excel’s objects belong to collections. Your house sits within a neighborhood, for example, which
is a collection of houses called a neighborhood. Each neighborhood sits in a collection of neighborhoods
called a city. Excel considers collections to be objects themselves.
In each Workbook object, you have a collection of Worksheets. The Worksheets collection is an
object that you can call upon through VBA. Each worksheet in your workbook lives in the
Worksheets collection.
If you want to refer to a worksheet in the Worksheets collection, you can refer to it by its position
in the collection, as an index number starting with 1, or by its name, as quoted text. If you run these
two lines of code in a workbook that has only one worksheet called MySheet, they both do the same
thing:
Worksheets(1).Select
Worksheets(“MySheet”).Select
If you have two worksheets in the active workbook that have the names MySheet and YourSheet, in
that order, you can refer to the second worksheet by typing either of these statements:
Worksheets(2).Select
Worksheets(“YourSheet”).Select
If you want to refer to a worksheet in a workbook called MySheet in a particular workbook that is not
active, you must qualify the worksheet reference and the workbook reference, as follows:
Workbooks(“MyData.xls”).Worksheets(“MySheet”).Select
Understanding properties
Properties are essentially the characteristics of an object. Your house has a color, a square footage, an
age, and so on. Some properties can be changed — like the color of your house. Other properties
can’t be changed — like the age of your house.
Likewise, an object in Excel like the Worksheet object has a sheet name property that can be
changed, and a Rows.Count row property that cannot.
You refer to the property of an object by referring to the object, and then the property. For instance,
you can change the name of your worksheet by changing its Name property.
In this example, you are renaming Sheet1 to MySheet:
Sheets(“Sheet1”).Name = “MySheet”
Some properties are read-only, which means that you can’t assign a value to them directly — for
instance, the Text property of cell. The Text property gives you the formatted appearance of value
in a cell, but you cannot overwrite or change it.
Understanding methods
Methods are the actions that can be performed against an object. It helps to think of methods as
verbs. You can paint your house, so in VBA, that translates to something like
house.paint
A simple example of an Excel method is the Select method of the Range object:
Range(“A1”).Select
Another is the Copy method of the Range object:
Range(“A1”).Copy
Some methods have parameters that can dictate how it is applied. For instance, the Paste method
can be used more effectively by explicitly defining the Destination parameter.
ActiveSheet.Paste Destination:=Range(“B1”)
