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?
- Huh? What is a Custom Class?
- Alright, fine, but how does one even begin to understand how to create a VBA Custom Class?
- OK, so what is in this blueprint called a Class?
- So what are attributes?
- So what 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?
- OK, show me how to do one of your fancy schmancy Array Based Data Tables
- Methods are where the magic happens
- What is the advantage of these Class Modules?
- Conclusion
- Would you like to know more?
What is a Class Module?
A VBA Class Module is where you define a custom class from which to create objects.
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.
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.
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 Private pFirstName as String
‘Attributes
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.
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.
this blog posting makes me happy all over
Thank you, it is very good study material for VBA CLASS.
Pingback: Condense Button Code from 400 Subs to be More Manageable
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!
Hi. Sorry about that. When I get a chance, I will post the code in full in a separate post.
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
I’ve corrected the code. Hopefully, it works now.
Pingback: Why Morons And Idiots Can Contribute to the Digital World | The Land Beyond Spreadsheets
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
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?
Great help. Thank you.
This works for me.
‘Class Code
‘————–
‘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 Let Title(Value As String)
pTitle = Value
End Property
‘General Methods
Function EmployeeFullInfo() As String
EmployeeFullInfo = FirstName & ” ” & LastName & ” is a ” & Title
End Function
‘Module 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”)
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
thanks ….. it was really helpful
Dim mWB As Workbook
Set mWB = ActiveWorkbook
Set WSBoardMembers = Application.Worksheets(“Sheet1”)
is needed to fix “Run-time error ‘9’: Subscript out of range”
This is the least simple I have ever seen.
Could have been done with 10 lines of code, why in the world did you write such a complicated piece of code and explain it so poorly?
1/10 for attempt and good intentions, you suck at explaining things.
I understand this is just a simple example but I’d still like to point out that reading cells into VBA one at a time is extremely computationally expensive so this approach isn’t going to scale well to applications beyond a few hundred rows. IMHO, a better technique would be to simply set the data range to a variant at which point loading it into a class defined data structure would be largely superfluous. In my experience I’ve been able to set tables in the tens/hundreds of thousands of rows essentially instantaneously. Another advantage of array variants is you can quickly and syntactically efficiently extract individual rows and columns from the table using the INDEX function.
That said I still found this to be a very useful intro into how to put together a user defined class. Thank you for posting it.
Matt,
that was my first thought as well. Used Range to Variant Array to Class is the way to go and would certainly be best practice.
I’m a little more forgiving, since this is an introduction to classes and not a lesson in efficiency.
Yes, it is very good material. However, it would be useful if you made use of indentation – like the material inside a for loop is typically indented so that it is inside of the loop. This makes it MUCH easier to read.
Mike
Has anyone ever heard of indenting your code within functions/methods/subroutines? It makes the code much more readable.
General errors that may stop some people:
Using ` instead of ‘ where the latter ‘ is VBA comment indicator.
Title Let ( instead of Let Title(
Not including something like
Dim mWB As Workbook
Set mWB = ActiveWorkbook
Set WSBoardMembers = mWB.Sheets(“EmployeeInfo”)
as
Set WSBoardMembers = Worksheets(“EmployeeInfo.xlsm”)
will give an Object Required error.
i want to know the concept of making custom object.
i mean when sholud i make custom class ?
Apparently Nerobyrne has never taught; if he/she has, I have sympathy for his/her students. Yes, it could have been done with 10 lines of code, but that would not have illustrated using classes, which was the point and which you did quite well (BTW, I have taught at the college and corporate levels, including Excel). Thank you for the good article. It would appear some people just “suck” at understanding, and wish to blame their shortcomings on others.
Please be careful though.
If you want to pass ByRef, this is not the data structure for you, I am now learning the hard way.
Example:
cPeople.FirstName = “Jane”
cPeople.LastName = “Doe”
If cPeople.LastName = “Anraj” Then …
Those are all fine, but here’s what’s not…
x = MyFunction (cPeople.FirstName)
MyFunction only has access to Get, not Let, even if the function specifies ByRef.
So for recreating something like a recordset and a bunch of general-use string-manipulation functions that just aren’t going to all get converted into methods of this class… it just doesn’t work. I’ve been searching for workarounds, but none of them I’ve found allow user-defined functions to treat the Let and Get class module instances like regular variables.
Advanced class example can be viewed in this video :https://www.youtube.com/watch?v=7pkexwngELo
I executed your code but nothing get printed out. Is there another way to look at the result?
Thanks
Thank you very much for tutorials.
I made useful userforms using class modules :
– Fast percentage calculation form
– Product discount calculation according to quantity,price and percent rate
– Hiding / displaying the columns of sheet (Column management with userform)
Templates can be viewed here : https://youtu.be/eBg4xnw9NM4