Insert a Count property to return the number of items in the collection: Public Property Get Count() As Long

Count = AllEmployees.Count

End Property

Insert an Items property to return the entire collection: Public Property Get Items() As Collection

Set Items = AllEmployees

End Property

Insert an Item property to return a specific item from the collection: Public Property Get Item(myItem As Variant) As clsEmployee

Set Item = AllEmployees(myItem)

End Property

Insert a Remove property to remove a specific item from the collection: Public Sub Remove(myItem As Variant)

AllEmployees.Remove (myItem)

End Sub

Because you have to create your own methods, you can improve on their functionality. For example, in the Add procedure, you can have it create the unique key automatically.

Property Get is used with Count, Item, and Items because these are read-only properties. Item returns a reference to a single member of the collection, whereas Items returns the entire collection (so that it can be used in For Each Next loops).

After the collection is configured in the class module, a procedure can be written in a standard module to use it:

Sub EmpAddCollection()

Dim colEmployees As New clsEmployees

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)

0 0

Post a comment