VBA Class Module Example – Better VBA With Objects

How to use Class Modules in Excel VBA, check out the video tutorial then the article for some extra depth.

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 …

  1. 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.

  2. 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.

Figure 1

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).

Figure 2

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.

Figure 3
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).

Sign up below and get notified when new Business Programmer blog or video tutorials are created.

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 objectes 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.

Sign up below and get notified when new Business Programmer blog or video tutorials are created.

Free Email Updates Get the latest content first.

Thanks for reading this post

If you have any queries or comments please leave them below as they help to improve the quality of the information that I provide.

If you found this article useful then don't forget to share it by clicking on one of the social media buttons below.

18 Comments

  1. Veeresh Kumar

    June 15, 2016 at 10:40 am

    Thanks a lot. After reading this blog my doubts on Class module is gone. I’m crystal clear now.

  2. Candance Ratcliff

    January 15, 2017 at 2:10 am

    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.

    • Sean Johnson

      January 27, 2017 at 1:06 pm

      Hi Candance

      Thanks for your kind words.

  3. T Appa Rao

    January 15, 2017 at 4:45 am

    Thanks for wonderful elucidation on class. I am thankful to you if you explain property let, property get and property set.

  4. Young Lee

    February 16, 2017 at 4:00 pm

    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.

    • Sean Johnson

      February 16, 2017 at 4:10 pm

      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.

  5. Carl

    March 17, 2017 at 11:34 pm

    Brushing back up on OOP.. Nice example to get my memory working again! Thanks Sean!

    • Sean Johnson

      March 21, 2017 at 12:02 pm

      Your welcome Carl, thanks for commenting

  6. Samson Tshuma

    March 23, 2017 at 8:13 pm

    Thanks a lot for providing us with such a powerful resource……

    • Sean Johnson

      April 6, 2017 at 9:46 am

      Hi Samson

      Glad I could help.

  7. John Davy

    May 17, 2017 at 12:38 am

    Great job! It was very nicely done. Your are a great Instructor.

  8. Greg Clancey

    June 2, 2017 at 9:31 pm

    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!

  9. Greg Clancey

    June 3, 2017 at 9:06 pm

    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.

    • Sean Johnson

      June 6, 2017 at 2:04 pm

      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.

  10. Greg Clancey

    June 3, 2017 at 9:12 pm

    Sean, a general question. What happens in Access if you do not type “Private Sub. . .”, but only “Sub. . .” ?

    • Sean Johnson

      June 6, 2017 at 1:52 pm

      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 😉

  11. samba

    September 27, 2017 at 5:26 pm

    just started programming from your tutorials and its getting great.

  12. samba

    September 27, 2017 at 5:46 pm

    what does the word “embedded” mean in programming

Leave a Reply

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