Why doesn’t an Excel/VBA user defined default property that returns a Range behave like a Range?

Question posted 2012 · +8 upvotes

A property in an Excel/VBA class I’m writing returns a Range. I made it the default property for the class using the technique described at http://www.cpearson.com/excel/DefaultMember.aspx. I expected to use all the Range class’s built-in properties and methods with objects of my class without specifying the property explicitly. It doesn’t work. Here are a couple of much simpler classes to illustrate. (These listings are the exported source viewed with a text editor since VBA’s editor hides the Attribute statements.)

' clsDefLong:    This class just verifies that default properties work as I expected.
Public Property Get DefProp() As Long
Attribute DefProp.VB_UserMemId = 0
    DefProp = 125
End Property

' clsDefRange:  This class is identical except the default property returns a Range.
Public Property Get DefProp() As Range
Attribute DefProp.VB_UserMemId = 0
    Set DefProp = ActiveCell
End Property

Here’s a Sub in a normal module to instantiate and test the classes. The comments indicate what happens when I single step through it:

Sub DefTest()
    Dim DefRange As New clsDefRange, DefLong As New clsDefLong
    Debug.Print DefLong.DefProp         '(1) Displays 125. Verifies the class behaves as intended.
    Debug.Print DefLong                 '(2) Same as (1). Verifies VBA uses the DefProp property as the default.
    Debug.Print DefRange.DefProp.Value  '(3) Displays the ActiveCell content. Verifies that this class works as intended.
    Debug.Print DefRange.DefProp        '(4) Same as (3). Verifies VBA knows DefProp returns a Range without further prompting.
    Debug.Print DefRange                '(5) Aborts with the messge "Run-time error '13': Type mismatch"
 End Sub

Why doesn’t DefRange in statement (5) behave just like DefRange.DefProp in statement (4)?

If I change statement (5) to:

Debug.Print DefRange.Cells(1, 1) 

The compiler selects “.Cells”, says “Compile error: Method or data member not found” and stops so the problem is in the object model – not just something getting messed up at run-time. Am I doing something wrong? Or isn’t it possible to have a default property that returns a Range? How about other built-in classes? User defined classes?