VBA: How to store a reference to an Object type within the Object type

KingLarryXVII

New Member
Joined
Jan 11, 2013
Messages
11
So this is a bit of a corner case, but I was setting up an Object, cPerson, and wanted to reference their parent's objects, which are also cPerson type, so I could call (rough example):

Set person = New cPerson
Set mother = New cPerson
Set father = New cPerson
set person.Mother = mother
set person.Father = father

The problem is that when I set the properties "Mother" and "Father" within cPerson, I can't have them as type cPerson (stack overflow because each mother needs parents and so on to infinity)

Question: Are there any ways I can create a reference to the 'mother' and 'father' objects within 'person' but not of the type cPerson?

Let me know if the question isn't clear. Thank you in advance!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
There must be something I'm missing, because I don't see how you get a stack overflow error. Here's what I coded

CPerson:
Code:
Option Explicit

Private m_mother As CPerson
Private m_father As CPerson
Private m_name As String

Public Property Get Mother() As CPerson
    Set Mother = m_mother
End Property

Public Property Set Mother(ByVal RHS As CPerson)
    Set m_mother = RHS
End Property

Public Property Get Father() As CPerson
    Set Father = m_father
End Property

Public Property Set Father(ByVal RHS As CPerson)
    Set m_father = RHS
End Property

Public Property Get Name() As String
    Name = m_name
End Property

Public Sub Born(ByVal Name As String)
    m_name = Name
End Sub

and in a standard module:

Code:
Option Explicit

Sub Test()
    Dim theMother As CPerson
    Dim theFather As CPerson
    Dim theChild As CPerson
    
    Set theMother = New CPerson
    theMother.Born "Amy"
    
    Set theFather = New CPerson
    theFather.Born "Charles"
    
    Set theChild = New CPerson
    Set theChild.Father = theFather
    Set theChild.Mother = theMother
    theChild.Born "David"
    
    Debug.Print theChild.Name, theChild.Mother.Name, theChild.Father.Name
End Sub

The result, as expected is:

Code:
David         Amy           Charles

You could also change the class to this:

Code:
Option Explicit

Private m_mother As CPerson
Private m_father As CPerson
Private m_name As String

Public Property Get Mother() As CPerson
    Set Mother = m_mother
End Property

Public Property Get Father() As CPerson
    Set Father = m_father
End Property

Public Property Get Name() As String
    Name = m_name
End Property

Public Sub Born(ByVal Name As String, ByVal Father As CPerson, ByVal Mother As CPerson)
    m_name = Name
    Set m_father = Father
    Set m_mother = Mother
End Sub

and the module to this

Code:
Option Explicit

Sub Test()
    Dim theMother As CPerson
    Dim theFather As CPerson
    Dim theChild As CPerson
    
    Set theMother = New CPerson
    theMother.Born "Amy", Nothing, Nothing
    
    Set theFather = New CPerson
    theFather.Born "Charles", Nothing, Nothing
    
    Set theChild = New CPerson
    theChild.Born "David", theFather, theMother
    
    Debug.Print theChild.Name, theChild.Mother.Name, theChild.Father.Name
End Sub

And it would still work fine.
 
Upvote 0
Hi there,

I'm not sure I follow. I thought I did, then I didn't, then I did... Perhaps you can elaborate a bit? I don't see why you can't just use the same class over again as a type/object. Perhaps some detailed explanation can further this along.

I setup an example of what I thought you were talking about. There is one class module and one standard module.

In a class module named cPerson:
Code:
Option Explicit


Private pMother As cPerson
Private pFather As cPerson
Private pFirstName As String
Private pLastName As String
Private pDOB As Date


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CLASS PROCEDURES & PROPERTIES
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Property Get Mother() As cPerson
    Set Mother = pMother
End Property
Public Property Set Mother(ByVal vMother As cPerson)
    Set pMother = vMother
End Property


Public Property Get Father() As cPerson
    Set Father = pFather
End Property
Public Property Set Father(ByVal vFather As cPerson)
    Set pFather = vFather
End Property


Public Property Get FirstName() As String
    FirstName = pFirstName
End Property
Public Property Let FirstName(ByVal vFirstName As String)
    pFirstName = vFirstName
End Property


Public Property Get LastName() As String
    LastName = pLastName
End Property
Public Property Let LastName(ByVal vLastName As String)
    pLastName = vLastName
End Property


Public Property Get DOB() As Date
    DOB = pDOB
End Property
Public Property Let DOB(ByVal vDOB As Date)
    pDOB = vDOB
End Property

In a standard module named mPersonTest:
Code:
Option Explicit

Sub zFamilyTest()

    'Declare variables
    Dim John As cPerson
    Dim JohnMom As cPerson
    Dim JohnDad As cPerson
    Dim JohnGMa_Mom As cPerson
    Dim JohnGPa_Mom As cPerson
    Dim JohnGMa_Dad As cPerson
    Dim JohnGPa_Dad As cPerson
    
    'Set variables to class
    Set John = New cPerson
    Set JohnMom = New cPerson
    Set JohnDad = New cPerson
    Set JohnGMa_Mom = New cPerson
    Set JohnGPa_Mom = New cPerson
    Set JohnGMa_Dad = New cPerson
    Set JohnGPa_Dad = New cPerson
    
    'Set John's family details
    Set John.Mother = JohnMom
    Set John.Father = JohnDad
    John.FirstName = "John"
    John.LastName = "Doe"
    John.DOB = #1/1/1985#
    
    'Set John's mother's details
    JohnMom.FirstName = "Mary"
    JohnMom.LastName = "Contrary"
    Set JohnMom.Father = JohnGPa_Mom
    Set JohnMom.Mother = JohnGMa_Mom
    JohnMom.DOB = #2/2/1965#
    
    'Set John's father's details
    JohnDad.FirstName = "Mr"
    JohnDad.LastName = "T"
    Set JohnDad.Father = JohnGPa_Dad
    Set JohnDad.Mother = JohnGMa_Dad
    JohnDad.DOB = #3/3/1965#
    
    'Set John's grandmother's details (mom's side)
    JohnGMa_Mom.FirstName = "Marie"
    JohnGMa_Mom.LastName = "Callender"
    JohnGMa_Mom.DOB = #4/4/1935#
    
    'Set John's grandfather's details (mom's side)
    JohnGPa_Mom.FirstName = "Joe"
    JohnGPa_Mom.LastName = "Dirt"
    JohnGPa_Mom.DOB = #12/31/1934#
    
    'Set John's grandmother's details (dad's side)
    JohnGMa_Dad.FirstName = "Elizabeth"
    JohnGMa_Dad.LastName = "Hensley"
    JohnGMa_Dad.DOB = #5/5/1930#
    
    'Set John's grandfather's details (dad's side)
    JohnGPa_Dad.FirstName = "George"
    JohnGPa_Dad.LastName = "Walkford"
    JohnGPa_Dad.DOB = #12/31/1929#
    
    'Message user
    MsgBox "John's family is all setup!", vbInformation, "Set John's Family"
    
End Sub

So if you wanted to reference John's Mother's Mother, it might look something like this...
Code:
    With John
        With .Mother
            With .Mother
                MsgBox .FirstName & " " & .LastName
            End With
        End With
    End With


'or....


    With John.Mother.Mother
        MsgBox .FirstName & " " & .LastName
    End With

And if you wanted to reference John's Father's Mother, it might look something like this...
Code:
    With John
        With .Father
            With .Mother
                MsgBox .FirstName & " " & .LastName
            End With
        End With
    End With


'or...


    With John.Father.Mother
        MsgBox .FirstName & " " & .LastName
    End With

Is this what you're looking for? If not please give some more details. The more detailed you are the faster you'll get what you need. :)
 
Upvote 0
Hey! I wanted to bring the best brains I knew into this! It's an interesting question. Though I don't get the declaration of variables as the class module they're in..
 
Upvote 0
Think of them as recursive calls. You can still reference the object type of the object you're instantiating. It's like saying, "hey you, you're like me, got it?" Make sense?
 
Upvote 0
Thanks all for the quick responses. This is what I get for being self-taught, I never knew Get/Set existed vs Get/Let. Instead, I've always used class_initialize() which creates the run-away instances.

Old Code:
Code:
Private myChildrenCount As IntegerPrivate myMother As cPerson
Private myFather As cPerson


'Should parents be references to their person?
Private Sub class_initialize()


    Set myMother = New cPerson
    Set myFather = New cPerson
End Sub


Public Property Get Mother() As cPerson
    Set Mother = myMother
End Property
Public Property Let Mother(value As cPerson)
    Set myMother = value
End Property




Public Property Get Father() As cPerson
    Set Father = myFather
End Property
Public Property Let Father(value As cPerson)
    Set myFather = value
End Property




Public Property Get ChildrenCount() As Integer
    ChildrenCount = myChildrenCount
End Property
Public Property Let ChildrenCount(value As Integer)
    myChildrenCount = value
End Property

Changing the Mother and Father 'Let's to 'Set's and then removing the initalize 'Set's fixes it. Thanks again everyone!
 
Last edited:
Upvote 0
It's one of the many disadvantages of VBA vs. VB (where there is no Let string literal, only Get and Set for properties, and they're not used in standard procedures).

In your code above you still have the Let keyword for setting the property. That needs to be Set.

Rich (BB code):
Public Property Let Mother(value As cPerson)
    Set myMother = value
End Property
...should be...
Rich (BB code):
Public Property Set Mother(value As cPerson)
    Set myMother = value
End Property
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top