Built-in class modules

If you’ve been following the examples in this book so far, then you’ve already used a class module. Excel automatically creates a class module for the Workbook object, each Worksheet object, and any Userform objects. That’s right: The ThisWorkbook module is just a class module. And when you insert a Userform into your project, you’re inserting a class module.

The difference between a Userform’s class module and a custom class module is that the Userform has a user-interface component (the form and its controls) that a custom class module doesn’t have. However, you can create properties and methods in a Userform’s class module to extend its functionality because it’s just a class module.

Custom class modules

The remainder of this chapter deals with creating custom class modules. Unlike built-in class modules where Excel defines the object and its properties and methods, custom class modules allow you to define them. What custom objects you create depends on your application. If you’re writing a contact manager application, you might have a Company class and a Contact class. For a sales commission calculator, you might have a Salesperson class and an Invoice class. One of the benefits of class modules is that you can design them to fit your specific needs perfectly.

Inserting a class module

Activate Visual Basic Editor (VBE) and choose Insert ➜ Class Module. This step adds an empty class module named Class1. If the Properties window isn’t displayed, press F4 to display it. Then change the name of the class module to CNumLock (see Figure 20.1).

Adding VBA code to the class module

In the next step, you create the code for the Value property. To detect or change the state of the Num Lock key, the class module needs the Windows API declarations that detect and set the Num Lock key. That code follows.

Private Declare Sub keybd_event Lib"user32" _
    (ByVal bVk As Byte, _
    ByVal bScan As Byte, _
    ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Private Declare PtrSafe Function GetKeyboardState Lib"user32" _
    (pbKeyState As Byte) As Long
 
Private Declare PtrSafe Function SetKeyboardState Lib"user32" _
    (lppbKeyState As Byte) As Long
 
'Constant declarations
Const VK_NUMLOCK = &H90

Next, you need a procedure that retrieves the current state of the Num Lock key. I called this the Value property of the object, but you can use any name for the property. To retrieve the state, insert the following Property Get procedure:

Public Property Get Value() As Boolean
'   Get the current state
    Dim Keys(0 To 255) As Byte
    GetKeyboardState Keys(0)
    Value = CBool(Keys(VK_NUMLOCK))
End Property

This procedure, which uses the GetKeyboardState Windows API function to determine the current state of the Num Lock key, is called whenever VBA code reads the Value property of the object. For example, after the object is created, a VBA statement such as this executes the Property Get procedure:

MsgBox clsNumLock.Value

If the Value property were read/write, you would need a Property Let procedure to go with your Property Get. Since we’re setting the Value property via the Toggle method, there is no Property procedure.

Next, you need a procedure to toggle the NumLock state. I called this procedure the Toggle method.

Public Sub Toggle()
'   Toggles the state
'   Simulate Key Press
    keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
'   Simulate Key Release
    keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY _
        Or KEYEVENTF_KEYUP, 0
End Sub

Note that Toggle is a standard Sub procedure (not a Property Let or Property Get procedure). A VBA statement such as the following one toggles the state of the clsNumLock object by executing the Toggle procedure:

clsNumLock.Toggle

Using the CNumLock class

Before you can use the CNumLock class, you must create an instance of the object. The following statement, which resides in a regular VBA module (not the class module), does just that:

Dim clsNumLock As CNumLock

Note that the object type is CNumLock (that is, the name of the class module). The object variable can have any name, but I use the convention of prefixing class modules with a capital C and prefixing the object variables that are derived from those class modules with cls. So the CNumLock class is instantiated as the clsNumLock object variable. The following procedure reads the Value property of the clsNumLock object, toggles the value, reads the value again, and displays a message to the user describing what just happened.

Public Sub NumLockTest()
    Dim clsNumLock As CNumLock
    Dim OldValue As Boolean
 
    Set clsNumLock = New CNumLock
    OldValue = clsNumLock.Value
    clsNumLock.Toggle
    DoEvents
    MsgBox"Num Lock was changed from" & _
        OldValue &" to" & clsNumLock.Value
End Sub

Figure 20.2 shows the result of running NumLockTest. Using the NumLock class is much simpler than dealing directly with the API functions. After you create a class module, you can reuse it in any other project simply by importing the class module.

Programming properties of objects

Most objects have at least one property, and you can give them as many as you need. After a property is defined and the object is created, you can use it in your code using the standard dot syntax:

object.property

The VBE Auto List Members option works with objects defined in a class module, which makes it easier to select properties or methods when writing code.

Properties for the object that you define can be read-only, write-only, or read/write. You define a read-only property with a single procedure — using the Property Get keyword. Here’s an example of a Property Get procedure:

Public Property Get FileNameOnly() As String
    Dim Sep As String, LastSep As Long
    Sep = Application.PathSeparator
    LastSep = InStrRev(FullName, Sep)
    FileNameOnly = Right(FullName, Len(FullName) - LastSep)
End Property

You may have noticed that a Property Get procedure works like a Function procedure. The code performs calculations and then returns a property value that corresponds to the procedure’s name. In this example, the procedure’s name is FileNameOnly. The property value returned is the filename part of a path string (contained in a Public variable named FullName). For example, if FullName is c:datamyfile.txt, the procedure returns a property value of myfile.txt. The FileNameOnly procedure is called when VBA code references the object and property.

For read/write properties, you create two procedures: a Property Get procedure (which reads a property value) and a Property Let procedure (which writes a property value). The value being assigned to the property is treated as the final argument (or the only argument) of a Property Get procedure.

Two example procedures follow:

Dim XLFile As Boolean
 
Property Get SaveAsExcelFile() As Boolean
    SaveAsExcelFile = XLFile
End Property
 
Property Let SaveAsExcelFile(bVal As Boolean)
   XLFile = bVal
End Property

A Public variable in a class module can also be used as a property of the object. In the preceding example, the Property Get and Property Let procedures could be eliminated and replaced with this module-level declaration:

Public SaveAsExcelFile As Boolean

In the unlikely event that you need to create a write-only property, you create a single Property Let procedure with no corresponding Property Get procedure.

The previous examples use a Boolean module-level variable named XLFile. The Property Get procedure simply returns the value of this variable as the property value. If the object were named FileSys, for example, the following statement would display the current value of the SaveAsExcelFile property:

MsgBox FileSys.SaveAsExcelFile

The Property Let statement, on the other hand, accepts an argument and uses the argument to change the value of a property. For example, you could write a statement such as the following to set the SaveAsExcelFile property to True:

FileSys.SaveAsExcelFile = True

In this case, the value True is passed to the Property Let statement, thus changing the property’s value.

You’ll need to create a variable that represents the value for each property that you define within your class module.

Programming methods for objects

A method for an object class is programmed by using a standard Sub or Function procedure placed in the class module. An object might or might not use methods. Your code executes a method by using standard notation:

object.method

Like any other VBA method, a method that you write for an object class will perform some type of action. The following procedure is an example of a method that saves a workbook in one of two file formats, depending on the value of the XLFile variable. As you can see, nothing about this procedure is special.

Sub SaveFile()
    If XLFile Then
        ActiveWorkbook.SaveAs FileName:=FName, _
          FileFormat:=xlWorkbookNormal
    Else
        ActiveWorkbook.SaveAs FileName:=FName, _
          FileFormat:=xlCSV
    End If
End Sub

Class module events

Every class module has two events: Initialize and Terminate. The Initialize event occurs when a new instance of the object is created; the Terminate event occurs when the object is destroyed. You might want to use the Initialize event to set default property values.

The frameworks for these event-handler procedures are as follows:

Private Sub Class_Initialize()
'    Initialization code goes here
End Sub
 
Private Sub Class_Terminate()
'    Termination code goes here
End Sub

An object is destroyed (and the memory it uses is freed) when the procedure or module in which it is declared finishes executing. You can destroy an object at any time by setting it to Nothing. The following statement, for example, destroys the object named MyObject:

Set MyObject = Nothing

Creating the CSalesRep and CSalesReps classes

Figure 20.6 shows two tables. The first lists all the sales representatives and some commission information. The second table is a list of invoices. Start by creating a CSalesRep class module and include the following code:

Private mSalesRepID As Long
Private mSalesRep As String
Private mCommissionRate As Double
Private mThreshold As Double
 
Public Property Let SalesRepID(ByVal lSalesRepID As Long)
    mSalesRepID = lSalesRepID
End Property
Public Property Get SalesRepID() As Long
    SalesRepID = mSalesRepID
End Property
Public Property Let SalesRep(ByVal sSalesRep As String)
    mSalesRep = sSalesRep
End Property
Public Property Get SalesRep() As String
    SalesRep = mSalesRep
End Property
Public Property Let CommissionRate( _
    ByVal dCommissionRate As Double)
    mCommissionRate = dCommissionRate
End Property
Public Property Get CommissionRate() As Double
    CommissionRate = mCommissionRate
End Property
Public Property Let Threshold(ByVal dThreshold As Double)
    mThreshold = dThreshold
End Property
Public Property Get Threshold() As Double
    Threshold = mThreshold
End Property

You’ll notice that there is a private variable for every column in the sales rep table and a Property Get and Property Let statement for every variable. Next, add another class module named CSalesReps. This will be the parent class that holds all of the CSalesRep objects.

In the parent class, create a Collection variable that will hold all the children.

Private mSalesReps As New Collection

Now you need to add a way to get the children into the collection. Create an Add method, an Item property, and a Count property in the CSalesReps class module using this following code:

Public Sub Add(clsSalesRep As CSalesRep)
    mSalesReps.Add clsSalesRep, CStr(clsSalesRep.SalesRepID)
End Sub
 
Public Property Get Count() As Long
    Count = mSalesReps.Count
End Property
 
Public Property Get Item(lId As Long) As CSalesRep
    Set Item = mSalesReps(lId)
End Property

You might notice that all you’ve done is mimic the Collection object’s Add method and Item and Count properties. The Collection object’s key argument must be a unique string, so you used the SalesRepID property and the Cstr() function to ensure the key is unique and a string.

That’s all there is to creating a parent class. Simply add a Collection variable and mimic whichever of the Collection’s properties and methods you need.

Creating the CInvoice and CInvoices classes

Following is the code for the CInvoice class:

Private mInvoice As String
Private mInvoiceDate As Date
Private mAmount As Double
 
Public Property Let Invoice(ByVal sInvoice As String)
    mInvoice = sInvoice
End Property
Public Property Get Invoice() As String
    Invoice = mInvoice
End Property
Public Property Let InvoiceDate(ByVal dtInvoiceDate As Date)
    mInvoiceDate = dtInvoiceDate
End Property
Public Property Get InvoiceDate() As Date
    InvoiceDate = mInvoiceDate
End Property
Public Property Let Amount(ByVal dAmount As Double)
    mAmount = dAmount
End Property
Public Property Get Amount() As Double
    Amount = mAmount
End Property

I won’t go into as much detail on CInvoice because, like CSalesRep, it simply creates a property for every column in the table. But it doesn’t create one for the SalesRepID column, and you’ll see why later in this section. The following code is in the CInvoices class module:

Private mInvoices As New Collection
 
Public Sub Add(clsInvoice As CInvoice)
    mInvoices.Add clsInvoice, clsInvoice.Invoice
End Sub
 
Public Property Get Count() As Long
    Count = mInvoices.Count
End Property

Like CSalesReps, this class has a Collection, an Add method, and a Count property. It doesn’t have an Item property because that’s not currently needed. But you can add an Item property later if the application requires it. Now you have two parent classes and two child classes. The final step before you can start creating objects is to define the relationship between them. In CSalesRep, include the following code:

Private mInvoices As New CInvoices
Public Property Get Invoices() As CInvoices
    Set Invoices = mInvoices
End Property

Now the hierarchy is CSalesReps > CSalesRep > CInvoices > CInvoice.

Filling the parent classes with objects

With your classes defined, you can create new CSalesRep and CInvoice objects and add them to their respective parent classes. The following two procedures do just that.

Public Sub FillSalesReps(ByRef clsSalesReps As CSalesReps)
    Dim i As Long
    Dim clsSalesRep As CSalesRep
    Dim loReps As ListObject
 
    Set loReps = Sheet1.ListObjects(1)
    'loop through all the sales reps
    For i = 1 To loReps.ListRows.Count
        'create a new sales rep object
        Set clsSalesRep = New CSalesRep
        'Set the properties
        With loReps.ListRows(i).Range
            clsSalesRep.SalesRepID = .Cells(1).Value
            clsSalesRep.SalesRep = .Cells(2).Value
            clsSalesRep.CommissionRate = .Cells(3).Value
            clsSalesRep.Threshold = .Cells(4).Value
        End With
        'Add the child to the parent class
        clsSalesReps.Add clsSalesRep
        'Fill invoices for this rep
        FillInvoices clsSalesRep
    Next i
End Sub
 
Public Sub FillInvoices(ByRef clsSalesRep As CSalesRep)
    Dim i As Long
    Dim clsInvoice As CInvoice
    Dim loInv As ListObject
 
    'create a variable for the table
    Set loInv = Sheet2.ListObjects(1)
    'loop through the invoices table
    For i = 1 To loInv.ListRows.Count
        With loInv.ListRows(i).Range
            'Only if it's for this rep, add it
            If .Cells(4).Value = clsSalesRep.SalesRepID Then
                Set clsInvoice = New CInvoice
                clsInvoice.Invoice = .Cells(1).Value
                clsInvoice.InvoiceDate = .Cells(2).Value
                clsInvoice.Amount = .Cells(3).Value
 
                clsSalesRep.Invoices.Add clsInvoice
            End If
        End With
    Next i
End Sub

The first procedure accepts a CSalesReps argument. This is the class at the top of the hierarchy. The procedure loops through all the rows in the sales rep table, creates a new CSalesRep object, sets the properties of the new object, and adds it to the parent class.

Inside the loop, the FillSalesReps procedure calls FillInvoices and passes it a CSalesRep object. Only those invoices that relate to the CSaleRep object are created and added to it. There isn’t just one CInvoices parent class like there’s only one CSalesReps class. Instead, each CSalesRep has its own CInvoices instance that holds the invoices that relate to it. This relationship of using a parent class like CInvoices acting as a child to another class is a complicated but powerful coding technique.

Calculating the commissons

Insert a new standard module and type the following code to calculate the commission and output the results.

Public Sub CalculateCommission()
    Dim clsSalesReps As CSalesReps
    Dim i As Long
    'Create a new parent object and fill it with child objects
    Set clsSalesReps = New CSalesReps
    FillSalesReps clsSalesReps
 
    'Loop through all the reps and print commissions
    For i = 1 To clsSalesReps.Count
        With clsSalesReps.Item(i)
            Debug.Print .SalesRep, _
               Format(.Commission,"$#,##0.00")
        End With
    Next i
End Sub

You may have noticed that the above procedure uses a Commisson property that has not yet been created. In the CSalesRep class, insert the following code to create a Commission property.

Public Property Get Commission() As Double
    If Me.Invoices.Total < Me.Threshhold Then
        Commission = 0
    Else
        Commission = (Me.Invoices.Total - Me.Threshhold) _
            * Me.CommissionRate
    End If
End Property

If the total of all the invoices is less than the threshold, this procedure sets the commission to zero. Otherwise, the total sales in excess of the threshold is multiplied by the commission rate. To get the total of the invoices, this property uses a Total property from CInvoices. Since you haven’t created that property yet, insert the following code into CInvoices to do so.

Public Property Get Total() As Double
    Dim i As Long
    For i = 1 To mInvoices.Count
        Total = Total + mInvoices.Item(i).Amount
    Next i
End Property

Figure 20.7 shows the output in the Immediate Window from running CalculateCommissions. You probably noticed that using the class module requires a little more setup than writing normal procedures. And for an application as simple as this, it may not be worth the effort. But as your applications get more complicated, you’ll find that organizing your code in class modules will make it more readable, easier to maintain, and easier to modify should the need arise.