Home > Articles > Programming > Windows Programming

Professional Excel Development: Using Class Modules to Create Objects

  • Print
  • + Share This
The goal of this chapter from Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET, 2nd Edition is to show you how to create your own objects, including how to create a single custom object and then create a collection containing multiple instances of the object, and how to trap and raise events within your classes.
This chapter is from the book

Class modules are used to create objects. There are many reasons for you as a developer to create your own objects, including the following:

  • To encapsulate VBA and Windows API code to make it transportable and easy to use and reuse, as shown in Chapter 12, "Understanding and Using Windows API Calls"
  • To trap events
  • To raise events
  • To create your own objects and object models

In this chapter, we assume you are already familiar with writing VBA code to manipulate the objects in Excel and are familiar with the Excel object model that defines the relationships among those objects. We also assume you are familiar with object properties, methods, and events. If you have written code in the ThisWorkbook module, any of the modules behind worksheets or charts, or the module associated with a UserForm, you have already worked with class modules. One of the key features of these modules, like all class modules, is the ability to trap and respond to events.

The goal of this chapter is to show you how to create your own objects. We begin by explaining how to create a single custom object and then show how you can create a collection containing multiple instances of the object. We continue with a demonstration of how to trap and raise events within your classes.

Creating Objects

Say we want to develop code to analyze a single cell in a worksheet and categorize the entry in that cell as one of the following:

  • Empty
  • Containing a label
  • Containing a constant numeric value
  • Containing a formula

This can be readily accomplished by creating a new object with the appropriate properties and methods. Our new object will be a Cell object. It will have an Analyze method that determines the cell type and sets the CellType property to a numeric value that can be used in our code. We will also have a DescriptiveCellType property so we can display the cell type as text.

Listing 7-1 shows the CCell class module code. This class module is used to create a custom Cell object representing the specified cell, analyze the contents of the cell, and return the type of the cell as a user-friendly text string.

Listing 7-1. The CCell Class Module

Option Explicit

Public Enum anlCellType
End Enum

Private muCellType As anlCellType
Private mrngCell As Excel.Range

Property Set Cell(ByRef rngCell As Excel.Range)
    Set mrngCell = rngCell
End Property

Property Get Cell() As Excel.Range
    Set Cell = mrngCell
End Property

Property Get CellType() As anlCellType
    CellType = muCellType
End Property

Property Get DescriptiveCellType() As String
    Select Case muCellType
        Case anlCellTypeEmpty
            DescriptiveCellType = "Empty"
        Case anlCellTypeFormula
            DescriptiveCellType = "Formula"
        Case anlCellTypeConstant
            DescriptiveCellType = "Constant"
        Case anlCellTypeLabel
            DescriptiveCellType = "Label"
    End Select
End Property

Public Sub Analyze()
    If IsEmpty(mrngCell) Then
        muCellType = anlCellTypeEmpty
    ElseIf mrngCell.HasFormula Then
        muCellType = anlCellTypeFormula
    ElseIf IsNumeric(mrngCell.Formula) Then
        muCellType = anlCellTypeConstant
        muCellType = anlCellTypeLabel
    End If
End Sub

The CCell class module contains a public enumeration with four members, each of which represents a cell type. By default, the enumeration members are assigned values from zero to three. The enumeration member names help make our code more readable and easier to maintain. The enumeration member values are translated into user-friendly text by the DescriptiveCellType property.

Listing 7-2 shows the AnalyzeActiveCell procedure. This procedure is contained in the standard module MEntryPoints.

Listing 7-2. The AnalyzeActiveCell Procedure

Public Sub AnalyzeActiveCell()

    Dim clsCell As CCell

    ' Create new instance of Cell object
    Set clsCell = New CCell

    ' Determine cell type and display it
    Set clsCell.Cell = Application.ActiveCell
    MsgBox clsCell.DescriptiveCellType

End Sub

If you select a cell on a worksheet and run the AnalyzeActiveCell procedure it creates a new instance of the CCell class that it stores in the clsCell object variable. The procedure then assigns the active cell to the Cell property of this Cell object, executes its Analyze method, and displays the result of its DescriptiveCellType property. This code is contained in the Analysis1.xls workbook in the \Concepts\Ch07 – Using Class Modules to Create Objects folder on the CD that accompanies this book.

Class Module Structure

A class module can be thought of as a template for an object. It defines the methods and properties of the object. Any public subroutines or functions in the class module become methods of the object, and any public variables or property procedures become properties of the object. You can use the class module to create as many instances of the object as you require.

Property Procedures

Rather than rely on public variables to define properties it is better practice to use property procedures. These give you more control over how properties are assigned values and how they return values. Property procedures allow you to validate the data passed to the object and to perform related actions where appropriate. They also enable you to make properties read-only or write-only if you want.

The CCell class uses two private module-level variables to store its properties internally. muCellType holds the cell type in the form of an anlCellType enumeration member value. mrngCell holds a reference to the single-cell Range that an object created from the CCell class will represent.

Property procedures control the interface between these variables and the outside world. Property procedures come in three forms:

  • Property Let—Used to assign a simple value to a property
  • Property Set—Used to assign an object reference to a property
  • Property Get—Used to return the simple value or object reference held by a property to the outside world

The property name presented to the outside world is the same as the name of the property procedure. The CCell class uses Property Set Cell to allow you to assign a Range reference to the Cell property of the Cell object. The property procedure stores the reference in the mrngCell variable. This procedure could have a validation check to ensure that only single-cell ranges can be specified. There is a corresponding Property Get Cell procedure that allows this property to be read.

The CCell class uses two Property Get procedures to return the cell type as an enumeration member value or as descriptive text. These properties are read-only because they have no corresponding Property Let procedures.


The CCell class has one method defined by the Analyze subroutine. It determines the type of data in the cell referred to by the mrngCell variable and assigns the corresponding enumeration member to the muCellType variable. Because it is a subroutine, the Analyze method doesn't return a value to the outside world. If a method is created as a function it can return a value. The Analyze method could be converted to a function that returned the text value associated with the cell type as shown in Listing 7-3.

Listing 7-3. The Analyze Method of the Cell Object

Public Function Analyze() As String

    If IsEmpty(mrngCell) Then
        muCellType = anlCellTypeEmpty
    ElseIf mrngCell.HasFormula Then
        muCellType = anlCellTypeFormula
    ElseIf IsNumeric(mrngCell.Formula) Then
        muCellType = anlCellTypeConstant
        muCellType = anlCellTypeLabel
    End If

    Analyze = Me.DescriptiveCellType

End Function

You could then analyze the cell and display the return value with the following single line of code instead of the original two lines:

MsgBox clsCell.Analyze()
  • + Share This
  • 🔖 Save To Your Account