How do I create this VBA structure?

Question

I've been learning how to use classes in VBA over the past couple of months but am having a problem figuring out what's the proper way of grouping multiple class items together so I can iterate through them.

For example, I have a class named clsPersons with Properties such as Name, Gender, DOB, and Age (Today - DOB). If I just wanted to add one person I would do the following:

    Dim Person As New clsPersons

    Person.Name = "Phillip"
    Person.Gener = "Male"
    Person.DOB = "#1/1/2000"
    ' Person.Age would be automatically set by adding Person.DOB

However, if I had a spreadsheet with everyone on it and decide to create a report with everyone who's name begins with the letter "P", then I first need some kind of object to use (collection, dictionary, etc) and something like this to fill it:

    Dim Person As New clsPersons
    Dim lastRow As Long
    lastRow = Sheet1.Cells(Rows.Count, 1).Row(xlUp).End

    For i = 1 To lastRow
        If Worksheet.Function.Left(Sheet1.Cells(i, 1), 1) = "P" Then
            Person.Name = Sheet1.Cells(i, 1)
            Person.Gener = Sheet1.Cells(i, 2)
            Person.DOB = Sheet1.Cells(i, 3)
        End If
    Next i

After filling it I would then want to be able to loop through it to pull items out, so something like this:

    For Each Person In objPeople
        Sheet2.Cells(i, 1) = Person.Name
        Sheet2.Cells(i, 1) = Person.Gender
        Sheet2.Cells(i, 1) = Person.Age
    Next Person

Hopefully what I've outlined makes sense. Simply put I'm just looking for the proper way to group the People together into one object that I can iterate through later on in my procedure.

pheeper · Accepted Answer · 2016-09-06 15:52:01Z

6

Putting multiple instances of your class into a Collection would be fine.

I would maybe name your class clsPerson though, since each instance only represents a single person.

Dim col As New Collection
Dim lastRow As Long
lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lastRow
    With Sheet1.Rows(i)
        If Left(.Cells(1), 1) = "P" Then
            col.Add Person(.Cells(1), .Cells(2), .Cells(3))
        End If
    End with
Next i

Person function:

Function Person(nm, gender, dob) as clsPerson
    Dim p As New clsPerson
    With p
        .Name = nm
        .Gender = gender
        .DOB = dob
    End With
    Set Person = p
End Function

edited Sep 6, 2016 at 15:52

pheeper

1,5475 gold badges22 silver badges38 bronze badges

answered Sep 5, 2016 at 21:29

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pheeper Over a year ago

I wasn't sure how that would work with collections, and if it did work if that was really the correct way to do it so thanks for confirming. That's also a great piece of tidy code, thanks for posting!!

Collectives™ on Stack Overflow

How do I create this VBA structure?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related