Excel 2016 Power Programming with VBA (2016)
Part IV. Developing Excel Applications
Chapter 20. Leveraging Class Modules
In This Chapter
· Introducing class modules
· Exploring some typical uses for class modules
· Seeing examples that demonstrate some key concepts related to class modules
What Is a Class Module?
For many VBA programmers, the concept of a class module is a mystery. This feature can be confusing, but the examples in this chapter may help to make it less mysterious.
A class module is a special type of VBA module that you can insert in a VBA project. Basically, a class module enables the programmer (you) to create a new object. As you should know by now, programming Excel really boils down to manipulating objects. A class module allows you to create new objects, along with corresponding properties, methods, and events.
At this point, you might be asking, “Do I really need to create new objects?” The answer is no. You don’t need to, but you might want to after you understand some of the benefits of doing so. In many cases, a class module simply serves as a substitute for functions or procedures, but it could be a more convenient and manageable alternative. In other cases, however, you’ll find that a class module is the only way to accomplish a particular task.
Following is a list of some typical uses for class modules:
· To encapsulate code and improve readability: By moving all your code related to payroll, for example, into custom objects representing employees and paychecks, you can keep your code more organized.
· To handle events of objects not exposed by Excel: such as application events, chart events, or query table events. Chapter 15 shows an example of using application events.
· To encapsulate a Windows Application Programming Interface (API) function to make it easier to use in your code: For example, you can create a class that makes it easy to detect or set the state of the Num Lock or Caps Lock key. Or you can create a class that simplifies access to the Windows Registry.
· To enable multiple objects in a UserForm to execute a single procedure: Normally, each object has its own event handler. The example in Chapter 15 demonstrates how to use a class module so that multiple CommandButtons have a single Click event-handler procedure.
· To create reusable components that can be imported into other projects: After you create a general-purpose class module, you can import it into other projects to reduce your development time.
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.
Classes and objects
The terms class and object are used interchangeably by many VBA developers. They are very closely related, but there is a minor distinction. A class module defines an object, but it’s not the actual object.
Think of a class module as a blueprint for a house. The blueprint describes all the properties and dimensions of the house, but it’s not a house. You can create a bunch of houses from one blueprint. Similarly, you can create a bunch of objects from one class.
Objects, properties, and methods
It’s helpful to think of objects, properties, and methods in terms of grammar. The objects are the nouns. They are things. They may represent tangible things like an employee, a customer, or a dump truck. They may also represent intangible things like a transaction. When you’re designing your application using a class module, start by identifying the objects that live in your domain.
Objects have properties. Properties are the adjectives in the grammar analogy. They describe the characteristics of an object. One characteristic of a house is how many cars fit in the garage. If you create a house class, you might also create a GarageCarCount property. Similarly, you might create an ExteriorColor property that holds the color of the paint used on the outside of the house. You don’t have to create a property for every conceivable characteristic of an object. You only create properties for characteristics that are important to your application. Excel has a Font object that has a Size property. You can read this property to find out what the font size is, or you can set this property to change the font size.
Finally, methods are the verbs of the class grammar. Methods describe actions that the class module takes. In general, there are two types of methods: methods that change more than one property at a time and methods that interact with the outside world. Excel’s Workbook object has a Name property. You can read the Name property, but you can’t change it. In order to change the Name property, you have to use a method (like Save or SaveAs) because the outside world, namely the operating system, cares what the name of the workbook is.
Creating a NumLock Class
One of the benefits of class modules is to give complicated, hard-to-use code (like Windows APIs) a better interface. Detecting or changing the state of the Num Lock key requires a couple of Windows API functions and is fairly complicated. You can put the API functions into a class module and build your own properties and methods that are far easier to use than the API functions.
In this section, I provide step-by-step instructions for creating a useful, albeit simple, class module. This class module creates a NumLock class that has one property (Value) and one method (Toggle).
After the class is created, your VBA code can determine the current state of the Num Lock key by using an instruction such as the following, which displays the Value property:
In addition, your code can toggle the Num Lock key by using the Toggle method:
The class is designed so that you can’t simply set the Value property. The Value property isn’t just a value that you’re storing in a class, but the actual state of the keyboard. To change the Value property, you define a method that interacts with the keyboard via the Windows API and that changes the property value. It’s important to understand that a class module contains the code that defines the object, including its properties and methods. You can then create an instance of this object in your VBA general code modules and manipulate its properties and methods.
To better understand the process of creating a class module, you might want to follow the instructions in the next sections. Start with an empty workbook.
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).
Figure 20.1 An empty class module named CNumLock.
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
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
Value = CBool(Keys(VK_NUMLOCK))
The details of Property procedures are described later in this chapter, in the “Programming properties of objects” section.
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:
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
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:
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
MsgBox"Num Lock was changed from" & _
OldValue &" to" & clsNumLock.Value
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.
Figure 20.2 A message box shows the change in status of the Num Lock key.
On the Web
The completed class module for this example is available on the book’s website. The workbook, named keyboard class.xlsm, also contains a class module to detect and set the state of the Caps Lock key and the Scroll Lock key.
Coding Properties, Methods, and Events
The example in the preceding section demonstrates how to create a new object class with a single read-only property named Value and a single method named Toggle. An object class can contain any number of properties, methods, and events.
The name that you use for the class module in which you define the object class is also the name of the object class. By default, class modules are named Class1, Class2, and so on. Usually, you’ll want to provide a more meaningful name for your object class.
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:
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)
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:\data\myfile.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
Property Let SaveAsExcelFile(bVal As Boolean)
XLFile = bVal
Use Property Set in place of Property Let when the property is an object data type.
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:
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.
Normal procedure-naming rules apply to property procedures, and you’ll find that VBA won’t let you use some names if they are reserved words. If you get a syntax error when creating a property procedure, try changing the name of the procedure.
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:
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.
If XLFile Then
ActiveWorkbook.SaveAs FileName:=FName, _
ActiveWorkbook.SaveAs FileName:=FName, _
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
Private Sub Class_Terminate()
' Termination code goes here
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
Exposing a QueryTable Event
Excel automatically creates class modules for some objects, like ThisWorkbook and Sheet1. These class modules expose events like Workbook_SheetActivate and Worksheet_SelectionChange. Other objects in the Excel object model have events, but you have to create a custom class module to expose them. In this section, I’ll show you how to expose the events of a QueryTable object.
Figure 20.3 shows a worksheet with a web query that starts in cell A5. The web query pulls financial information from a website. The only thing that’s missing is the date when this web query was last updated so you can know whether the prices are current.
Figure 20.3 A web query for financial information.
In VBA, a web query is a QueryTable object. The QueryTable object has two events: BeforeRefresh and AfterRefresh. Those events are pretty well named, and you’ve probably already figured out when they fire.
To be able to use the QueryTable events, you need to:
· Create a custom class module.
· Declare a QueryTable using the WithEvents keyword.
· Write the event procedure code.
· Create a Public variable to keep the object in scope.
· Create a procedure to instantiate the class.
Those are the basic steps for exposing events of any object that supports events. (Not all of them do.) When you use the WithEvents keyword, VBA will only let you declare objects that support events. Follow these steps to add a message to a worksheet informing the user when a web query was last updated:
1. In the VBE, choose Insert ➜ Class Module to insert a new class module.
2. Press F4 to go to the Properties dialog and name the module CQueryEvents.
3. Type the following code in the class module:
4. Private WithEvents qt As QueryTable
6. Public Property Get QTable() As QueryTable
7. Set QTable = qt
8. End Property
10. Public Property Set QTable(rQTable As QueryTable)
11. Set qt = rQTable
12. End Property
The first line declares a module-level variable that will store the web query. You can see that it was declared with the WithEvents keyword. Next, Property Get and Property Set procedures are written so we can set the variable from outside of the class.
13.From the drop-downs at the top of the code pane (see Figure 20.4), select qt and AfterRefresh. This will insert the Sub and End Sub statements for the event module.
14. Type the following code into the event procedure:
15. Private Sub qt_AfterRefresh(ByVal Success As Boolean)
16. If Success Then
17. Me.QTable.Parent.Range("A1").Value = _
18. "Last updated:" & Format(Now,"mm-dd-yyyy hh:mm:ss")
19. End If
20. End Sub
The event procedure has a built-in argument, Success, that is True if the query updated without errors. Now that the class is set up, you need to create an object based on it.
21.Insert a standard module (Insert ➜ Module). You can accept the default name of Module1 for this exercise or change it if you wish.
22. Type the following code into the module:
23. Public clsQueryEvents As CQueryEvents
25. Sub Auto_Open()
26. Set clsQueryEvents = New CQueryEvents
27. Set clsQueryEvents.QTable = Sheet1.QueryTables(1)
28. End Sub
A globally scoped variable (declared with the Public keyword) will stay in scope for as long as the worksheet is open. That means the class will continue to “listen” for events until you close the workbook. The Auto_Open procedure runs when the workbook is first opened. It creates the clsQueryEvents object and then sets the events variable to the web query on Sheet1.
29.Run Auto_Open from the Immediate Window or by pressing F5 in the VBE.
That’s it. You now have code that will run after the web query on Sheet1 is refreshed. You can click Refresh All on the Data tab of the Ribbon to test the code. If you followed the steps, you should see something similar to Figure 20.5.
Figure 20.4 The code pane lists available events.
Figure 20.5 After a web query is refreshed, the last update time is recorded.
On the Web
A workbook named query table events.xlsm is available on this book’s website. It contains the web query used in the example in this section. Another workbook, named query table events complete.xlsm, contains the web query and the completed code.
Creating a Class to Hold Classes
One of the benefits of using class modules is to organize your code according to the objects the code affects. You may, for instance, create a CEmployee class for your code that deals with employees. But you probably don’t have just one employee. Often, you create many objects from one class, and a great way to keep track of them is within another class.
In this section, you’ll learn how to create parent classes and child classes in a commission calculating application. You’ll create a CSalesRep child class and keep track of all instances of it in a CSalesReps class. (Naming the parent class as the plural of the child class is a common convention.) Similarly, you’ll create a CInvoices parent class to hold CInvoice objects.
On the Web
A workbook with all the data and code for this section is available on this book’s website. The workbook is named commission calc.xlsm.
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
Public Property Get SalesRepID() As Long
SalesRepID = mSalesRepID
Public Property Let SalesRep(ByVal sSalesRep As String)
mSalesRep = sSalesRep
Public Property Get SalesRep() As String
SalesRep = mSalesRep
Public Property Let CommissionRate( _
ByVal dCommissionRate As Double)
mCommissionRate = dCommissionRate
Public Property Get CommissionRate() As Double
CommissionRate = mCommissionRate
Public Property Let Threshold(ByVal dThreshold As Double)
mThreshold = dThreshold
Public Property Get Threshold() As Double
Threshold = mThreshold
Figure 20.6 Excel tables hold the information for the objects.
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)
Public Property Get Count() As Long
Count = mSalesReps.Count
Public Property Get Item(lId As Long) As CSalesRep
Set Item = mSalesReps(lId)
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
Public Property Get Invoice() As String
Invoice = mInvoice
Public Property Let InvoiceDate(ByVal dtInvoiceDate As Date)
mInvoiceDate = dtInvoiceDate
Public Property Get InvoiceDate() As Date
InvoiceDate = mInvoiceDate
Public Property Let Amount(ByVal dAmount As Double)
mAmount = dAmount
Public Property Get Amount() As Double
Amount = mAmount
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
Public Property Get Count() As Long
Count = mInvoices.Count
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
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
clsSalesRep.SalesRepID = .Cells(1).Value
clsSalesRep.SalesRep = .Cells(2).Value
clsSalesRep.CommissionRate = .Cells(3).Value
clsSalesRep.Threshold = .Cells(4).Value
'Add the child to the parent class
'Fill invoices for this rep
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
'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
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
'Loop through all the reps and print commissions
For i = 1 To clsSalesReps.Count
Debug.Print .SalesRep, _
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
Commission = (Me.Invoices.Total - Me.Threshhold) _
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 fromCInvoices. 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
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.
Figure 20.7 The commission calculation is output to the Immediate Window.