An Overview of the Excel Object Model
Visual Basic for Applications is an object-oriented programming language. The basic concept of object-oriented programming is that a software application (Excel in this case) consists of various individual objects, each of which has its own set of features and uses.
An Excel application contains cells, worksheets, charts, pivot tables, drawing shapes — the list of Excel’s objects is seemingly endless. Each object has its own set of features, which are called properties, and its own set of uses, called methods.
You can think of this concept just as you would the objects you encounter every day, such as your computer, car, or refrigerator. Each of these objects has identifying qualities, such as height, weight, and color. They each have their own distinct uses, such as working with Excel, transporting you over long distances, or keeping perishable foods cold.
VBA objects also have identifiable properties and methods of use. A worksheet cell is an object, and among its describable features (its properties) are its address, height, and formatted fill color. A workbook is also a VBA object, and among its usable features (its methods) are its capabilities to be opened, closed, and have a chart or pivot table added to it.
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 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:
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:
Indeed, if you have your cursor already in cell A1, you can simply use the ActiveCell object, negating the need to spell out the range:
Many of Excel’s objects belong to collections, which are essentially groups of like objects. Similarly, your house sits within a neighborhood, which is a collection of houses. 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 the following two lines of code in a workbook that has only one worksheet called MySheet, they both do the same thing:
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:
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:
Properties are essentially the characteristics of an object. Your house has a color, a square footage, an age, and so on. Some properties, such as the color of your house, can be changed. Other properties, such as the year your house was built, can’t be changed.
Likewise, an object in Excel such as 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 to the property. For instance, you can change the name of your worksheet by changing its Name property.
In this example, you rename Sheet1 to MySheet:
Sheets("Sheet1").Name = "MySheet"
Some properties are read-only, which means that you can’t assign a value to them directly. An example of a read-only property is the Text property of cell, which provides the formatted appearance of a value in a cell. You cannot overwrite or change it.
Methods are the actions that can be performed against an object. It helps to think of methods as verbs. For example, you can paint your house; in VBA, that might translate to
A simple example of an Excel method is the Select method of the Range object:
Another is the Copy method of the Range object:
Some methods have parameters that can dictate how the methods are applied. For instance, the Paste method can be used more effectively by explicitly defining the Destination parameter: