Excel Object Model Overview

Article
11/16/2012

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Application-level projects Microsoft Office version Excel 2003 Excel 2007 For more information, see Features Available by Application and Project Type.

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Project type

Document-level projects
Application-level projects

Microsoft Office version

Excel 2003
Excel 2007

For more information, see Features Available by Application and Project Type.

To develop solutions that use Microsoft Office Excel, you can interact with the objects provided by the Excel object model. This topic introduces the most important classes:

The object model closely follows the user interface. The Application object represents the entire application, and each Workbook object contains a collection of Worksheet objects. From there, the major abstraction that represents cells is the Range object, which enables you to work with individual cells or groups of cells.

Visual Studio Tools for Office extends many of these native objects into host items and host controls that can be used in document-level customizations. These controls have additional functionality, including data-binding capabilities and events. For example, a native Excel Range object is extended into a Microsoft.Office.Tools.Excel.NamedRange control, which can be bound to data and which exposes events. For more information about host items and host controls, see Extended Objects in Document-Level Projects.

This topic provides a brief overview of the Excel object model. For resources where you can learn more about the entire Excel object model, see Using the Excel Object Model Documentation.

link to video For a related video demonstration, see How Do I: Use Event Handlers in an Excel 2007 Add-in?, and How Do I: Use Shapes to Create a Bubble Chart in Excel?.

Accessing Objects in an Excel Project

When you create a new application-level project for Excel by using Visual Studio Tools for Office, Visual Studio automatically creates a ThisAddIn.vb or ThisAddIn.cs code file. You can access the Application object by using Me.Application or this.Application.

When you create a new document-level project for Excel by using Visual Studio Tools for Office, you have the option of creating a new Excel Workbook or Excel Template project. Visual Studio Tools for Office automatically creates the following code files in your new Excel project for both workbook and template projects.

Visual Basic	C#
ThisWorkbook.vb	ThisWorkbook.cs
Sheet1.vb	Sheet1.cs
Sheet2.vb	Sheet2.cs
Sheet3.vb	Sheet3.cs

You can use the global class, Globals, to access ThisWorkbook, Sheet1, Sheet2, or Sheet3 from outside of the respective class. For more information, see Global Access to Objects in Visual Studio Tools for Office Projects. The following example calls the PrintPreview(Object) method of Sheet1 regardless of whether the code is placed in the Sheet class or the Workbook class.

Globals.Sheet1.PrintPreview()

Globals.Sheet1.PrintPreview(missing);

Because the data in an Excel document is highly structured, the object model is hierarchical and straightforward. Excel provides hundreds of objects with which you might want to interact, but you can get a good start on the object model by focusing on a very small subset of the available objects. These objects include the following four:

Application
Workbook
Worksheet
Range

Much of the work done with Excel centers around these four classes and their members.

Application Object

The Excel Application object represents the Excel application itself. The Application object exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance.

Note

You should not set the EnableEvents property of the Application object in Excel to false. Setting this property to false prevents Excel from raising any events, including the events of host controls.

Workbook Object

The Workbook class represents a single workbook within the Excel application.

Visual Studio Tools for Office extends the Workbook class by providing the Microsoft.Office.Tools.Excel.Workbook class, which gives you access to all members of the Workbooks collection, in addition to data-binding capabilities and additional events.

Worksheet Object

The Worksheet object is a member of the Worksheets collection. Many of the properties, methods, and events of the Worksheet are identical or similar to members provided by the Application or Workbook classes.

Excel provides a Sheets collection as a property of a Workbook object, but there is no Sheet class in Excel. Instead, each member of the Sheets collection is either a Worksheet or a Chart object.

For a document-level customization, Visual Studio Tools for Office provides a Microsoft.Office.Tools.Excel.Worksheet host item and creates three instances in new projects: Sheet1, Sheet2, and Sheet3. Access to any of the sheets can be made through the Globals reference. Visual Studio Tools for Office extends the Worksheet object, providing the Microsoft.Office.Tools.Excel.Worksheet host item.

Range Object

The Range object is the object you will use most within your Excel applications. Before you can manipulate any region within Excel, you must express it as a Range object and work with methods and properties of that range. A Range object represents a cell, a row, a column, a selection of cells that contains one or more blocks of cells (which might or might not be contiguous), or even a group of cells on multiple sheets.

For a document-level customization, Visual Studio Tools for Office introduces two range host controls: the Microsoft.Office.Tools.Excel.NamedRange control and the Microsoft.Office.Tools.Excel.XmlMappedRange control. For more information about host controls, see Host Items and Host Controls Overview.

Extended Objects in Document-Level Projects

It is important to understand the differences between the native objects provided by the Excel object model and the extended objects (host items and host controls) provided by Visual Studio Tools for Office. Both types of objects are available to document-level projects.

The differences are in the following four areas:

Design time. When you add any of the extended Excel objects at design time, they are automatically created as host items and host controls. For example, if you add a list to a worksheet in the designer, code is automatically generated to extend the list into a Microsoft.Office.Tools.Excel.ListObject control.
Run time. Host items are not automatically created at run time. If you add workbooks, worksheets, or chart sheets at run time, they are native Excel objects and do not have the additional capabilities that host items provide. You can programmatically add many of the host controls. For more information, see Host Items and Host Controls Overview.
Data binding and events. Host items and host controls have data-binding capabilities and events, which are not available to the native objects.
Types. The native Excel objects use the types defined in the Microsoft.Office.Interop.Excel namespace, whereas host items and host controls use the aggregated types defined in the Microsoft.Office.Tools.Excel namespace.

Using the Excel Object Model Documentation

For information about the classes you can use in the Excel object model, see the following sets of documentation:

The first link provides information about the classes and interfaces in the primary interop assembly for Excel. The other links provide information about the Excel object model as it is exposed to Visual Basic for Applications (VBA) code. Each set of documentation has advantages and disadvantages for developers who are using Visual Studio Tools for Office.

Primary Interop Assembly Reference

This documentation describes all of the types in the Excel primary interop assembly that you can use in Visual Studio Tools for Office projects. However, this documentation has the following disadvantages:

It describes only the types in the primary interop assembly for Excel 2003. For descriptions of new types and members in the primary interop assembly for Excel 2007, you must refer to the VBA reference for Excel 2007.
It does not provide any code examples at this time.

VBA Reference

All of the objects and members in the VBA reference correspond to classes and members in the primary interop assembly that you use in Visual Studio Tools for Office projects. For example, the Worksheet object in the Excel VBA documentation corresponds to the Worksheet class in the primary interop assembly.

The VBA reference has the following advantages:

It documents the object models of both Excel 2007 and Excel 2003.
It provides code examples for most members.

The VBA reference has the following disadvantages:

It provides syntax and code examples for VBA only. To use the code examples in a Visual Studio Tools for Office project, you must translate the VBA code to Visual Basic or Visual C#.

Additional Types in Primary Interop Assemblies

The primary interop assemblies contain many types that are not available to VBA. These additional types help translate objects in the COM-based object model of Excel to managed code, are not intended to be used directly in your code.

For more information, see Overview of Classes and Interfaces in the Office Primary Interop Assemblies.