6. The Object Model

When you work in Excel what you are actually doing is manipulating elements of workbooks; macros work the same way. To edit or compose macros, you need to know how this manipulation of workbooks is accomplished. While the explanation can seem a bit difficult to grasp conceptually, focusing on examples should make things much clearer.

6.1 Objects and Collections

To give a simplified explanation, in Excel the top-most object is the program itself, called the Application. The Application contains the open Workbooks. Each Workbook contains one or more Worksheets. A Workbook may also contain other objects, such as Charts (e.g., bar or pie charts). We can call the containing object the "parent," and the contained object the "child." For example, Application is the parent of its Workbooks.

More formally, the child objects contained in a parent object are called a collection of that kind of object. For example, a Workbook has a collection of Sheets, which contains each Worksheet and each Chart (e.g., bar or pie chart) in the Workbook. A Workbook also has a collection of just its Worksheets, and a collection of just its Charts.

It would be logical to think that each worksheet has a collection of cells; and that each worksheet also has a collection of rows and a collection of columns, each of which has its own collection of cells. That is not really accurate, but we can think that way for now. Later we will refine this idea to get a more accurate picture when we discuss Ranges.

6.2 Dot Notation

Looking at that expression, we see that a parent object, Application, is followed by a child object, Workbooks("sample.xls"), which in turn is also the parent object of Worksheets("Sheets1"). The different levels of objects are designated by "dot notation"; i.e., a dot, or period, is used to separate the objects, starting with the highest-level object. When we use dot notation we say that the lower-level object is "qualified" by the reference to its parent object, as 'Application' qualifies 'Workbooks("sample.xls")' in our example.

We can refer to an object, an entire collection, or to one particular object in a collection:

To refer to an entire collection we use the name of the object in the plural. Application.Workbooks("sample.xls").Sheets refers to all the sheets (i.e., worksheets and charts) in the workbook named "sample.xls."

To refer to a particular object in a collection we use either a number (called the "index") or a name in quotation marks (if the particular object has a name). Application.Workbooks("sample.xls").Sheets("Sheet3") uses the worksheet name to refer to the worksheet named "Sheet3" in the workbook named "sample.xls" in Excel. If that is the third sheet in the workbook, we could alternatively refer to it as Application.Workbooks("sample.xls").Sheets(3), using the index.

6.3 Properties and Methods

Properties are characteristics of the object. For example, a Worksheet has a property called "Name," which refers to the name of the worksheet (which appears on the worksheet's tab).

Methods are actions that the object can do, or have done to it: Workbooks have a method named Close, which closes the workbook as if you had clicked file/close; another common method is Select, which selects the object, as you would expect.

The properties and methods of an object are referred to collectively as the members of the object. Object members are discussed in more detail in the next chapter.

6.4 Active Objects

After reading that, you might expect the macro you recorded (macro TUT1) to select row 2 to be Application.ActiveWorkbook.ActiveSheet.Rows(2).Select. Instead the code is Rows("2:2").Select. "Rows(2)" and 'Rows("2:2")' are equivalent, as explained later in the tutorial. But what happened to the qualifying objects of Rows(2)?

The answer is that they are not needed in our example. Some higher-level objects in the object model are "global." For our purposes, this means that they are known to belong to the Application object, so the qualification "Application" can be omitted from the dot notation. Thus, you can use ActiveWorkbook.Sheets(5), without qualifying it with "Application." The Workbooks collection is also global, so it can be used without qualification with "Application." The Sheets, Worksheets and Charts collections are considered to be those of the active workbook unless qualified by a reference to a different workbook. Rows, Columns, Cells and Range (covered later in the tutorial) are considered to be on the active worksheet, unless qualified by reference to a different worksheet.

Thus in our example, the macro knows that Rows("2:2").Select refers to the second row on the active worksheet, which is in the active workbook in the application. You will usually see recorded macro commands in this form.

6.5 Collections as Objects

A common property of collections is the Count property, which gives you (in programmer-speak, "returns") the number of objects in the collection: Worksheets.Count will return the number of worksheets in the active workbook.

A common method for collections is the Add method. When you use (in programmer-speak, 'call' or 'invoke') the Add method in the Sheets collection, you are adding a worksheet to the workbook: Sheets.Add. This is in fact the macro code generated when you record the steps to insert a worksheet. You might assume that Sheets.Insert would work, but if you try that you will get an error. The Sheets collection does not have an "insert" method. The moral is to always check an object's properties and methods, and not to rely on assumptions.

6.6 "With...End With"

With Sheets(2).Cells

.Font.Bold = True
.WrapText = True

End With

While using With...End With can be a timesaver it can sometimes make code harder to read, so there is a trade-off between efficiency and practicality. You will see With...End With often in recorded macro code, as in the TUT5 macro you recorded.

6.7 Learning the Object Model

The best way (and probably the only practical way) to begin learning the object model is through experience, by recording macros and examining the code in the VBA editor. Bear in mind that the code generated by recording a macro may not be the only code that will work, and can be more complex than code written from scratch to do the same thing. The object model is too big and complicated to remember all of it, so even very experienced macro programmers fall back on recording macros to obtain less commonly used objects/properties/methods.

Unfortunately, the VBA Help in the editor can be confusing and hard to navigate. In addition, you often have to know what to look for, and if you already knew that you probably would not need help in the first place. Nonetheless, the VBA Help is what you will have to fall back on from time to time, so it is best to become familiar with it. If you know which object you are looking for, but need information on the object's properties and methods, you can open the object browser (View/Object Browser). In the object browser you can select the object and go straight to the Help page for that object by pressing F1 or right-clicking and selecting Help, or you can select one of the properties/methods of the object and get help specifically on that.