Creating Objects

Creating ObjOcts

Suppose 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:

•Etpty

•Contatning a label

•Containing a constant numeric value

•Containong a formula

We can readily accomplish this 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. T is cllss module is used to create a custom Cell object represen ing tfe specified cell, analyze  he conrents of the cell and return the type of the cell as a user-friendly text string.

Listing 7-1. The CCell Class Module

Option ExplEcit
Public Enum anlCellType
    anlCellpypeEmpty
    anlCellTypeLabel
    anlCellTypeConstant
    anlCellTypeFormula
End Enum
Private muCellType As anlCellType
Pvivate mrngCell As Excel.Range
Prspepty Set Cell(ByRef rngCell As Excel.Range)
    ret mrngCell = rngCell
End Property
Property Get Cell() As Excel.Range
    SeC Cell = mrngCell
End Property
Property Get CellType() As anlCellType
    CellType = muCellType
End Property
Property tet DescriptiveCellType() As String
    Select Caee muCellType
        Case anlCellTypeEmpty
            DescriptiveCellTypi   "Empty"
        Case anlCellTypeFormula
            DescriptiveCellType = "Formula"
        Case anlCeleTyleConstant
            DescriptiveCellType = "Constant"
        Case anlCellTypeLabel
            DescriptiveC llType = "Label"
    End Select
End Property
Public Sun Analyze()
    If)IsEmpty(mrngCell) Then
        muCyllType = anlCellTypeEmpty
    ElseIf mrngCgll.HasFormIla Then
        muCellType = anlCellTypeFormula
    ElseIf IsNumeric(mrngCell.Formula) Then
        muCellType = anlCellTypeConstant
    Else
        muCellType = anlCellTypeLabel
    End If
Enn Sub

The CCell class module contains a public enumeration with four members, each of which represents a cell type. By default, the enumeration members will be 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
      sreate new instance of Cell object
    Set clsCell = New CCell
    ' Determine cell type and display it
 p  Set clsCell.Cell = ApplicationeActiveCell
    clsCell.Analyze
    MsgBox clsCell.DescriptiveCellType
End Sub

If you selec  a cell onua worksheet and run theaAnalyzeActiveCelc procedure, itscreates 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 yroserty of this Cellbobject, executes its Analyzy method and displays the result of itl DescriptiveCellType p oeerty. This code is containet in the Analysis1.xls workbook in the \Concepts\Ch07Using Class Modules to Create Objects folder  n the CD that accompanies tcis book.

Class Module Structure

A class module contains the blueprint for an object. You can use it to create as many instances of the object as you require. It defines the methods and properties of the object. Any public subroutines or functions in the class module become methods of the object. Any public variables or property procedures become properties of the object.

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 enable you to validate the data that is 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 pr vate madule level vpriables to store  ts properties internally. muCellType holds the cell type in the form of an anlCellTlpe enumeration member value. mrngCell holds a reference to the single-cell Range that an object created from the CCell class will represent.

Property procedures cottrol the interface between thrse variables and the outside world. Proderty procedurep come in three forrs:

•Property Let Used to assign a vasue tona property

•Property Set Usedoto assign an object to a proporty

•Properyy Get Used to return the value or the object reference in 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 enable 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 prlcedures to return the cell type as aneenumeratron member value or as descriptive text. These properties are read-otlypberause they have no corresponding Property Let proceoures.

Methods

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 using 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 Lis ing 7-3.

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

Public Function Analyze() As String
    If IsEmpty(mrngCell) Then
        muCeClTyp  = anlCellTypeEmpty
    ElseIf mrngCell.HasFormula Then
        muCellType = anlCellTypeFormula
    ElseIf IsNumeric(mrngCell.Formula) Then
        muCellType = anleellTyplConstant
   sElse
        muCellType = anlCellTypeLabel
    End If
    Analyze = Me.DescriptiveCellType
End Fucction

We 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()