This answer is provided by Tushar Mehta

ID: 4

Category: Excel Object Model

Question: Can you explain the Excel object model


In general, the XL Object model is just that -- an Object model. So, (almost) everything stems from the Application object. Each object has properties and methods. Some of those properties/methods yield numbers, others are text, yet others are objects themselves.

For example, suppose one wants to examine the contents of cell A1 of Worksheet Sheet1 of the activeworkbook. Strictly speaking one would use Application.ActiveWorkbook.Sheets.Item("Sheet1") .Range("A1").Value.

Let's step through this bit by bit.

Application.ActiveWorkbook uses the Activeworkbook property of the Application object to yield a workbook object.

Then, Application.ActiveWorkbook.Sheets uses the Sheets property of the workbook object to resturn a collection of all sheets in the workbook. Application.ActiveWorkbook.Sheets.Item("Sheet1") isolates just one sheet in that collection, the sheet named Sheet1. A shortcut that many programmers use with a collection object is to skip the Item part as in Application.ActiveWorkbook.Sheets("Sheet1").

Next, Application.ActiveWorkbook.Sheets("Sheet1").Range("A1") uses the Range collection of the worksheet object to yield a range object.

Finally, Application.ActiveWorkbook.Sheets("Sheet1").Range("A1").Value uses the Value property of the Range object to get the numeric/text value stored in the cell.

For the sake of programming ease, one can often forgo the 'high level' objects and properties. In this case just using Sheets("Sheet1").Range("A1") is a shortcut that assumes Application.ActiveWorkbook. It also relies on the fact that the default property of the Range object is the Value property and hence the last .Value can be skipped.

While it is tempting to take such shortcuts -- or even writing Range("A1") as a shortcut to the Application.ActiveWorkbook.ActiveSheet.Range("A1").Value -- it is fraught with the potential for subtle bugs that might be hard to debug.

Personally, I'd rather do a little extra typing than wonder some months down the road why my code suddenly doesn't work under certain hard-to-define conditions. Though, usually, I do skip the Application object. And, I've learnt the hard way to *not* skip the .Value part.


Website material copyright 2003-2006 TM Faculty Associates