Introduction to the Excel Object Model

Regardless of the approach you choose to integrate your code with Excel, you will eventually need to talk to the Excel object model to get things done. It is impossible to describe the Excel object model completely in this book, but we try to make you familiar with the most important objects in the Excel object model and show some of the most frequently used methods, properties, and events of these objects.

The first step in learning the Excel object model is getting an idea of the basic structure of the object model hierarchy. Figure 3.16 shows the most critical objects in the Excel object model and their hierarchical relationship.

Figure 3.16. The basic hierarchy of the Excel object model.

A Workbook object has a collection called Sheets. The Sheets collection can contain objects of type Worksheet or Chart. A Chart is sometimes called a chart sheet because it covers the entire area that a worksheet would cover. You can insert a chart sheet into a workbook by right-clicking the worksheet tabs in the bottom-left corner of the Excel workbook and choosing Insert. Figure 3.17 shows the dialog box that appears. Note that two additional objects are in the Sheets collection: MS Excel 4.0 macro sheets and MS Excel 5.0 dialog sheets. If you insert a macro sheet or dialog sheet into an Excel workbook, it is treated as a special kind of worksheet; no special object model type corresponds to a macro sheet or a dialog sheet.

Figure 3.17. Inserting various kinds of sheets into an Excel Workbook.

Because a workbook can contain these various kinds of objects, Excel provides several collections off the Workbook object. The Worksheets collection contains just the Worksheet objects in the workbook. The Charts collection contains just the chart sheets in the workbook. The Sheets collection is a mixed collection of both. The Sheets collection returns members of the collection as type Object; you must cast the returned object to a Worksheet or Chart. In this book, when we talk about an object that could be either a Worksheet or a Chart, we refer to it as a sheet.

Figure 3.18 shows a more complete hierarchy tree with the major objects associated with the objects in Figure 3.16. This starts to give you an idea of the extensive hierarchy of objects that is the Excel object model, especially when you realize that this diagram shows fewer than half of the objects available. The objects shown in gray are coming from the Microsoft.Office.Core namespace, which is associated with the Microsoft Office 11.0 PIA (office.dll). These objects are shared by all the Office applications.

Figure 3.18. A more detailed hierarchy of some major objects in the Excel object model.

Figure 3.19 shows the object hierarchy associated with Range, a very important object in Excel that represents a range of cells you want to work with in your code. We used the Range object in Listing 3.2

Figure 3.19. A more detailed hierarchy of objects associated with Range in the Excel object model.

Figure 3.20 shows the object hierarchy associated with Shape. A Shape represents things that float on the worksheet that are not cells, such as embedded buttons, drawings, and comment bubbles.

Figure 3.20. A more detailed hierarchy of objects associated with Shape in the Excel object model.