Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: VBA: How to store a reference to an Object type within the Object type
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: How to store a reference to an Object type within the Object 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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  4. #4
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Well heya Juan! Should've refreshed before I posted.
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Zack Barresse View Post
    Well heya Juan! Should've refreshed before I posted.
    Hey Zack... it's all Tracy's fault. You can blame it on her.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Heh, oh I will!
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  7. #7
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

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

  8. #8
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

  9. #9
    New Member
    Join Date
    Jan 2013
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by KingLarryXVII; Apr 11th, 2015 at 11:13 AM. Reason: forgot information on the fix

  10. #10
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,724
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Code:
    Public Property Let Mother(value As cPerson)
        Set myMother = value
    End Property
    ...should be...
    Code:
    Public Property Set Mother(value As cPerson)
        Set myMother = value
    End Property
    Regards,
    Zack Barresse
    My Book on Excel Tables
    (If you would like comments in any code, please say so.)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •