VBA - trying to implement inheritance

Question

I am trying to implement Inheritance in VBA in the following way -

I have one class module clsRange as shown below

Private strRngName as String

Public Property Let RangeName(ByVal thisRangeName As String)
    strRngName = thisRangeName
End Property

Public Property Get RangeName() As String
    RangeName= strRngName
End Property

Another class module clsChildRange

private rngHolder as New clsRange

Public Property Get RangeName() As String
   Set RangeName = rngHolder.RangeName
End Property

Public Property Let RangeName(ByVal thisRangeName As String)
    rngHolder.RangeName = thisRangeName
End Property

I have a module, In that I am trying to create an object for clsChildRange and try to set the properties of clsRange in the following way

Dim objCRng  as New clsChildRange

objCRng.RangeName= "Range1"

But I get an error - object variable or with block variable not set.

That should work as you expect, on what line is the error, have you stepped into objCRng.RangeName ? — Alex K.
– Alex K., Commented Aug 7, 2012 at 10:35
yes I stepped into objCRng.RangeName. I get the error where I try to assign "Range1" — Vikram
– Vikram, Commented Aug 7, 2012 at 10:38
If I create those classes your code works fine from a module, is there other code? — Alex K.
– Alex K., Commented Aug 7, 2012 at 13:13
Thanks Alex , I figured it out, I did not use "Set" keyword where I have to use it in another part of the code. Problem solved. — Vikram
– Vikram, Commented Aug 8, 2012 at 5:57

Dick Kusleika · Accepted Answer · 2012-08-07 16:20:11Z

As Uri said, rngHolder is not instantiated which is causing the problem. Don't test for Null, but test of Is Nothing. Here are two ways to do this, depending on what you're trying to accomplish.

Explicitly Set the Range

In CRange

Private msRngName As String

Public Property Let RngName(ByVal sRngName As String): msRngName = sRngName: End Property
Public Property Get RngName() As String: RngName = msRngName: End Property

In CChildRange

Private mclsRange As CRange

Public Property Set Range(ByVal clsRange As CRange): Set mclsRange = clsRange: End Property
Public Property Get Range() As CRange: Set Range = mclsRange: End Property

Public Property Get RngName() As String

    If Not Me.Range Is Nothing Then
        RngName = Me.Range.RngName
    End If

End Property

Public Property Let RngName(sName As String)

    If Not Me.Range Is Nothing Then
        Me.Range.RngName = sName
    End If

End Property

Then in a standard module

Sub test()

    Dim clsRange As CRange
    Dim clsChildRange As CChildRange

    'Create a new CRange instance
    Set clsRange = New CRange

    'Create a new CChildRange instance
    Set clsChildRange = New CChildRange

    'Set the Range property to the CRange instance
    Set clsChildRange.Range = clsRange

    'Set the RngName property of the chile
    clsChildRange.RngName = "Range1"

    'Test that the parent has the property set
    Debug.Assert clsRange.RngName = "Range1"

End Sub

Implicitly set the Range

CRange is the same.

In CChildRange

Private mclsRange As CRange

Public Property Set Range(ByVal clsRange As CRange): Set mclsRange = clsRange: End Property
Public Property Get Range() As CRange: Set Range = mclsRange: End Property

Public Property Get RngName() As String

    RngName = Me.Range.RngName

End Property

Public Property Let RngName(sName As String)

    Me.Range.RngName = sName

End Property

Private Sub Class_Initialize()

    Set mclsRange = New CRange

End Sub

Private Sub Class_Terminate()

    Set mclsRange = Nothing

End Sub

Then in a standard module

Sub test()

    Dim clsChildRange As CChildRange

    'Create a new CChildRange instance
    'Range object created when class is created
    Set clsChildRange = New CChildRange

    'Set the RngName property of the chile
    clsChildRange.RngName = "Range1"

    'Test that the parent has the property set
    Debug.Assert clsChildRange.Range.RngName = "Range1"


End Sub

Thanks Dick , I figured it out, I did not use "Set" keyword where I have to use it in another part of the code. Problem solved.

Uri Goren · Accepted Answer · 2012-08-07 11:56:21Z

1

I suspect that the rngHolder member isn't getting initialized in this matter, try this code

private rngHolder as clsRange

Public Property Get RangeName() As String
   If rngHolder=Null Then Set rngHolder=New clsRange
   Set RangeName = rngHolder.RangeName
End Property

Public Property Let RangeName(ByVal thisRangeName As String)
    If rngHolder=Null Then Set rngHolder=New clsRange
    rngHolder.RangeName = thisRangeName
End Property

answered Aug 7, 2012 at 11:56

Uri Goren

13.8k8 gold badges63 silver badges113 bronze badges

1 Comment

Vikram Over a year ago

Thanks Uri, I figured it out, I did not use "Set" keyword where I have to use it in another part of the code. Problem solve

Collectives™ on Stack Overflow

VBA - trying to implement inheritance

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related