Creating a Collection in a Standard Module

The quickest way to create a collection is to use the built-in Collection method. By setting up a collection in a standard module, you can access the four default collection methods: Add, Remove, Count, and Item.

The following example reads a list of employees off a sheet and into an array. It then processes the array, supplying each property of the object with a value, and places each record in the collection:

Sub EmpPayCollection()

Dim colEmployees As New Collection

Dim recEmployee As New clsEmployee

Dim LastRow As Integer, myCount As Integer

Dim EmpArray As Variant

LastRow = ActiveSheet.Range("A65536").End(xlUp).Row EmpArray = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, 4))

For myCount = 1 To UBound(EmpArray) With recEmployee

.EmpName = EmpArray(myCount, 1) .EmpID = EmpArray(myCount, 2) .EmpRate = EmpArray(myCount, 3) .EmpWeeklyHrs = EmpArray(myCount, 4) colEmployees.Add recEmployee, .EmpID End With Next myCount

MsgBox "Number of Employees: " & colEmployees.Count & Chr(10) & _

"Employee(2) Name: " & colEmployees(2).EmpName MsgBox "Tracy's Weekly Pay: $" & colEmployees("1651").EmpWeeklyPay

Set recEmployee = Nothing

0 0

Post a comment