How to use Class Modules in Excel VBA, check out the video tutorial then the article for some extra depth.
Get VBA Class Module Spreadsheet Here
Also get updates when new blog or video tutorials are released.
VBA Class Modules – Some Essential background
Class modules are the bread and butter of Object Oriented programming (OOP) with VBA in Microsoft Office.
Before I get into these in detail, let me explain the big idea behind OOP in VBA.
Microsoft Office VBA is an object based language, what that means is that VBA is the programming language that is used to manipulate the Microsoft Office Object Model for applications, such as Word, Excel, Access etc.
For instance Excel VBA macro's do their stuff by communicating with Excel's built in "code" objects such as "workbook","worksheet", "range" etc.
Before we discuss VBA Class Modules which you use to create your own custom objects, I want to make sure you understand what objects are in a vba context.
If you know all this already, or just want to get to the meat of class modules you can skip to the Class Modules section here.
Here we are going to discuss manipulating Excel's own built in objects, Microsoft would have created these using something similar to class modules "under the hood" (most likely in C++).
The simplest way to understand Object Orientation is to use the analogy of delegation in the workplace.
If you want to get something done, you have the choice of doing it yourself or getting someone else to do it.
Lets say you can get a member of your staff or yourself to write a report on a particular subject. You could …
Do The Report Yourself
This is the coding equivalent of procedural – non “object oriented” programming.
First you have to do the necessary research, then open up your word processor, get a cup of strong coffee (optional), type out your report then figure out how MS Word styles and tabs work, so that your word processed document does not look like a load of junk.
Get Someone Else To Do The Report – i.e. Delegate It.
Now this is the coding equivalent of object orientation.
This is achieved by saying “Bob, I need the report by 5pm tonight, please”.
Then magically the report appears by 5pm without any more input from you.
Thus you are freed up to do other tasks.
So VBA is designed to control Microsoft Office applications using the second, “delegate the job to someone else” method.
Lets take Excel as an example.
VBA sees Excel as an object or “thing” that can be manipulated by sending it instructions.
You the programmer do not need to understand how the instructions are implemented.
That is to say you don’t need to see the code “under the hood” for that.
Lets take a simple Excel spreadsheet that we wish to write the textual string, “objects are great” into cell “B6”, we will assume that we are going to write to “Sheet 1” of the spreadsheet (workbook to be precise), and that there are currently two Excel files open and we want to write to the second one.
We could say this in plain english as …
Hey Excel put “objects are great” into Cell B6 of the first sheet of workbook 2 of your open workbooks.
Code is…01 Workbooks(2).Worksheets(1).Range("b6").Value = "objects are great"
You might also want to say this to Excel…
Hey Excel, I want to write “objects are great” to Cell “B6” but I also want the “B6” range object to hang around for a while because I may have some other changes to make to it later.
In this case let’s create an object called rngBeeSix (I know, I need to get out more…).
01 Dim rngBeeSix as Range 02 set rngBeeSix = Workbooks(2).Worksheets(1).Range("b6") 03 rngBeeSix.Value = "Objects are great"
Depending on the scope of rngBeeSix, it will “hang around” for as long as you wish.
This is the essential difference between OO programming and procedural.
Here you created an object or thing which in this case was the reference to cell B6, which you can issue instructions to at any time.
For example later on we could make the font of cell “B6” bold with the command…
01 rngBeeSix.Font.Bold = True
The big idea here is that you are not emulating user keystrokes in Excel or Word for that matter but rather with Excel’s objects you are telling them the result that you want, eg make cell “B6” bold and the cell or range object just does it.
All it’s implementation code is hidden in the class behind the scenes.
Class Modules
Just like Excel has it’s own built in objects such as worksheet and range to name a few, which are based on built in classes, you can create custom objects of your own use using Class Modules.
In VBA a class module is used to create an Object.
The class has the same relationship to the object as an Architectural Plan has to a building, it describes the building, but it is not the building, and in fact the plan could be used to create several clone buildings.
A class is used to create an object, so what is an object in VBA code?
Well it looks like a variable however it’s actually a mini program and that is where it’s power lies. Let’s take a real world example, say for some reason you want to model the characteristics of various car brands and models in VBA.
Well what do we know about cars?
In general a car has the following properties:- brand, model, horse power, colour, number of doors, fuel type etc.
It also has the following verbs associated with it, for example accelerate, brake, apply handbrake, start engine, stop engine, turn etc, the technical term for these in object oriented programming are “methods”.
Methods are functions or subroutines in class modules and Properties are variables that hold descriptions such as “FIAT” or “FORD” or “BMW” for the “brand” property.
Lets Create A Class To Describe A Basic Car
In the Visual Basic For Applications IDE (Alt+F11 gets you there from Excel), choose “Insert” then “Class Module” (Figure 1).
Name the class clsCar, do this by clicking Name in the properties window then typing.
Your Visual Basic For Applications IDE should look like Figure 2 now.
Double click on clsCar in the project window this will load the empty class module into your editor.
We now need to add code to flesh out the class module.
Type the code below into the editor, don’t type the line numbers as those are just for reference in this post, you code will not work if you include them.
01 Option Explicit 02 03 ' These are properties 04 Public Brand As String 05 Public Model As String 06 Public FuelType As String 07 Public EngineSize As String 08 Public Doors As Integer 09 10 ' These are methods 11 Sub StartEngine() 12 ' Code for Start Engine Goes Here 13 MsgBox "Engine Start" 14 End Sub 15 16 Sub StopEngine() 17 ' Code for Stop Engine goes here 18 MsgBox "Engine Stop" 19 End Sub 20 21 Sub Accelerate() 22 ' Accelerate code here 23 MsgBox "Speed Up" 24 End Sub 25 26 Sub Brake() 27 ' Brake code here 28 MsgBox "Braking" 29 End Sub
This code is a simple visual basic class that represents a car.
What this means, is that that exact code could be used to create any number of car objects all with the same or different characteristics.
So you could create a BMW 320i, a Ford Focus etc.
For the sake of argument, you could have another module that calculates tax liabilty based on the specifications of these cars.
Imagine you had a database holding details of your company car fleet, or you are a second hand car dealer and you want different metrics on your stockholding.
Well you could have code that would loop through your database and for each item fill out the information into objects created from this class (which was written once and is going to be reused with each iteration of the loop).
Get VBA Class Module Spreadsheet Here
Also get updates when new blog or video tutorials are released.
Create Standard Module To Run Objects
Now in order to make use of this class “clsCar” we need to insert an ordinary module into your VBA project, refer to Figure 1 again, it is above “Class Module” in the drop down menu.
I suggest you name this module “mMain”.
Figure 4
01 Option Explicit 02 03 Sub TestCars() 04 Dim myChrysler As clsCar 05 Dim myFord As clsCar 06 Dim SistersRangeRover As clsCar 07 08 Set myChrysler = New clsCar 09 With myChrysler 10 .Brand = "Chrysler" 11 .Model = "Voyager" 12 .FuelType = "Diesel" 13 .EngineSize = "2.8 Litre" 14 .Doors = 5 15 End With 16 17 Set SistersRangeRover = New clsCar 18 With SistersRangeRover 19 .Brand = "Land Rover" 20 .Model = "Range Rover" 21 .EngineSize = "3 Litre" 21 .FuelType = "Diesel" 22 .Doors = 5 23 End With 24 25 ' These print results to "immediate" window 26 ' (menu View->Immediate Window or "Ctrl+G") 27 Debug.Print "Fuel Type: " & myChrysler.FuelType 28 Debug.Print "Brand: " & SistersRangeRover.Brand 29 30 myChrysler.StartEngine 31 myChrysler.Accelerate 32 SistersRangeRover.StartEngine 33 SistersRangeRover.Accelerate 34 35 End Sub
Now once the empty Module has been added to your VBA Project, type in the code to the right.
Here between lines 4 and 6 we define three car objects based on the car class “clsCar”.
On line 8 we instantiate the first car object “myChrysler”, this means a myChrysler object is created in computer memory based on clsCar.
Lines 9 through 15 fill out the details of the myChrysler object.
Similarly Lines 17 through 23 fill out the details of the SistersRangeRover object (yeah my sister has one).
Point is as each detail is being filled out the code is running through “clsCar” and putting the results to a different part of computer memory, thus several objects can be created from the same code class.
Now we are going to run this code in “Debug Mode”.
Place your cursor on line 4 or 5 or anywhere within the routine “TestCars” (It does not matter where exactly), then Press the “F8” key or choose “Debug” from the VBA IDE menu then “Step Into”.
Continue pressing “F8” until you reach line 30.
Figure 5
The results of Lines 27 and 28 in Figure 4, the debug.print statements can be seen to the right in the immediate window i.e Fuel Type and Car Brand.
The yellow highlight indicates that the debugger is stopped on that line and has yet to execute it.
Lines 30 through 33 (Figure 4) show us executing “methods” of the two objects, again these are functions or subroutines and are thus mini programs themselves.
Figure 6
Examine Objects In Memory
We can examine the objects in memory if we look at the “Locals” window in the visual basic ide.
So the Locals window in the VBA IDE is really useful, it enables you to see the contents of all variables that are in the current scope.
You can see that the memory variables created for “myChrysler” and “SisterRangeRover” have values for all properties that were populated.
You can also see that a memory variable was created for myFord, however as it was not instanciated with the line…
Set SistersRangeRover = New clsCar
it is thus an empty memory variable i.e. it is set to “Nothing”.
Step Through The Final “Methods”
The final 4 lines of code on lines 30 through 33 (Figure 4) of module “mMain” show us calling various “methods” or verbs of the objects.
StartEngine, Accelerate, Brake and StopEngine.
The interesting thing is that if you continue stepping through the code by pressing the “F8” key, you will see that regardless whether you are stepping over myChrysler or SisterRangeRover the same code module clsCar is executed.
This is true for for all code in mMain that calls on clsCar
Object’s enable you to manage complexity, because if you can picture real world problems as objects, you can break the problem down into small chunks (of code and class modules) and each chunk communicates with the other and only has to be written once.
For example an air traffic control system could have a class called Aeroplane with properties such as speed, height and heading and methods such as Climb and Descend etc
These would perhaps interact with another class whose job was to manage all these objects and make sure that none of them come within a certain distance of each other.
In my opinion this can only be done with Object Oriented programming.
I hope this was helpful please leave comments below, it helps me improve the quality of my articles.
Thanks a lot. After reading this blog my doubts on Class module is gone. I’m crystal clear now.
Hi, Fantastic!!! During the last 8 years every now and then I have been trying to get involved with OOP.
I’ve clicked – thank you – really well done.
Hi Craig
Glad I could help.
Fantastic! This article should be seen by more people! Instructions are clear and concise. You are great! Thanks for sharing. I will definitely be sharing with others.
Hi Candance
Thanks for your kind words.
Thanks for wonderful elucidation on class. I am thankful to you if you explain property let, property get and property set.
Amazing. Efficient. From your example, I learned OOP in less than an hour, after I spent a day to learn OOP from a thick VBA Programming book. Thanks a lot.
You’re welcome.
I find a lot of programming books go into way too much detail which gets in the way of the learning process.
Brushing back up on OOP.. Nice example to get my memory working again! Thanks Sean!
Your welcome Carl, thanks for commenting
Thanks a lot for providing us with such a powerful resource……
Hi Samson
Glad I could help.
Great job! It was very nicely done. Your are a great Instructor.
Sean, I just found you on the INTERNET. I’m a retired accountant and have been studying Access VBA on my own. I was seeking instruction on the creation and use of Class Modules and found your tutorial video by chance. Looks good. I’ve worked my entire career developing Excel worksheets for clients who, quite frankly, didn’t have a clue. I hope to be a regular here. Thank you!
I’ve entered and executed the code as you’ve presented here several times and without any problems. However, I am now working on understanding a particular functionality. I’m sure this will clear for me as I get more used to using Classes in my DBs.
The Class Module I understand. I find the Properties more useful than the Methods right now, since my goal is to store certain strings which I can restore to a series of Access Form TextFields which, when I return to it, have been rendered blank by program flow. I don’t always need to have these data restored, only if I opt to occasionally return to a particular form to revise what I’ve written. I’m composing HTML strings, writing them to an .html text file and then Browsing to another Form which displays the .html file in a WebBrowser Control — showing the actual display output of my HTML. At times, I may want to return to the original HTML and revise it, having seen the output. Here is where I want to return to the HTML composition form and see the original array of strings containing the HTML, tags and all. What we are building is an Unsorted List from a group of text boxes that will compile as Line Items for the list.
What I’m not getting is where to I place the code that you have in the Standard Module “mMain.” In the sample, you are using “F8” to execute the code. It seems to me that I’m more apt to initiate this code by clicking a button — say “restore fields.” I’m going to try placing the Set statements in the Form’s module at the Button’s Click Event. Thanks for allowing me to think out loud.
HI Greg
In a nutshell mMain is a standard module rather than a class module, so you could say the class module provides the service and the standard moudle is a customer of that service.
Class modules are just templates, that is to say that they do not become objects until they are called by (instanciated) by the standard moudule as in the example you refer to.
So for example you could have a “Currency” class module and from that you could create dollar, sterling, yen and peso objects from that one module.
To your specific thinking out loud question If you were using Excel, you could have a button on a spreadsheet, which calls a subroutine in a standard module, this subroutine then instanciates the class, i.e contains the code that calls the class module as per examples in the tutorial above.
In access you have multiple ways of doing this, however in all cases the class must be called from other code such as a button click event etc.
Hope this helps.
Sean, a general question. What happens in Access if you do not type “Private Sub. . .”, but only “Sub. . .” ?
Private sub is all about code organisation, If you make a subroutine or a function private, then it can only be used from within that module.
This is a very good thing, because if you organise your projects properly i.e. have plenty of sensibly named modules and functions you then know which functions or subs are part of the interface of your project and which parts are internal to the module.
For example if you had a module called mCustomerManagement you might have a function called addCustomer, well part of the implementation of the addCustomer operation might be to check if the customer is already in the database.
When you call the addCustomer function from a different module the addCustomer function might call another function called customerExists() or getNewCustomerID(), these functions ought to be private because they are part of the addCustomer operation.
Making them private also tells you the developer or future developers on your project that the important function is addCustomer() and the private functions are part of the internal operations of that module.
Just like a car has accelerator, steering wheels etc in the cabin, but spark plugs remain in the engine compartment (If you car is from the 70’s or early 80’s anyhow 😉
just started programming from your tutorials and its getting great.
what does the word “embedded” mean in programming
Hi Sean,
By far the clearest and simplest explanation on class modules that i have come across. For years I avoided classes as i had found the literature on it to be extremely obtuse and uninviting. Thanks to you i’m raring to write my first class module. Much appreciated.
Dear Sir,
Thank you so much!!! you open the window to the world of programming in such an easy to understanding manner.
I will always be grateful to you.
With Warm Regards,
Keshav Murthy
Thanks for the feedback, Keshav
you have explained very well.
Before reading your article, I was confused about classes and objects. But now everything seems simple and crystal clear. Thank you a lot!
Hi Di
Glad I could help.
The best explanation about class Module, Very clear.
Thank you very Much Sir
Now I finally understood.
Very briefly but clearly explained.
Simple and crystal clear.
Thanks.
Now I finnally understand classmodules thanks to you good explanation. I have different things about it but it was never clear to me to use this. Now I have
Thank you for your simple example. I tried it and it worked great. I have not programmed much in several years but would like to code a project in Excel using object oriented code versus procedural code. The problem that I am having is that I want to create the objects at the beginning of my program and then utilize them throughout. When I used your example, I am able to find the objects inside the subroutine that they were created in but not outside of that subroutine. I have tried any number of changes to make the object public without success. Any assistance is greatly appreciated. My test adds a step only your code as follows:
clsCar in Class Module:
Option Explicit
Public Brand As String
Public Model As String
Public Sub StartEngine()
MsgBox “Engine Start”
End Sub
Public Sub StopEngine()
MsgBox “Engine Stop”
End Sub
Creating the Objects in the mMain Module:
Public Sub TestCars()
Dim myChrysler As clsCar
Set myChrysler = New clsCar
With myChrysler
.Brand = “Chrysler”
.Model = “Voyager”
End With
MsgBox “Test inside the creating subroutine: ” & myChrysler.Brand ‘(this works!)
myChrysler.StartEngine ‘(this works!)
End Sub
Second subroutine in the mMain Module:
Public Sub TestCars2()
MsgBox “Test outside of the creating subroutine: ” & myChrysler.Brand ‘(this cannot find the myChrysler object)
myChrysler.StopEngine
End Sub
Don
To make your objects have project level scope what you need to do is declare them at the top of your standard module “mMain” in my example.
So for example in the mMain module …
Option Explicit
Public myChrysler as New clsCar
Or
Public myChrysler as clsCar
and in an initialisation routine
set myChrysler = new clsCar
Either of the two above examples will allow myChrysler to be accessed throughout your project.
Hope that helps
Great article, i’m happy to found it. Thanks a lot!
Your Welcome, Jose
Hi sean,
can you get some samples on VBA speech recogontion (speech to text)
regards,
Sesh
Not something I’ve ever done, and not on my bucket list at the moment, 🙂
If I have a module that assigns values into my class, how do I use those values in different modules of my project?
The class i.e. object would either have to be a global variable (I don’t tend to like that) or passed around as an argument to routines in the different modules.
That means you have a main routine that may have the object and you call those modules from that main routine.
Hi Sean,
Thanks for putting this together – I’m an experienced OOP programmer in other languages and wanted to apply the same principles in VBA to expand my skill set into MS products. As such, I found your explanation refreshingly practical.
One thing I found that beginners may (or may not) find a little confusing is in the *Examine Objects In Memory* section, specifically the reference to “SistersRangeRover”: “You can also see that a memory variable was created for myFord, however as it was not instantiated with the line…”
*******************************************
* Set SistersRangeRover = New clsCar *
*******************************************
it is thus an empty memory variable i.e. it is set to “Nothing”.
Perhaps if the code example in the little box used “myFord”, it would be a clearer example:
*******************************************
* Set myFord = New clsCar *
*******************************************
Not trying to be pedantic or detract from your great post, however I recall decades ago when I was first learning OOP, little things could mislead.
Keep up the good work!
Cheers,
Rich.
Hi sean,
This is the first great simple and understandable explanation of Class Module in VBA that I ever read before, You showed me the way.
regards,
Nader
You’re welcome Nader
Great video on class modules Sean. Brand new for me but you put it in a simple way that I could understand. Up to now never quite understood ckass modules so hopefully I can build from here. Thanks
Thanks for the feedback Richard
I have been stucked in class module for about a week. Have read so many website pages about class modules. And this is the only website where i feel like getting clear about classes. Great job and thanks Sean!
GBU
Glad you found it useful Yosua
Sean, I read through your OOP class module section, and while it showed how to use it, I was wondering if you could provide me with a reason why I would use a class module. Your car example, to me, could easily store the information in an Access table or an Excel worksheet. As far as uniformity is concerned, I use forms in Access to enter information and each one has required fields, can’t save a new or modified record unless all of them have a value. In Excel, if I want BeeSix to hang around, I create a range name. So, when you say that class modules are the bread and butter of OOP, I am worried that I have missed something significant or am doing something very wrong because I have never used one. Judging from your website and the replies here you know what you’re doing, so I hope you can spare a few minutes to help me out. Regards, Mike
Michael
Class modules (which are used to create objects) help simplify complex tasks, the object that the class module creates can hold both data and code, i.e. it can hold the data and perform actions on it.
You say …
>>Your car example, to me, could easily store the information in an Access table or an Excel worksheet
You’re completely missing the point here, storing the information is not the same as using it in code.
With the class module for a car I only need to write the car code once, but it can be used within a program to create thousands of different cars on the fly.
Although the car may have complex code to implement tasks such as drive, brake or turn on indicators the other code that wants to make the car object do any of those things only has to call the drive or brake function on any car object and it’s done without the programmer who is using the car class needing to know what’s going on behind the scenes.
Without classes games like fortnite, call of duty etc could not be programmed up as the complexity would be impossible to overcome.
Each weapon and item including character types would be made up of C++ classes that would be instantiated into objects during game play.