Working with Class Modules

I’d been working with Excel and VBA for more then 6 years before I caught on to just how useful class modules can be.  Class modules can be a powerful tool used within intermediate to advanced level VBA programming.  Below is something I hope you will find a useful introduction to how to use them.

This example does assume some basic knowledge of the VBE (Visual Basic Editor).

We’ll start where almost all examples for class modules start; with an employee, the employee is called Fred Smith (original, I know), works in accounts, is 25 and earns £22k.

To start, add a new class module to your VBA project.  For this example change the name from Class1 to cls_Employee.  Do this by editing the (Name) item in the ‘Properties’ list for the class module.

You then need to create the variables which are going to be used within the class module, since they are declared Private, they are not available outside of the class module.

Option Explicit

Private strName As String
Private strDept As String
Private lngAge As Long
Private dblSalary As Double

Now we need to add the property procedures which allow us to write to and read from the above variables.  To do this we need Property Get and Property Let functions.  (Property Set is used for objects).

' Name property
Public Property Get Name() As String
    Name = strName
End Property
Public Property Let Name(value As String)
    strName = value
End Property

' Department property
Public Property Get Dept() As String
    Dept = strDept
End Property
Public Property Let Dept(value As String)
    strDept = value
End Property

' Age property
Public Property Get Age() As Long
    Age = lngAge
End Property
Public Property Let Age(value As Long)
    lngAge = value
End Property

' Salary property
Public Property Get Salary() As String
    Salary = dblSalary
End Property
Public Property Let Salary(value As String)
    dblSalary = value
End Property

The Let procedure is used to assign values to the class. The get procedure is used to get the values from the class.  Please note that the data types must be the same for the ‘value’ be passed through and the Get procedure.

A property can be made read-only simply by omitting the Let procedure. For example, a read-only property might be tax, which is calculated when it is called. E.g.,

'Tax property
Public Property Get Tax() As Double
    Tax = Calculate value
End Property

Class modules can also contain sub routines and functions, such as the PrintEmployeeData below.

' Print employee data routine
Public Sub PrintEmployeeData()
    ' You code appears here
End Sub

Now that we have the class module ready we can create objects based on the class module.  Add a standard code module to your project.  Within a new sub routine declare a new variable as type cls_Employee like the example below.

Option Explicit

Sub AddEmployee()
    Dim clsEmp As cls_Employee

End Sub

ClsEmp has to be set as a new instance of the the class before we can assign values to it’s properties, like below.

    Set clsEmp = New cls_Employee
    clsEmp.Name = "Fred Smith"
    clsEmp.Dept = "Accounts"
    clsEmp.Age = 22 
    clsEmp.Salary = 25000

You can retrieve the data value from the class using code similar to the below example.

    MsgBox "Employee Name: " & clsEmp.Name;

I hope this has helped with your understanding of class modules within VBA.  The above example code will work in Excel and Access.

I would like to add that this blog post and code examples, although my own, is based a on the article from which I learned about class modules.  That article can be found at Chip Pearson’s website: http://www.cpearson.com/. 

In my next blog post I will cover how to store multiple objects (employees) in a collection.