Tag Archives: collection

class collection in VBA

Posted on by
Reply

It takes more effect to set up you collection in a class module but it will make your code more portable and easier to maintain.
Employee class

Option Explicit
 
Private FName As String
Private FHoursPerWeek As Double
Private FRate As Double
 
Public Function GetGrossWeeklyPay() As Double
    GetGrossWeeklyPay = FHoursPerWeek * FRate
End Function
 
Public Property Get Name() As String
    Name = FName
End Property
 
Public Property Get HoursPerWeek() As Double
    HoursPerWeek = FHoursPerWeek
End Property
 
Public Property Get Rate() As Double
    Rate = FRate
End Property
 
Public Property Let Name(v As String)
    FName = v
End Property
 
Public Property Let HoursPerWeek(v As Double)
     FHoursPerWeek = v
End Property
 
Public Property Let Rate(v As Double)
    FRate = v
End Property

Emoployees class

Option Explicit
 
Private FEmployees As New Collection
Public Function Add(ByVal value As Employee)
    Call FEmployees.Add(value, value.Name)
End Function
 
Public Property Get Count() As Long
    Count = FEmployees.Count
End Property
 
Public Property Get Items() As Collection
    Set Items = FEmployees
End Property
 
Public Property Get Item(ByVal v As Variant) As Employee
    Set Item = FEmployees(v)
End Property
 
Public Sub remove(ByVal v As Variant)
    Call FEmployees.remove(v)
End Sub

test module

Option Explicit
Dim theEmployees As New Employees
 
Sub testEmployeesCollection()
    Dim anEmployee As Employee
    Dim I As Long
 
    For I = theEmployees.Count To 1 Step -1
        Call theEmployees.remove(I)
    Next I
 
    Set anEmployee = New Employee
    anEmployee.Name = "Paul Kimmel"
    anEmployee.Rate = 15
    anEmployee.HoursPerWeek = 45
    Call theEmployees.Add(anEmployee)
 
    Set anEmployee = New Employee
    anEmployee.Name = "Bill Gates"
    anEmployee.Rate = 13
    anEmployee.HoursPerWeek = 56
    Call theEmployees.Add(anEmployee)
 
    Debug.Print "number of employees = " & theEmployees.Count
    Debug.Print "employees(2) = " & theEmployees.Item(2).Name
    Debug.Print "employees(paul kimmel rate) = " & theEmployees.Item("Paul Kimmel").Rate
    For Each anEmployee In theEmployees.Items
        Debug.Print anEmployee.Name & "earns $" & anEmployee.GetGrossWeeklyPay()
    Next anEmployee
End Sub