Chapter 12: The Excel Object Model

Overview

The Excel object model is at the heart of using VBA in Excel. It distinguishes programming in Excel from programming in other VBA applications by providing additional commands to access the worksheets and workbooks and by providing all the functionality that the user would normally obtain from the menu structure of Excel. The object model in Microsoft Access, for example, contains commands and objects specifically relating to the database, giving the means to manipulate tables, queries, forms, and reports . In Excel, the whole application is oriented toward a structure of workbooks and spreadsheets, so the object model is written around this. Excel is a three-tier application: the client services tier, the object model, and the data services layer. The usual spreadsheet interface that you view is the client services tier and is the layer that normally communicates with the user.

Underneath this sits the Excel object model. Each time you do something on your spreadsheet, you are issuing commands through the Excel object model. For example, if you open a workbook, the underlying code behind File Open uses the same functionality as the command Workbooks.Open to open your workbook and add it to the workbooks collection object. Similarly, if you have Calculation set to manual in the Options dialog and are using F9 to recalculate, the same functionality as the command Application.Calculate is used each time you do this. Using the Excel object model and a programming language such as Visual Basic, it's not difficult to develop your own Excel front end with exactly the same functionality as the Microsoft Excel front end. Every menu command and function key on the Microsoft Excel front end is represented within the Excel object model. This is not to say that these are the exact objects that Excel uses itself, but Microsoft has empowered you with all the objects and methods so you can do anything in code that can be done from the Excel menu, and as you will find out in the practical examples later on in this book, a whole lot more besides.

Strangely enough, if you decided to write your own front end, there would be relatively little code to write because all the functionality is contained in the object model. Below the Excel object model sits the data services layer, which holds the data in the spreadsheets and is modified by commands from the Excel object model.

The Excel object model contains a large number of objects ‚ for example, Workbooks , Worksheets , Ranges , Charts , Pivot Tables , and Comments . These Excel objects are discrete entities that offer various pieces of data analysis functionality. Most important, they can be controlled from your code.

When programming in Excel using VBA, you use standard VBA commands and functions such as For..Next, If..Then..Else, and MsgBox, but you use the object model to communicate with the Excel application by manipulating the properties and methods of the various objects at your disposal, such as the Workbook object or the Worksheet object.

An object is a programming structure encapsulating both data and functionality that is defined and allocated as a single unit and for which the only public access is through the programming structure's interfaces.

An object is a part of the Excel program. The objects are arranged in a hierarchy. For example, at the top of the object model is the Application object, which is Excel itself. Under the Application object is the Workbook object, and within the Workbook object are Worksheet objects. Within each Worksheet object are Range objects, and so on. Each object can contain settings, called properties, and actions that can be performed on the object, called methods. For example, if you want to enter data into a cell reference using code, you refer to the range property of the worksheet. You specify a cell or a range of cells in the range property and then use the text or value property to place your data in the cells . An example might be

 Worksheets("sheet1").Range("a1").Value="MyData" 

This enters the text ‚“MyData ‚½ into cell A1 on sheet1