CODEWITHSUNDEEP
vbacollectionsmultiple-instances
Davis
Davis

Reputation: 21

VBA and collection - need set a instance to nothing before add to collection?

I'm trying to add an object twice with modified values ​​for a collection but at the end of the collection contains the same values ​​for the two items. Where am I wrong?

Private Sub MySub()
    Dim ClientList As Collection
    Set ClientList = New Collection

    Dim Inst1 As Client

    Set Inst1 = New Client

    Inst1.Code = 1
    Inst1.Name = "First Client"
    'Adding first client
    ClientList.Add Inst1 

    Inst1.Code = 2
    Inst1.Name = "Second Client"
    'Adding second client
    ClientList.Add Inst1

    Set Inst1 = ClientList(1)
    MsgBox Inst1.Name 'Show "Second Client"   

    Set Inst1 = ClientList(2)
    MsgBox Inst1.Name 'Show "Second Client" too   


End Sub

Upvotes: 2

Views: 1586

Answers (1)

cmsjr
cmsjr

Reputation: 59175

The collection is storing a reference to your object Inst1, you need to either use a second variable, or set the existing variable = to a new instance 

   Private Sub MySub()
        Dim ClientList As Collection
        Set ClientList = New Collection

        Dim Inst1 As Client

        Set Inst1 = New Client

        Inst1.Code = 1
        Inst1.Name = "First Client"
        'Adding first client
        ClientList.Add Inst1 

        'Setting Inst1 to a new instance 
        Set Inst1 = New Client

        Inst1.Code = 2
        Inst1.Name = "Second Client"
        'Adding second client
        ClientList.Add Inst1

        Set Inst1 = ClientList(1)
        MsgBox Inst1.Name 'Show "Second Client"   

        Set Inst1 = ClientList(2)
        MsgBox Inst1.Name 'Show "Second Client" too   


    End Sub

Upvotes: 5

Related Questions