CODEWITHSUNDEEP
vbaoopexcel
Niqlas
Niqlas

Reputation: 13

Adding Objects to a collection in VBA --> OOP-Basics?

I am new to OOP, so probably there is an obvious explaination why this does not work. I am trying to add objects to a collection in VBA. My class module is this:

Option Explicit

'the person class
Public FirstName As String
Public LastName As String

Property Get FullName() As String
  'return the person's full name
  FullName = FirstName & " " & LastName
End Property

My Code is this:

Sub myProg()


'create a new collection!
Dim Persons As New Collection

Dim p1 As New clsPerson

'give them names in "Loop"

p1.FirstName = "Rita"
p1.LastName = "Smith"
Persons.Add p1

p1.FirstName = "Sue"
p1.LastName = "Jones"
Persons.Add p1

p1.FirstName = "Bob"
p1.LastName = "Brown"
Persons.Add p1

'"Loop" end

For Each p1 In Persons
  Debug.Print p1.FullName
Next p1

End Sub

It returns 3 times "Bob Brown". I would like it to return the 3 names I entered.

Upvotes: 1

Views: 1150

Answers (3)

Sobigen
Sobigen

Reputation: 2179

When you alter p1 the 2nd and 3rd time you're altering the reference at each spot in the collection. The collection hold a reference to p1 and that reference can be altered externally from the collection itself. You need to make three person objects.

Sub myProg()
    'create a new collection!
    Dim Persons As Collection

    Dim p1 As clsPerson
    Dim p2 As clsPerson
    Dim p3 As clsPerson
    Dim p As clsPerson

    'give them names in "Loop"

    set Persons = New Collection
    set p1 = new clsPerson
    p1.FirstName = "Rita"
    p1.LastName = "Smith"
    Persons.Add p1

    set p2 = new clsPerson        
    p2.FirstName = "Sue"
    p2.LastName = "Jones"
    Persons.Add p2

    set p3 = new clsPerson        
    p3.FirstName = "Bob"
    p3.LastName = "Brown"
    Persons.Add p3

    '"Loop" end

    For Each p In Persons
      Debug.Print p.FullName
    Next p

    'alternate looping way where a new object is created each time
    For i = 1 To 5
        Set p = New clsPerson
        p.FirstName = "First Name" & i
        p.LastName = "Last Name" & i
        Persons.Add p
        Set p = Nothing 'may not be necessary
    Next

End Sub

Upvotes: 1

Alex K.
Alex K.

Reputation: 175956

p1 is a reference variable, it points to the single specific instance of clsPerson you created when you used New.

When you add a reference variable to a collection you are adding the reference itself, not a copy, meaning the p1 in the collection is always going to be pointing to the same clsPerson instance which will contains the last values you assigned to it.

You need to use New to create a new, independent instance of the class and add that to the collection, E.g.

Set p1 = New clsPerson
p1.FirstName = "Bob"
p1.LastName = "Brown"
Persons.Add p1

Set p1 = New clsPerson
p1.FirstName = "Sue"
p1.LastName = "Jones"
Persons.Add p1

Upvotes: 0

R3uK
R3uK

Reputation: 14547

Just don't REuse p1 in the For Each and this will do it ;)

Sub myProg()


'create a new collection!
Dim Persons As New Collection

Dim p1 As New clsPerson
Dim pT As clsPerson

'give them names in "Loop"

p1.FirstName = "Rita"
p1.LastName = "Smith"
Persons.Add p1

p1.FirstName = "Sue"
p1.LastName = "Jones"
Persons.Add p1

p1.FirstName = "Bob"
p1.LastName = "Brown"
Persons.Add p1

'"Loop" end

For Each pT In Persons
  Debug.Print pT.FullName
Next pT

End Sub

Upvotes: 0

Related Questions