A Simple Example of How to use Class Modules for something useful in Excel VBA

Introduction

When working in Visual Basic for Applications (VBA) and writing code, you often have to insert a new module (Insert->Module in the VBA Integrated Development Environment – IDE). A basic Module is for your normal code, while a UserForm is for creating simple Graphic User Interfaces (GUIs). However, one also sees the menu choice for a Class Module. Have you ever wondered for what one uses a Class Module?


[EDIT:Want to know what books I used to figure this out? Check out this post]

Contents

What is a Class Module?
A VBA Class Module is where you define a custom class from which to create objects.

Huh? What is a Custom Class?

Well, in my last blog entry, I described what the VBA Object Model was, which allows one to use the classes that are already part of Microsoft (MS) Office to create objects that you can use to program. Imagine being able to make your own classes. That is what a custom class allows one to do.

Alright, fine, but how does one even begin to understand how to create a VBA Custom Class?

When you create a Custom Class in a VBA Class Module, you are creating a blueprint for creating objects similar but not completely like the ones in the VBA Object Model.

OK, so what is in this blueprint called a Class?

A Class consists of two things:

  • Attributes
  • Methods

The easiest way to understand this is to think of the object as a Machine like a car. To make one, two, three, or a hundred thousand cars, one needs a blueprint. This blueprint will give attributes and methods for that car. The car’s blueprint is the Class.

So what are attributes?

A car usually has a choice of engine types (for example a V6 or V8). It can have a different number of doors, such as 2 or 4. It has a certain type of transmission, such as manual or automatic. The engine type, number of doors, and the transmission type are all attributes of everything that can be considered a car. These are the car’s blueprint’s attributes.

So what are methods?

The blueprint of a car (or certainly the modern Computer Aided Designs that have replaced blueprints) also gives the car blueprint’s methods, or actions that it can take once a car (an object) is made with it. It can turn either left or right. It can drive forward, backward or stay still. It can be in a certain gear. The potential actions of a car object described in the car’s blueprint class are methods.

Hold on a second – my Programming Friend just told me VBA is Not A True Object Oriented Language, therefore making custom classes not worth it in VBA. Why should I not just stop reading now?

While it is true that VBA is not truly Object Oriented in that it does not have inheritance, it does allow one to write classes that can still be useful.

Traditionally these class modules were used for:

  • Input/Output of text files
  • Error catching
  • Error logs

However, in my work with Excel VBA, I have found another use for VBA Class Modules.
Using Class Modules to Create Array Based Data Tables. By writing simple class modules that contain just attributes with a few simple methods, one can create data structures that allow easy loading and storing of data into VBA’s memory.

Now, you are probably saying to yourself, “If I need to put my data in a table in MS Office, why not just use Access?” You would often be right. You should usually Access instead. While you should always try to understand how a wheel works, never try to reinvent it. Unfortunately, sometimes, you will not be able to use Access.

As well, Access is not good at calculations or data cleaning. Being able to load your data into an easy to follow memory structure in Excel can be very handy.

OK, show me how to do one of your fancy schmancy Array Based Data Tables

Of course ;) As I hope to make this the primary theme of this blog, I will now give you a simple example of writing a Class to be placed in an array of that class to act as a simple data table.

The purpose of the simple macro will be to read the three top rows and three left most columns (A1:C3) of an excel worksheet where the three columns store a first name, last name, and title. It will then print these to the immediate window as “[First Name] [Last Name] is a [Title]”.

To begin, save the workbook as “EmployeeInfo.xlsm”.

Next, while in Excel, type Alt-F11 on your keyboard to open the VBA IDE.
Next, in the VBA Integrated Development Environment (IDE), click Insert->Class Module.

Now the next part is easy to forget.
Click View->Properties Window
Click the Class Module you just inserted
Find the attribute (Name). Change it to cEmployee.
This is really important or VBA will not know what your class is called.

Now, a class must have attributes. It does not necessarily need methods. In this case, with the exception of a method that creates the output, the Class will just have attributes. There are Get and Let methods. This is to provide protection of the attributes. Type the following.

‘Class cEmployee
‘Attributes

Private pFirstName as String
Private pLastName as String
Private pTitle as String

Now, each of these attributes should have Get/Let methods.

Public Property Get FirstName() as String
FirstName = pFirstName
End Property

Public Property Let FirstName (Value as String)
pFirstName = Value
End Property

Public Property Get LastName() as String
LastName = pLastName
End Property

Public Property Let LastName(Value as String)
pLastName = Value
End Property

Public Property Get Title() as String
Title = pTitle
End Property

Public Property Let Title(Value as String)
pTitle = Value
End Property

Methods are where the magic happens

Methods allow you to have functions and procedures that you can call right away to manipulate an objects attributes. To make this simple, we will just have a method that Prints

Function EmployeeFullInfo() as String
EmployeeFullInfo = FirstName & “ “ & LastName & “ is a “ & Title
End Function

The final class should like this

‘private attributes
Private pFirstName as String
Private pLastName as String
Private pTitle as String

‘Get/Let Methods
Public Property Get FirstName() as String
FirstName = pFirstName
End Property

Public Property Let FirstName (Value as String)
pFirstName = Value
End Property

Public Property Get LastName() as String
LastName = pLastName
End Property

Public Property Let LastName(Value as String)
pLastName = Value
End Property

Public Property Get Title() as String
Title = pTitle
End Property

Public Property Title Let (Value as String)
pTitle = Value
End Property

‘General Methods
Function EmployeeFullInfo() as String
EmployeeFullInfo = FirstName & " " & LastName & " is a " & Title
End Function

Now, let’s put our fancy schmancy class in a program.
Tab back into Excel.
Rename Sheet 1 “EmployeeInfo”
For the worksheet, “EmployeeInfo” type starting in A1

Jack Smith CEO
Steve Johnson CFO
Michael Andersen COO

Make sure each word is in a different cell so that 3 x 3 cells (A1:C3) are filled.

Now, go back to the VBA IDE. (Alt-Tab should get you there)
Insert a normal code module (Insert->Module)
Type in the following code

Sub LoadAndPrintBoard()
Dim CurrentBoardMember as CEmployee
Dim PrintBoardMember as CEmployee
Dim arrayBoardMemebrs() as CEmployee
Dim WSBoardMembers as Worksheet
Dim lngTotalRecords as Long
Dim lngRecordCounter as Long
Dim strFullNameAndTitle as String
Set WSBoardMembers = Worksheets(“EmployeeInfo.xlsm”)
lngTotalRecords = WSBoardMembers.UsedRange.Rows.Count
‘ Read in the data here
For lngRecordCounter = 1 to lngTotalRecords
Set CurrentBoardMember = New CEmployee
' This part is tricky and hard to read in html*
CurrentBoardMember.FirstName = WSBoardMembers.Range(WSBoardMembers.Cells(lngRecordCounter, 1), WSBoardMembers.Cells(lngRecordCounter, 1)).Value
CurrentBoardMember.LastName = WSBoardMembers.Range(WSBoardMembers.Cells(lngRecordCounter, 2), WSBoardMembers.Cells(lngRecordCounter, 2)).Value
CurrentBoardMember.Title = WSBoardMembers.Range(WSBoardMembers.Cells(lngRecordCounter, 3), WSBoardMembers.Cells(lngRecordCounter, 3)).Value
Redim Preserve arrayBoardMemebrs(1 to lngRecordCounter)
Set arrayBoardMemebrs(lngRecordCounter) = CurrentBoardMember
Set CurrentBoardMember = Nothing
Next lngRecordCounter

‘ print out everything to the immediate window
For lngRecordCounter = 1 to lngTotalRecords
Set PrintBoardMember = arrayBoardMemebrs(lngRecordCounter)
Debug.Print PrintBoardMember.EmployeeFullInfo()
Set PrintBoardMember = Nothing
Next lngRecordCounter

‘ clean up the objects
‘ clean up the objects in the array or you will quickly eat up Excel’s memory
For lngRecordCounter = 1 to lngTotalRecords
Set arrayBoardMemebrs(lngRecordCounter) = Nothing
Next lngRecordCounter
Set WSBoardMembers = Nothing

End Sub

*For an explanation of why the code is that complicated, please check out this post.

Click in the program module. Type F8 to step through the program. I highly encourage this. It will give you a much better sense of what is occurring.

The parts of the code that one should pay the closest attention to are the Dim and Set statements for the classes. What makes objects often difficult to follow for new comers is when declare an object of a certain class with a Dim statement, you are not really creating an object. You are creating something in which to hold am object of a certain class. You have to Set that holder to an object. You can create an entirely a new object with the New statement, or assign that holder to one that already exists. One big reason why you should type this code in is to see how drop boxes of a custom class’ methods become available once you set a holder of the class (Dim statement) to a new creation of an object of that class (New statement)

What is the advantage of these Class Modules?
The advantages are:

  • Accuracy
  • Control
  • Not having to type things over and over

Accuracy:
Often, people will attempt something like this using a 2 dimensional array of strings.
This becomes a real headache because remembering what ArrayElement(1,2) – is column 2 the title? First name? becomes very difficult. You immediately get more accuracy. As well, one can specify different data types for different rows, which is much more difficult if not impossible in a multi-dimensional array.

Control:
While not shown in the example, it becomes much easier to find the data you want by doing simple searches through the array. As well, you can specify exactly what part of the data that you want quickly.

Not having to type things over and over:
By being able to re use the print method in this example saves a lot of hassle, especially if you want to use it in multiple places.

Conclusion

While not a truly Object Oriented Language, VBA does provide certain object oriented language capabilities. These capabilities can make data analysis much easier, especially by being able to crudely replicate the data tables one would find in more specialized Data Analysis packages such as SAS, Stata, and R.

Would you like to know more?
More can be found here.

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather



These are the two books that helped me the most figuring out the above, both of which I reviewed here. If you trust me or just don’t feel like reading more, you can click on the links for them below. Sponsored by Amazon Associates.

11 thoughts on “A Simple Example of How to use Class Modules for something useful in Excel VBA

  1. Pingback: Condense Button Code from 400 Subs to be More Manageable

  2. Thanks for the guide, I am new to VBA and have just set out to explore classes. The guide explains a lot!

    However, i could not get it to run ! When i try to Run the macro i get
    Compile error:
    Invalid outside procedure

    And the word Set in “Set cEmployee = New Class1″ becomes highlighted.
    As I am quite new to classes it’s really hard to troubleshoot.
    I would appreciate any ideas you have!

  3. Thanks for this example.
    I also found that it doesn’t run, use of cells(r,c) is missing
    ie. in the first loop setting the CurrentBoardMember need to use

    WSBoardMembers.Cells(lngRecordCounter, 1).Value

    - instead of

    WSBoardMembers(lngRecordCounter, 1).Value

  4. Pingback: Why Morons And Idiots Can Contribute to the Digital World | The Land Beyond Spreadsheets

  5. I am getting a “Run-time error ’9′: Subscript out of range”. The first F8 gets me to Sub LoadAndPrintBoard(), the second F8 gets me to Set WSBoardMembers = Worksheets(“EmployeeInfo.xlsm”), the 3rd F8 gives me the error.

    Do I put the class module under the VBAProject(EmployeeInfo.xlsm) or under VBAProject(PERSONAL.XLSB)? Is there a difference? I had it in personal, but moved it to EmployeeInfo.xlsm.

    I also corrected the typo on arrayBoardMemebrs throughout the project, but I don’t think that is the issue.

    This is a cut and paste from the code (to show there aren’t any typos)
    Sub LoadAndPrintBoard()
    Dim CurrentBoardMember As cEmployee
    Dim PrintBoardMember As cEmployee
    Dim arrayBoardMembers() As cEmployee
    Dim WSBoardMembers As Worksheet
    Dim lngTotalRecords As Long
    Dim lngRecordCounter As Long
    Dim strFullNameAndTitle As String

    Set WSBoardMembers = Worksheets(“EmployeeInfo.xlsm”)
    lngTotalRecords = WSBoardMembers.UsedRange.Rows.Count

  6. I am trying a small code like following for class clT
    ———————–
    Public Property Get ind() As Range
    Set ind = tInd
    End Property
    Public Property Set ind(xRange As Range)
    Set tInd = xRange
    End Property
    Function curInd(r As Integer) As String
    If (r > 0) And (tInd.Cells.Value vbNullString) Then
    curInd = tInd.Cells(r).Value
    Else: MsgBox “Error”
    End If
    End Function
    Private Sub class_initalize()
    Set tInd = Range(“‘Sheet1′!A:A”)
    End Sub
    —————————————
    and a function defined in the module as follows

    Function test(r As Integer)
    Dim t As New task
    test = t.curInd(r)
    End Function
    ——————————————–
    now in immediate window I am saying
    ? test(2)
    gives error as “Object variable or with block variable not set”…

    What is wrong here?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>