Excel already has many objects available, but there are times when a custom object would be better suited for the job at hand. You can create custom objects that you use in the same way as Excel’s built-in objects. These special objects are created in class modules.
Class modules are used to create custom objects with custom properties and methods. They can trap application events, embedded chart events, ActiveX control events, and more.
From the VB Editor, select Insert, Class Module. A new module, Class1, is added to the VBAProject workbook and can be seen in the Project Explorer window (see Figure 22.1). Two things to keep in mind concerning class modules:
Each custom object must have its own module. (Event trapping can share a module.)
The class module should be renamed to reflect the custom object.
Chapter 9, “Event Programming,” showed you how certain actions in workbooks, worksheets, and nonembedded charts could be trapped and used to activate code. Briefly, it reviewed how to set up a class module to trap application and chart events. The following goes into more detail about what was shown in that chapter.
The Workbook_BeforePrint
event is triggered when the workbook in which it resides is printed. If you want to run the same code in every workbook available, you have to copy the code to each workbook. Or, you could use an application event, Workbook_BeforePrint
, which is triggered when any workbook is printed.
The application events already exist, but a class module must be set up first so that they can be seen. To create a class module, follow these steps:
Insert a class module into the project. Rename it to something that will make sense to you, such as clsAppEvents
. Choose View, Properties Window to rename a module.
Enter the following into the class module:
Public WithEvents xlApp As Application
The name of the variable, xlApp
, can be any variable name. The WithEvents
keyword exposes the events associated with the Application object.
xlApp
is now available from that class module’s Object drop-down list. Select it from the drop-down, and then click the Procedure drop-down menu to the right of it to view the list of events that is available for the xlApp
’s object type (Application), as shown in Figure 22.2.
→ | For a review of the various Application Events, see“Application-Level Events” p. 171, in Chapter 9,“Event Programming.” |
Any of the events listed can by captured, just as workbook and worksheet events were captured in an earlier chapter. The following example uses the NewWorkbook
event to automatically set up footer information. This code is placed in the class module, below the xlApp
declaration line you just added:
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) Dim wks As Worksheet With Wb For Each wks In .Worksheets wks.PageSetup.LeftFooter = "Created by: " & .Application.UserName wks.PageSetup.RightFooter = Now Next wks End With End Sub
The procedure placed in a class module does not run automatically as events in workbook or worksheet modules would. An instance of the class module must be created and the Application object assigned to the xlApp
property. After that is complete, the TrapAppEvent
procedure needs to run. As long as the procedure is running, the footer will be created on each sheet every time a new workbook is added. Place the following in a standard module:
Public myAppEvent As New clsAppEvents Sub TrapAppEvent() Set myAppEvent.xlApp = Application End Sub
The application event trapping can be terminated by any action that resets the module level or public variables.This includes editing code in the VB Editor.To restart, run the procedure that creates the object (TrapAppEvent
).
In this example, the public myAppEvent
declaration was placed in a standard module with the TrapAppEvent
procedure. To automate the running of the entire event trapping, all the modules could be transferred to the Personal.xlsb and the procedure transferred to a Workbook_Open
event. In any case, the Public
declaration of myAppEvent
must remain in a standard module so that it can be shared between modules.
Preparing to trap embedded chart events is the same as preparing for trapping application events. Create a class module, insert the public declaration for a chart type, create a procedure for the desired event, and then add a standard module procedure to initiate the trapping. The same class module used for the application event can be used for the embedded chart event.
Place the following line in the declaration section of the class module. The available chart events are now viewable (see Figure 22.3):
Public WithEvents xlChart As Chart
→ | For a review of the various charts events, see“Chart Sheet Events” p. 167, in Chapter 9,“Event Programming.” |
Three events are set up. The primary event, MouseDown
, changes the chart scale with a right-click or double-click. Because these actions also have actions associated with them, you need two more events, BeforeRightClick
and BeforeDoubleClick
, to prevent the usual action from taking place:
This BeforeDoubleClick
event prevents the normal result of a double-click from taking place:
Private Sub xlChart_BeforeDoubleClick(ByVal ElementID As Long, _ ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean) Cancel = True End Sub
This BeforeRightClick
event prevents the normal result of a right-click from taking place:
Private Sub xlChart_BeforeRightClick(Cancel As Boolean) Cancel = True End Sub
Now that the normal actions of the double-click and right-click have been controlled, ChartMouseDown
rewrites what actions are initiated by a right-click and double-click:
Private Sub xlChart_MouseDown(ByVal Button As Long, ByVal Shift As Long, _ ByVal x As Long, ByVal y As Long) If Button = 1 Then xlChart.Axes(xlValue).MaximumScale = _ xlChart.Axes(xlValue).MaximumScale - 50 End If If Button = 2 Then xlChart.Axes(xlValue).MaximumScale = _ xlChart.Axes(xlValue).MaximumScale + 50 End If End Sub
After the events are set in the class module, all that’s left to do is declare the variable in a standard module, as follows:
Public myChartEvent As New clsEvents
Then, create a procedure that will capture the events on the embedded chart:
Sub TrapChartEvent() Set myChartEvent.xlChart = Worksheets("EmbedChart"). _ ChartObjects("Chart 2").Chart End Sub
Class modules are useful for trapping events, but they are also valuable because they can be used to create custom objects. When you are creating a custom object, the class module becomes a template of the object’s properties and methods. To better understand this, we are going to create an employee object to track employee name, ID, hourly wage rate, and hours worked.
Insert a class module and rename it to clsEmployee
. The clsEmployee object has four properties:
EmpName
—. Employee name
EmpID
—. Employee ID
EmpRate
—. Hourly wage rate
EmpWeeklyHrs
—. Hours worked
Properties are variables that can be declared Private
or Public
. These properties need to be accessible to the standard module, so they will be declared Public
. Place the following lines at the top of the class module:
Public EmpName As String Public EmpID As String Public EmpRate As Double Public EmpWeeklyHrs As Double
Methods are actions that the object can take. In the class module, these actions take shape as procedures and functions. The following code creates a method, EmpWeeklyPay()
, for the object that calculates weekly pay:
Public Function EmpWeeklyPay() As Double EmpWeeklyPay = EmpRate * EmpWeeklyHrs End Function
The object is now complete. It has four properties and one method. The next step is using the object in an actual program.
After a custom object is properly configured in a class module, it can be referenced from another module. Declare a variable as the custom object type in the declarations section:
Dim Employee As clsEmployee
In the procedure, Set
the variable to be a New object:
Set Employee = New clsEmployee
Continue entering the rest of the procedure. As you refer to the properties and method of the custom object, a screen tip appears, just as with Excel’s standard objects (see Figure 22.4).
Option Explicit Dim Employee As clsEmployee Sub EmpPay() Set Employee = New clsEmployee With Employee .EmpName = "Tracy Syrstad" .EmpID = "1651" .EmpRate = 25 .EmpWeeklyHrs = 40 MsgBox .EmpName & " earns $" & .EmpWeeklyPay & " per week." End With End Sub
The subprocedure declares an object Employee as a new instance of clsEmployee. It then assigns values to the four properties of the object and generates a message box displaying the employee name and weekly pay (see Figure 22.5). The object’s method, EmpWeeklyPay
, is used to generate the displayed pay.
Public variables, as declared in the earlier example, have read/write properties. When they are used in a program, the values of the variables can be retrieved or changed. To assign read/write limitations, use Property Let
and Property Get
procedures.
Property Let
procedures give you control of how properties can be assigned values. Property Get
procedures give you control of how the properties are accessed. In the custom object example, there is a public variable for weekly hours. This variable is used in a method for calculating pay for the week, but doesn’t take overtime pay into consideration. Variables for normal hours and overtime hours are needed, but the variables must be read-only.
To accomplish this, the class module must be reconstructed. It needs two new properties, EmpNormalHrs
and EmpOverTimeHrs
. Because these two properties are to be confined to read-only, however, they are not declared as variables. Property Get
procedures are used to create them.
If EmpNormalHrs
and EmpOverTimeHrs
are going to be read-only, they must have values assigned somehow. Their values are a calculation of the EmpWeeklyHrs
. Because EmpWeeklyHrs
is going to be used to set the property values of these two object properties, it can no longer be a public variable. There are two private variables. These private variables, NormalHrs
and OverHrs
, are used within the confines of the class module:
Public EmpName As String Public EmpID As String Public EmpRate As Double Private NormalHrs As Double Private OverHrs As Double
A Property Let
procedure is created for EmpWeeklyHrs
to break the hours down into normal and overtime hours:
Property Let EmpWeeklyHrs(Hrs As Double) NormalHrs = WorksheetFunction.Min(40, Hrs) OverHrs = WorksheetFunction.Max(0, Hrs - 40) End Property
The Property Get EmpWeeklyHrs
totals these hours and returns a value to this property. Without it, a value cannot be retrieved from EmpWeeklyHrs
:
Property Get EmpWeeklyHrs() As Double EmpWeeklyHrs = NormalHrs + OverHrs End Property
Property Get
procedures are created for EmpNormalHrs
and EmpOverTimeHrs
to set their values. If you use Property Get
procedures only, the values of these two properties are read-only. They can be assigned values only through the EmpWeeklyHrs
property:
Property Get EmpNormalHrs() As Double EmpNormalHrs = NormalHrs End Property Property Get EmpOverTimeHrs() As Double EmpOverTimeHrs = OverHrs End Property
Finally, the method EmpWeeklyPay
is updated to reflect the changes in the properties and goal:
Public Function EmpWeeklyPay() As Double EmpWeeklyPay = (EmpNormalHrs * EmpRate) + (EmpOverTimeHrs * EmpRate * 1.5) End Function
Update the procedure in the standard module to take advantage of the changes in the class module. Figure 22.6 shows the new message box resulting from this updated procedure:
Sub EmpPayOverTime() Dim Employee As New clsEmployee With Employee .EmpName = "Tracy Syrstad" .EmpID = "1651" .EmpRate = 25 .EmpWeeklyHrs = 45 MsgBox .EmpName & Chr(10) & Chr(9) & _ "Normal Hours: " & .EmpNormalHrs & Chr(10) & Chr(9) & _ "OverTime Hours: " & .EmpOverTimeHrs & Chr(10) & Chr(9) & _ "Weekly Pay : $" & .EmpWeeklyPay End With End Sub
Up to now, you’ve been able to have only one record at a time of the custom object. To create more, a collection is needed. A collection allows more than a single record to exist at a time. For example, Worksheet is a member of the Worksheets collection. You can add, remove, count, and refer to each worksheet in a workbook by item. This functionality is also available to your custom object.
The quickest way to create a collection is to use the built-in Collection
method. By setting up a collection in a standard module, you can access the four default collection methods: Add
, Remove
, Count
, and Item
.
The following example reads a list of employees off a sheet and into an array. It then processes the array, supplying each property of the object with a value, and places each record in the collection:
Sub EmpPayCollection() Dim colEmployees As New Collection Dim recEmployee As New clsEmployee Dim LastRow As Integer, myCount As Integer Dim EmpArray As Variant LastRow = ActiveSheet. Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row EmpArray = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, 4)) For myCount = 1 To UBound(EmpArray) Set recEmployee = New clsEmployee With recEmployee .EmpName = EmpArray(myCount, 1) .EmpID = EmpArray(myCount, 2) .EmpRate = EmpArray(myCount, 3) .EmpWeeklyHrs = EmpArray(myCount, 4) colEmployees.Add recEmployee, .EmpID End With Next myCount MsgBox "Number of Employees: " & colEmployees.Count & Chr(10) & _ "Employee(2) Name: " & colEmployees(2).EmpName MsgBox "Tracy's Weekly Pay: $" & colEmployees("1651").EmpWeeklyPay Set recEmployee = Nothing End Sub
The collection, colEmployees, is declared as a new collection and the record, recEmployee, as a new variable of the custom object type.
After the object’s properties are given values, the record, recEmployee, is added to the collection. The second parameter of the Add
method applies a unique key to the record—in this case, the employee ID number. This allows a specific record to be quickly accessed, as shown by the second message box (colEmployees("1651").EmpWeeklyPay
) (see Figure 22.7).
Collections can be created in a class module but, in this case, the innate methods of the collection (Add
, Remove
, Count
, Item
) are not available; they have to be created in the class module. The advantages of creating a collection in a class module are that the entire code is in one module, you have more control over what is done with the collection, and you can prevent access to the collection.
Insert a new class module for the collection and rename it clsEmployees
. Declare a private collection to be used within the class module:
Option Explicit Private AllEmployees As New Collection
Add the new properties and methods required to make the collection work. The innate methods of the collection are available within the class module and can be used to create the custom methods and properties:
Insert an Add
method for adding new items to the collection:
Public Sub Add(recEmployee As clsEmployee) AllEmployees.Add recEmployee, recEmployee.EmpID End Sub
Insert a Count
property to return the number of items in the collection:
Public Property Get Count() As Long Count = AllEmployees.Count End Property
Insert an Items
property to return the entire collection:
Public Property Get Items() As Collection Set Items = AllEmployees End Property
Insert an Item
property to return a specific item from the collection:
Public Property Get Item(myItem As Variant) As clsEmployee Set Item = AllEmployees(myItem) End Property
Insert a Remove
property to remove a specific item from the collection:
Public Sub Remove(myItem As Variant) AllEmployees.Remove (myItem) End Sub
Property Get
is used with Count
, Item
, and Items
because these are read-only properties. Item
returns a reference to a single member of the collection, whereas Items
returns the entire collection (so that it can be used in For Each Next
loops).
After the collection is configured in the class module, a procedure can be written in a standard module to use it:
Sub EmpAddCollection() Dim colEmployees As New clsEmployees Dim recEmployee As New clsEmployee Dim LastRow As Integer, myCount As Integer Dim EmpArray As Variant LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row EmpArray = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, 4)) For myCount = 1 To UBound(EmpArray) Set recEmployee = New clsEmployee With recEmployee .EmpName = EmpArray(myCount, 1) .EmpID = EmpArray(myCount, 2) .EmpRate = EmpArray(myCount, 3) .EmpWeeklyHrs = EmpArray(myCount, 4) colEmployees.Add recEmployee End With Next myCount MsgBox "Number of Employees: " & colEmployees.Count & Chr(10) & _ "Employee(2) Name: " & colEmployees.Item(2).EmpName MsgBox "Tracy's Weekly Pay: $" & colEmployees.Item("1651").EmpWeeklyPay For Each recEmployee In colEmployees.Items recEmployee.EmpRate = recEmployee.EmpRate * 1.5 Next recEmployee MsgBox "Tracy's Weekly Pay (after Bonus): $" & colEmployees.Item("1651"). _ EmpWeeklyPay Set recEmployee = Nothing End Sub
This program isn’t that different from the one used with the standard collection, but there are a few key differences. colEmployees
is declared as type clsEmployees
, the new class module collection, instead of as Collection
. The array and collection are filled the same way, but the way the records in the collection are referenced has changed. When referencing a member of the collection, such as employee record 2, the Item
property must be used. Compare the syntax of the message boxes in this program to the previous program.
The For Each Next
loop goes through each record in the collection and multiplies the EmpRate
by 1.5, changing its value. The result of this “bonus” is shown in a message box similar to the one shown previously in Figure 22.7.
User-defined types provide some of the power of a custom object but without the need of a class module. A class module allows the creation of custom properties and methods, whereas a UDT allows only custom properties. But sometimes, that’s all you need.
A UDT is declared with a Type..End Type
statement. It can be Public
or Private
. A name that is treated like an object is given to the UDT. Within the Type
, individual variables are declared that become the properties of the UDT.
Within an actual procedure, a variable is defined of the custom type. When that variable is used, the properties are available, just as they are in a custom object (see Figure 22.10).
The following example uses two UDTs to summarize a report of product styles in various stores.
The first UDT consists of properties for each product style:
Option Explicit Public Type Style StyleName As String Price As Single UnitsSold As Long UnitsOnHand As Long End Type
The second UDT consists of the store name and an array whose type is the first UDT:
Public Type Store Name As String Styles() As Style End Type
After the UDTs are established, the main program is written. Only a variable of the second UDT type, Store
, is needed because that type contains the first type, Style
(see Figure 22.11). But all the properties of the UDTs are easily available. And with the use of the UDT, the various variables are easy to remember—they’re only a dot (.) away:
Sub Main() Dim FinalRow As Integer, ThisRow As Integer, ThisStore As Integer Dim CurrRow As Integer, TotalDollarsSold As Integer, TotalUnitsSold As Integer Dim TotalDollarsOnHand As Integer, TotalUnitsOnHand As Integer Dim ThisStyle As Integer Dim StoreName As String ReDim Stores(0 To 0) As Store ' The UDT is declared FinalRow = Cells(Rows.Count, 1).End(xlUp).Row ' The following For loop fills both arrays. The outer array is filled with the ' store name and an array consisting of product details. ' To accomplish this, the store name is tracked and when it changes, 'the outer array is expanded. 'The inner array for each outer array expands with each new product For ThisRow = 2 To FinalRow StoreName = Range("A" & ThisRow).Value ' Checks whether this is the first entry in the outer array If LBound(Stores) = 0 Then ThisStore = 1 ReDim Stores(1 To 1) As Store Stores(1).Name = StoreName ReDim Stores(1).Styles(0 To 0) As Style Else For ThisStore = LBound(Stores) To UBound(Stores) If Stores(ThisStore).Name = StoreName Then Exit For Next ThisStore If ThisStore > UBound(Stores) Then ReDim Preserve Stores(LBound(Stores) To UBound(Stores) + 1) As Store Stores(ThisStore).Name = StoreName ReDim Stores(ThisStore).Styles(0 To 0) As Style End If End If With Stores(ThisStore) If LBound(.Styles) = 0 Then ReDim .Styles(1 To 1) As Style Else ReDim Preserve .Styles(LBound(.Styles) To _ UBound(.Styles) + 1) As Style End If With .Styles(UBound(.Styles)) .StyleName = Range("B" & ThisRow).Value .Price = Range("C" & ThisRow).Value .UnitsSold = Range("D" & ThisRow).Value .UnitsOnHand = Range("E" & ThisRow).Value End With End With Next ThisRow ' Create a report on a new sheet Sheets.Add Range("A1:E1").Value = Array("Store Name", "Units Sold", _ "Dollars Sold", "Units On Hand", "Dollars On Hand") CurrRow = 2 For ThisStore = LBound(Stores) To UBound(Stores) With Stores(ThisStore) TotalDollarsSold = 0 TotalUnitsSold = 0 TotalDollarsOnHand = 0 TotalUnitsOnHand = 0 ' Go through the array of product styles within the array ' of stores to summarize information For ThisStyle = LBound(.Styles) To UBound(.Styles) With .Styles(ThisStyle) TotalDollarsSold = TotalDollarsSold + .UnitsSold * .Price TotalUnitsSold = TotalUnitsSold + .UnitsSold TotalDollarsOnHand = TotalDollarsOnHand + .UnitsOnHand * .Price TotalUnitsOnHand = TotalUnitsOnHand + .UnitsOnHand End With Next ThisStyle Range("A" & CurrRow & ":E" & CurrRow).Value = _ Array(.Name, TotalUnitsSold, TotalDollarsSold, _ TotalUnitsOnHand, TotalDollarsOnHand) End With CurrRow = CurrRow + 1 Next ThisStore End Sub
In Chapter 23, “Advanced Userform Techniques,” you’ll learn about more controls and techniques you can use in building userforms.