Using Classes In Excel VBA
The following code demonstrates how to create multiple instances of a class and store those instances in a collection.
Program Code
' *****************************************************************
' This is a Regular Module in Excel
' It creates instances of a Class and adds those instances
' To a Collection, allowing multiple data fields in
' each member of the collection
' *****************************************************************
Option Explicit
Public colEmployees As Collection
Public Sub AddClassToCollection()
' *******************************************************
' Pass and Retrieve Information from a Class
' Note: Each new entry in the Class requires another
' New instance of that Class
' *******************************************************
Dim colEmployees As New Collection ' Instantiate the Collection
Dim Emp As cEmployees
Set Emp = New cEmployees ' Create first instance of cEmployees
Emp.Name = "Joe Smith"
Emp.Address = "123 Main Street"
Emp.Salary = 40000
colEmployees.Add Emp
Debug.Print Emp.Name
Debug.Print Emp.Address
Debug.Print Emp.Salary
Debug.Print Emp.WithholdingTax
' *******************************************************
' Each instance of cEmployees requires a "NEW" instance
' For example, to add 3 employees, it requires 3 NEW
' Statements. Things get strange if each new
' employee doesn't have a NEW instantiation.
' *******************************************************
Set Emp = New cEmployees ' Create second instance of cEmployees
Emp.Name = "Mary Jones"
Emp.Address = "1000 First Avenue"
Emp.Salary = 50000
colEmployees.Add Emp
Debug.Print Emp.Name
Debug.Print Emp.Address
Debug.Print Emp.Salary
Debug.Print Emp.WithholdingTax
' *******************************************************
' Access the data via subscripts
' *******************************************************
Debug.Print colEmployees.Item(1).Name
Debug.Print colEmployees.Item(2).Name
' *******************************************************
' Access the data using a For loop
' *******************************************************
For Each Emp In colEmployees
Debug.Print Emp.Name
Emp.PrintPaycheck
Next Emp
End Sub
' *****************************************************************
' This is a CLASS Module in Excel named cEmployees
' *****************************************************************
Option Explicit
Private pName As String
Private pAddress As String
Private pSalary As Double
Private pWitholding As Double
''''''''''''''''''''''
' Name property
''''''''''''''''''''''
Public Property Get Name() As String
Name = pName
End Property
Public Property Let Name(Value As String)
pName = Value
End Property
''''''''''''''''''''''
' Address property
''''''''''''''''''''''
Public Property Get Address() As String
Address = pAddress
End Property
Public Property Let Address(Value As String)
pAddress = Value
End Property
''''''''''''''''''''''
' Salary property
''''''''''''''''''''''
Public Property Get Salary() As Double
Salary = pSalary
End Property
Public Property Let Salary(Value As Double)
pSalary = Value
End Property
Property Get WithholdingTax() As Double
WithholdingTax = pSalary * 0.1
End Property
Public Sub PrintPaycheck()
MsgBox ("Check Printed For " & pName)
End Sub
Private Sub Class_Terminate()
MsgBox ("Class Terminated")
End Sub
Private Sub Class_Initialize()
MsgBox ("Class Initialized")
End Sub