CODEWITHSUNDEEP
vbaexcel
AndyW
AndyW

Reputation: 430

Excel VBA: Retrieving custom objects from a collection

I have a Person Object comprising forename, surname and section. These are stores in columns in an excel table but I need to extract these and pass them as a collection of Person Objects. I can create the objects and apparently add all 39 of them to the collection but when I foreach over the collection I get nothing. I have debug.print that appear to show the individual object being created OK but when I print out the retrieved objects I get 39 lines of blankness.

Public Function PeopleList() As Collection

    Set PeopleList = New Collection
    Dim newPerson As Person
    Set newPerson = New Person
    'hide active sheet and go to sheet holding class information
    SwitchSheets
    lastLine = GetFirstFreeLine(SHEETNAME, 1)
    For x = 2 To lastLine
        newPerson.idNumber = Worksheets(SHEETNAME).Cells(x, 1)
        newPerson.Forename = Worksheets(SHEETNAME).Cells(x, 2)
        newPerson.Surname = Worksheets(SHEETNAME).Cells(x, 3)
        newPerson.SectionName = Worksheets(SHEETNAME).Cells(x, 4)

        'Test print the newPerson object
        Debug.Print (newPerson.Forename & " " & newPerson.Surname & " " & newPerson.SectionName)

        PeopleList.Add newPerson
    Next

    For Each newPerson In PeopleList
        Debug.Print ("Person : " & newPerson.Forename & " " & newPerson.Surname & " " & newPerson.SectionName)
    Next
'restore active sheet
    ReturnSheets

End Function

Upvotes: 0

Views: 153

Answers (1)

Rory
Rory

Reputation: 34045

You need to create the New object inside your loop:

Public Function PeopleList() As Collection

Set PeopleList = New Collection
Dim newPerson As Person

'hide active sheet and go to sheet holding class information
SwitchSheets
lastLine = GetFirstFreeLine(SHEETNAME, 1)
For x = 2 To lastLine
    Set newPerson = New Person
    newPerson.idNumber = Worksheets(SHEETNAME).Cells(x, 1)
    newPerson.Forename = Worksheets(SHEETNAME).Cells(x, 2)
    newPerson.Surname = Worksheets(SHEETNAME).Cells(x, 3)
    newPerson.SectionName = Worksheets(SHEETNAME).Cells(x, 4)

    'Test print the newPerson object
    Debug.Print (newPerson.Forename & " " & newPerson.Surname & " " & newPerson.SectionName)

    PeopleList.Add newPerson
Next

For Each newPerson In PeopleList
    Debug.Print ("Person : " & newPerson.Forename & " " & newPerson.Surname & " " & newPerson.SectionName)
Next
'restore active sheet ReturnSheets

End Function

Upvotes: 1

Related Questions