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 Microsoft Office version 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 Microsoft.Office.Interop.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.
.gif)
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 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.
.gif) 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
Worksheet Object
Range Object
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 Microsoft.Office.Interop.Excel..::.Worksheet class in the primary interop assembly.
The VBA reference has the following advantages:
The VBA reference has the following disadvantages:
Additional Types in Primary Interop Assemblies
Concepts
Other Resources