Creating Your Own Objects

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