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:

Figure 22.1. Custom objects are created in class modules.

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.

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

Public myAppEvent As New clsAppEvents

Sub TrapAppEvent()

Set myAppEvent.xlApp = Application

End Sub

Embedded Chart Events

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.”

Figure 22.3. The chart events are available after the chart type variable has been declared.

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

Note

The BeforeDoubleClick and BeforeRightClick events are only triggered 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 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:

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).

Figure 22.4. The properties and method of the custom object are just as easily accessible as they are for standard objects.

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.

Figure 22.5. Create custom objects to make code more efficient.

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

Figure 22.6. Use Property Let and Property Get procedures for more control over custom object properties.

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.

Creating a Collection in a Standard Module

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).

Figure 22.7. Individual records in a collection can be easily accessed.

Note

The unique key is an optional parameter.An error message appears if a duplicate key is entered.

Creating a Collection in a Class Module

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.

Help Buttons

You have a complex sheet that requires a way for the user to get help.You could place the information in comment boxes, but they aren’t very obvious, especially to the novice Excel user.Another option is to create help buttons.

On the worksheet, create small labels with a question mark in each one.To get the buttonlike appearance shown in Figure 22.8, set the SpecialEffect property of the labels to Raised and darken the BackColor.Place one label per row.Two columns over from the button, enter the help text you want to appear when the label is clicked.Hide this help text column.

Figure 22.8. Attach help buttons to the sheet and enter help text.

Create a simple userform with a label and a Close button.Rename the form to HelpForm, the button to CloseHelp, and the label to HelpText. Size the label large enough to hold the help text.Add a macro behind the form to hide the form when the button is clicked:

Private Sub CloseHelp_Click()
Unload Me
End Sub

Insert a class module named clsLabel. You’ll need a variable, Lbl, to capture the control events:

Public WithEvents Lbl As MSForms.Label

Also, you need a method of finding and displaying the corresponding help text:

Private Sub Lbl_Click()
Dim Rng As Range
Set Rng = Lbl.TopLeftCell

If Lbl.Caption = "?" Then
    HelpForm.Caption = "Label in cell " & Rng.Address(0, 0)
    HelpForm.HelpText.Caption = Rng.Offset(, 2).Value
    HelpForm.Show
End If

End Sub

In the ThisWorkbook module, create a Workbook_Open procedure to create a collection of the labels in the workbook:

Option Explicit
Option Base 1
Dim col As Collection

Sub Workbook_Open()
Dim WS As Worksheet
Dim cLbl As clsLabel
Dim OleObj As OLEObject

Set col = New Collection

For Each WS In ThisWorkbook.Worksheets
    For Each OleObj In WS.OLEObjects
        If OleObj.OLEType = xlOLEControl Then
            If TypeName(OleObj.Object) = "Label" Then
                Set cLbl = New clsLabel
                Set cLbl.Lbl = OleObj.Object
                col.Add cLbl
            End If
        End If
    Next OleObj
Next WS

End Sub

Run Workbook_Open to create the collection.Click a label on the worksheet.The corresponding help text appears in the help form, as shown in Figure 22.9.

Figure 22.9. Help text is only a click away.

 

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).

Figure 22.10. The properties of a UDT are available as they are in a custom object.

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

Figure 22.11. UDTs can make a potentially confusing multivariable program easier to write. (Note:The results of the program have been combined with the raw data for convenience.)

In Chapter 23, “Advanced Userform Techniques,” you’ll learn about more controls and techniques you can use in building userforms.