I know you’re thinking that class module week is over, and you’re right. This post is more about the Excel Object Model and how it is set up. It stems from a couple of comments on another post that correctly point out that Application.Transpose(...) and Application.WorksheetFunction.Transpose(...) do the same thing.
For this example, we’ll look at the Application object and how it relates to the Range object. The hierarchy goes like this: Application => Workbook => Worksheet => Range. Yet you can access a Range object directly from the Application object. The key point to know is that when you type Application.Range... you’re not accessing the Range object directly, rather you’re using the Range property of the Application object. A subtle distinction, I’m sure you’ll agree. When you’re first learning the Excel Object Model, it’s much more convenient and less confusing to think of Application.Workbooks(1).Worksheets(2).Range("A1") as a string of four objects connected by periods. But in reality, you’re getting a Range object from the Range property of a Worksheet object. You get the Worksheet object from the (default) Item property of a Worksheets collection object. You get the Worksheets collection object from the Worksheets property of a Workbook object, which comes from the Item property of a Workbooks collection object, which comes from the Workbooks property of the Application object. Well, you can see why book authors use the shortcut to explain this.
Let’s look at how having different properties that return the same object works by writing the Excel Object Model in VBA. Well, kind of. Create class modules in a new workbook named CApplication, CCell, CRange, CWorkbook, CWorkbooks, CWorksheet, CWorksheets and a standard module for testing. Or you can download the workbook at the end of this post to follow along.
Here’s the code I want to run and have it work.
Dim clsApplication As CApplication
Set clsApplication = New CApplication
clsApplication.Workbooks.Add
clsApplication.Workbooks(1).Worksheets(“Sheet1”).Range(“D7”).Value = 37
Debug.Print clsApplication.Workbooks(1).Worksheets(“Sheet1”).Range(“D7”).Value, _
clsApplication.Range(“D7”).Value
End Sub
I want to create a new Application object, add a workbook to it, assign a value to a “cell”, and finally access that cell through a big hierarchy AND through the Application object.
We’ll start with the CRange object and move up to the Application object. We’re going to make an abbreviated Range object – rather than all the cells normally found in a worksheet, ours will only be A1:D10. The Range object is a bit odd as it’s really a collection object, and so it will be with our CRange object. Our CRange object will be a collection of CCell objects. Each CCell object will have two properties; Address and Value. CCell looks like this:
Private msAddress As String
Public Property Get Value() As Variant
Value = mvValue
End Property
Public Property Let Value(ByVal vValue As Variant)
mvValue = vValue
End Property
Public Property Get Address() As String
Address = msAddress
End Property
Public Property Let Address(ByVal sAddress As String)
msAddress = sAddress
End Property
CRange looks like this:
Private Sub Class_Initialize()
Dim clsCell As CCell
Dim i As Long, j As Long
Set mcolCells = New Collection
For i = 1 To 4
For j = 1 To 10
Set clsCell = New CCell
clsCell.Address = Chr$(64 + i) & j
mcolCells.Add clsCell, clsCell.Address
Next j
Next i
End Sub
Private Sub Class_Terminate()
Set mcolCells = Nothing
End Sub
Public Property Get Item(sAdd As String) As CCell
Set Item = mcolCells.Item(sAdd)
End Property
Whenever we create a new Range collection object, 40 cells are automatically created with addresses like A1, C8, etc. and no values. This is like when you insert a new worksheet into a workbook. In addition to creating a Worksheet object, you’re also creating millions of cells. OK, not really because Excel’s Range object is a lot more sophisticated than mine, but you get the idea. With CRange, you can only get at one CCell at a time. That’s a pretty big departure from Excel’s Range object, but I don’t have all day here.
Tomorrow we’ll code the rest of the class modules and see if we can get that test procedure to run.
You can download ReproObjectModel.zip
I never knew there was a Range property of the Application object. I guess it makes sense, since Range(“A1?) = 123 is allowed.
I did a test, and set up a worksheet specific name in both Sheet1 and Sheet2. eg Sheet1!MyName, and Sheet2!MyName
Then from vba:
Sheet1
?ThisWorkbook.Worksheets(2).Range(“MyName”)
Sheet2
Sheet1.Activate
?Application.Range(“MyName”)
Sheet1
Sheet2.Activate
?Application.Range(“MyName”)
Sheet2
Looks like Application.Range hooks the ActiveSheet range.
Application.Transpose is NOT the same as Application.WOrksheetFunction.Transpose.
Take this example:
– Enter 1,2,3 in cellA1 to A3
– Enter =NA() in cell A4
– run this routine:
Dim vResult As Variant
vResult = Application.Sum(Range(“A1:A4”).Value)
MsgBox TypeName(vResult)
vResult = Application.WorksheetFunction.Sum(Range(“A1:A4”).Value)
End Sub
Note the difference in how the error is handled
That’s right Rob. Strangely, to me, there is no Range property of ActiveWorkbook, which I thought would have also pointed to the ActiveSheet.
JKP: That’s an interesting difference. I wonder how that’s coded in Excel. That is, I wonder if it’s an accidental consequence or if they intended that way. A few years ago, Tushar convinced me to use WorksheetFunction whenever referring to one. His argument, if I recall, was that it’s more readable. And since I have no problem trapping errors with On Error for other things, I shouldn’t have a problem using it here. That was persuasive to me, so I’ve been spelling it out ever since.
I tend to use it without the wsf and check what I get returned (typename). That way I don’t have to use an error handler or on error resume next stuff. A matter of taste or habit I think.
Dick, I like your approach i.e. you start by saying, “Here’s the code I want to run and have it work” and then you actually write go ahead and the ‘client side’ code before you’ve even started the ‘server side’ (object model) code. This is absolutely key to getting abstraction correct.
Great series on class modules, BTW. As I may have already hinted, I fully expect articles in the near future on 1) safely referencing a parent object from a child (e.g. add a Public Property Get Parent() As CWorksheet to the Range object) and 2) subclassing (e.g. a Sheets collection that returns objects of type CWorksheet, CDialogSheet, etc). If not I’ll threaten to dust off my notes and do it myself ;)