Class Modules & The Classes Function in Microsoft Excel

Class Modules / Classes

Class Modules are probably one of the most valuable and underutilized tools within the VBA toolset. A Classmodule allows a developer to develop custom objects, which they can share with other developers or non-developers who might not have an in-depth knowledge and understanding of the code which they have developed.

For example, let’s assume that we have a spreadsheet which contains rows of personal information and characteristics of a group of people. Let’s assume you want to create a custom ‘People’ object with specific attributes (hair, eyes, weight, height) and specific abilities (speak, sing, jump). Developing such a custom object would then be easier for someone who might not be as familiar with the worksheet or other modules to access the data / functionality.

Let’s look at an example. Let’s assume we want other developers to use functionality we developed, allowing them to use our custom objects which be easier to use that reviewing and familiarizing themselves with rows and rows of code.

In this example the developer will just utilize our custom object (class). Pass variables to it and commit them to the spreadsheet. The code highlighted in yellow is the only code that the developer would need to write. They will create an instance of our custom object and initialize it. Then they can assign attributes to the custom object and then commit those values to the spreadsheet. Note that the developer does not need to know where and how the values will be stored. That is something that we can be responsible for maintaining. The only thing the developer needs to know is how to create the class instance and start using our object.

Code

Function

Function Example()

‘ On Error Resume Next

Dim NormalPeople As Person

Set NormalPeople = New Person

NormalPeople.Name = “Jack”

NormalPeople.Height = 7

NormalPeople.Weight = 260

NormalPeople.EyeColor = “Blue”

NormalPeople.HairColor = “Black”

Call RecordPerson(NormalPeople)

End Function

Function RecordPerson(ByRef NormalPeople As Person)

On Error Resume Next

Dim WB As Workbook‘Workbook object

Dim WS As Worksheet‘Worksheet object

‘Initialize the varables

Set WB = ThisWorkbook

Set WS = WB.Worksheets(“Example”)

Dim A As Long

Dim tmpValue As Variant

‘Step through the rows and find the first open row and start writing the output to it.

For A = 2 To 15

‘Find the first open record

If Len(Trim(WS.Cells(A, 2).Value)) = 0 Then

WS.Cells(A, 2).Value = NormalPeople.Name

WS.Cells(A, 3).Value = NormalPeople.Height

WS.Cells(A, 4).Value = NormalPeople.Weight

WS.Cells(A, 5).Value = NormalPeople.HairColor

WS.Cells(A, 6).Value = NormalPeople.EyeColor

Exit For

End If

Next

End Function

Class Module

‘Public variables to hold the values we assign to our object

Public strName As String

Public lngWeight As Long

Public strEyeColor As String

Public strHairColor As String

Public lngHeight As Long

‘You assign and obtain values from your Class properties using the Getand Letfunctions. You can also develop some data validation routines to ensure that the input is what you expect it to be.

Public Property Get Name() As String

Name = strName

End Property

Public Property Let Name(Value As String)

strName = Value

End Property

Public Property Get Weight() As Long

Weight = lngWeight

End Property

Public Property Let Weight(Value As Long)

lngWeight = Value

End Property

Public Property Get Height() As Long

Height = lngHeight

End Property

Public Property Let Height(Value As Long)

lngHeight = Value

End Property

Public Property Get EyeColor() As String

EyeColor = strEyeColor

End Property

Public Property Let EyeColor(Value As String)

strEyeColor = Value

End Property

Public Property Get HairColor() As String

HairColor = strHairColor

End Property

Public Property Let HairColor(Value As String)

strHairColor = Value

End Property

Output