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

Question

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.

Just curios but could this be achieved the same using an array of clsPerson's? — 99moorem, Jun 02 '15 at 14:15
Btw, dont use `Dim ... as New ...`. It is [**evil**](http://stackoverflow.com/questions/2478097/vba-difference-in-two-ways-of-declaring-a-new-object-trying-to-understand-why).. — Ioannis, Jun 02 '15 at 14:16

score 1 · Answer 1 · answered Jun 02 '15 at 14:16

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

in my case, I have a real loop, with 100+ cycles, so creating variables would be impossible. Thanks anyway, Made things clearer! — Niqlas, Jun 02 '15 at 14:42
Okay, I forgot to mention it in my text but I did add a looping structure to the end of the code. It's similar to your accepted answer in loop form. — Sobigen, Jun 02 '15 at 15:00

score 0 · Answer 2 · answered Jun 02 '15 at 14:08

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

score 0 · Accepted Answer · answered Jun 02 '15 at 14:12

0

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

answered Jun 02 '15 at 14:12

Alex K.

171,639
30
264
288

The first sentence is not true for the OP's code. `Dim ... As New` is declaration and not instantiation in `VBA`. [The variable is instantiating when it is first found in the code](http://www.cpearson.com/excel/classes.aspx). – Ioannis Jun 02 '15 at 14:23
That's a plumbing detail that does not affect this scenario – Alex K. Jun 02 '15 at 14:26

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

3 Answers3