In This Chapter
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:
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.
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.
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.
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.
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:
MsgBox clsNumLock.Value
In addition, your code can toggle the Num Lock key by using the Toggle method:
clsNumLock.Toggle
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.
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).
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
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.
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.
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.
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
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
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.
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:
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:
Type the following code in the class module:
Private WithEvents qt As QueryTable Public Property Get QTable() As QueryTable Set QTable = qt End Property Public Property Set QTable(rQTable As QueryTable) Set qt = rQTable 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.
Type the following code into the event procedure:
Private Sub qt_AfterRefresh(ByVal Success As Boolean) If Success Then Me.QTable.Parent.Range("A1").Value = _ "Last updated:" & Format(Now,"mm-dd-yyyy hh:mm:ss") End If 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.
Type the following code into the module:
Public clsQueryEvents As CQueryEvents Sub Auto_Open() Set clsQueryEvents = New CQueryEvents Set clsQueryEvents.QTable = Sheet1.QueryTables(1) 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.
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.
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.
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.
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.
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.
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.
18.118.19.207