Classes In VBA

Classes In VBA

Class Basics

For illustration, let's adapt the Employee Type described above into a class. First, insert a class module into your VBProject (from the Insert menu in the VBA editor). Name the class CEmployee (it is common practice to use a 'C' as the first letter of a class). There are three properties to create: Name, Address, and Salary. These values will be stored in private variables within the class. Since they are declared Private, they cannot be accessed outside the class module.

Private pName As String
Private pAddress As String
Private pSalary As Double

Next, we need to declare Property procedures to allow these variables to be read from and written to. This is done with Property Get and Property Let functions (or Property Set for object type variables).

''''''''''''''''''''''
' 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

The Get procedure is used to return a value out of the class, and the Let procedure is to put a value into the class. Note that the return data type of the Get property procedure must be the same data type as the (last) parameter to the Let property procedure. Otherwise, you'll get a compiler error.

Because Property procedures can contain any code you like, the Let Salary procedure can be written to exclude non-positive values.

Public Property Let Salary(Value As Double)
If Value > 0 Then
pSalary = Value
Else
' appropriate error code here
End If
End Property

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

Property Get WithholdingTax() As Double
WithholdingTax = calculated value
End Property

Finally, the class can contain methods, such as a PrintPaycheck procedure.

Public Sub PrintPaycheck()
' actual code to print check
End Sub

Now that we have defined the class, we can create objects based on the class. In a standard code module, declare a variable of type CEmployee.

Dim Emp As CEmployee

Then, Set that variable to a new instance of the class and assign some property values.

Set Emp = New CEmployee
Emp.Name = "Joe Smith"
Emp.Address = "123 Main Street"
Emp.Salary = 40000