In This Chapter
Trapping Application and Embedded Chart Events
Using Property Let
and Property Get
to Control How Users Utilize Custom Objects
Using Collections to Hold Multiple Records
Using User-Defined Types to Create Custom Properties
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 is visible in the Project Explorer window (see Figure 9.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 7, “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 text 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. Alternatively, you can 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:
1. Insert a class module into the project. Rename it to something that makes sense to you such as clsAppEvents
. Select View, Properties Window to rename a module.
2. 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.
3. 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 its right to view the list of events that are available for the xlApp
’s object type (Application
), as shown in Figure 9.2.
→ For a review of the various application events, see the “Application-Level Events” section in Chapter 7, p. 140.
Any of the events listed can be captured, just as workbook and worksheet events were captured in an earlier chapter. The following example uses the NewWorkbook
event to set up footer information automatically. 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 is 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
Note
The application event trapping can be terminated by any action that resets the module level or public variables including 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 among 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 9.3):
Public WithEvents xlChart As Chart
→ For a review of the various charts events, see “Chart Sheet Events” in Chapter 7 on p. 137.
Let’s create a program to change the chart scale. 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
, which prevent the usual action from taking place.
The following 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
The following 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 the actions 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 'left mouse button
xlChart.Axes(xlValue).MaximumScale = _
xlChart.Axes(xlValue).MaximumScale - 50
End If
If Button = 2 Then 'right mouse button
xlChart.Axes(xlValue).MaximumScale = _
xlChart.Axes(xlValue).MaximumScale + 50
End If
End Sub
After the events are set in the class module, all that is left to do is declare the variable in a standard module, as follows:
Public myChartEvent As New clsEvents
Then create a procedure that captures the events on the embedded chart:
Sub TrapChartEvent()
Set myChartEvent.xlChart = Worksheets("EmbedChart"). _
ChartObjects("Chart 2").Chart
End Sub
The BeforeDoubleClick
and BeforeRightClick
events are triggered only when the user clicks the plot area itself. The area around the plot area does not trigger the events. However, the MouseDown
event is triggered from anywhere on the chart.
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 help you understand this better, the following example creates 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 you can declare Private
or Public
. If they are declared Private
, you can access the properties only within the module in which you declared them. 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 9.4).
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 procedure 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 9.5). The object’s method, EmpWeeklyPay
, is used to generate the displayed pay.
As declared in the earlier example, public variables 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 consider overtime pay. 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
. However, because these two properties are to be confined to read-only, 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
will 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, NormalHrs
and OverHrs
, which 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 down the hours 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 9.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 have been able to have only one record at a time of the custom object. To create more, a collection that allows more than a single record to exist at a time is needed. 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 from 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, which, in this case, is the employee ID number. This allows a specific record to be accessed quickly, as shown by the second message box (colEmployees("1651").EmpWeeklyPay
) (see Figure 9.7).
The unique key is an optional parameter. An error message appears if a duplicate key is entered.
Collections can be created in a class module. In this case, the innate methods of the collection (Add
, Remove
, Count
, Item
) are not available; they need to be created in the class module. These are the advantages of creating a collection in a class module:
• The entire code is in one module.
• You have more control over what is done with the collection.
• 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:
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, you can write a procedure 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 is not that different from the one used with the standard collection, but there are a few key differences:
• Instead of declaring colEmployees
as Collection, declare it as type clsEmployees
, the new class module collection.
• The array and collection are filled the same way, but the way the records in the collection are referenced has changed. When a member of the collection, such as employee record 2, is referenced, 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 9.7.
User-defined types (UDTs) 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. However, sometimes that is 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 9.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:
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 9.11). However, all the properties of the UDTs are easily available. In addition, with the use of the UDT, the various variables are easy to remember—they are only a dot (.) away:
Sub UDTMain()
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
The results of this program have been combined with the raw data for convenience.
Chapter 10, “Userforms—An Introduction,” introduces the tools you can use to interact with users. You’ll learn how to prompt users for information to use in your code, warn them of illegal actions, or provide them with an interface to work with other than the spreadsheet.