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:
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.
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.
by John Walkenbach
by Bill Jelen and Tracy Syrstad