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