Automating Microsoft Access with VBA

In addition to using objects from existing libraries, VBA enables you to build your own classes and objects. This section shows you how to build a simple class that represents a weekly timecard summary. To get started, select Insert, Class Module. In the VBA window, an empty class module looks just like an empty regular module. But the Properties window shows that it's a class module, and you'll see a different icon in the Project window. Use the Properties window to name the class TimeWeek.

Now enter this code to implement the class:

Option Compare Database Option Explicit ' Expose some simple properties Public MondayHours As Integer Public TuesdayHours As Integer Public WednesdayHours As Integer Public ThursdayHours As Integer Public FridayHours As Integer Public SaturdayHours As Integer Public SundayHours As Integer ' Expose a property backed with a private variable Private strEmployeeName As String Public Property Let EmployeeName(NewName As String) strEmployeeName = NewName End Property Public Property Get EmployeeName() As String EmployeeName = strEmployeeName End Property ' Calculate three read-only properties Public Property Get TotalHours() As Integer TotalHours = MondayHours + TuesdayHours + _ WednesdayHours + ThursdayHours + FridayHours + _ SaturdayHours + SundayHours End Property Public Property Get RegularHours() As Integer If TotalHours > 40 Then RegularHours = TotalHours Else RegularHours = 40 End If End Property Public Property Get OvertimeHours() As Integer If TotalHours > 40 Then OvertimeHours = TotalHours - 40 Else OvertimeHours = 0 End If End Property ' Implement a simple method Public Function PrintTimeReport() Debug.Print "Monday " & MondayHours Debug.Print "Tuesday " & TuesdayHours Debug.Print "Wednesday " & WednesdayHours Debug.Print "Thursday " & ThursdayHours Debug.Print "Friday " & FridayHours Debug.Print "Saturday " & SaturdayHours Debug.Print "Sunday " & SundayHours End Function

This code demonstrates two ways to implement properties, and one way to implement a method. The easy way to create a property for a class is to simply give the class a public variable; all such variables are visible as properties of the class.

A more complex way to create a property is to write property procedures. The EmployeeName property is implemented using a Property Let procedure and a Property Get procedure. When someone sets a value for the property, the Property Let procedure is called with the new value. When someone reads the value of the property, the Property Get procedure is called to return the value. In the case of the EmployeeName property, the value is simply stored in an internal private variable; in this case, the property procedures behave exactly the same as a public string variable would.

The other three properties (TotalHours, RegularHours, and OvertimeHours) are all implemented using Property Get procedures without corresponding Property Let procedures. This makes them read-only properties, because there's no way for outside code to set these values.

PrintTimeReport is a method of the class. Any public Function procedure or public Sub procedure becomes a method of the class.

After saving this code, you can use the TimeWeek class just like any other class. Here's an example of calling the TimeWeek class:

Sub UseTimeWeek() ' Demonstrate the TimeWeek class Dim tw As New TimeWeek tw.MondayHours = 8 tw.TuesdayHours = 9 tw.WednesdayHours = 8.5 tw.ThursdayHours = 8 tw.FridayHours = 7.5 tw.SaturdayHours = 4 tw.SundayHours = 3.5 Debug.Print "Regular: " & tw.RegularHours Debug.Print "Overtime: " & tw.OvertimeHours Debug.Print "Total: " & tw.TotalHours Debug.Print "--------------------" tw.PrintTimeReport End Sub

And here's the output that the UseTimeWeek procedure produces in the Immediate window:

Regular: 49 Overtime: 9 Total: 49 -------------------- Monday 8 Tuesday 9 Wednesday 8 Thursday 8 Friday 8 Saturday 4 Sunday 4