Properties and Objects

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.

Sub test()
   
    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 mvValue As Variant
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 mcolCells As Collection

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

Posted in Uncategorized

5 thoughts on “Properties and Objects

  1. 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:

    ?ThisWorkbook.Worksheets(1).Range(“MyName”)
    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.

  2. 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:

    Sub test()
        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

  3. 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.

  4. 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.

  5. 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 ;)

Leave a Reply

Your email address will not be published. Required fields are marked *