Object oriented VBA project: how to create VBA custom classes
As you VBA application gets more complex, you will probably have to deal with very large pieces of code, spread over modules, sheets, and across several workbooks. It is often very useful to think of how you could simplify your code, and also re-use chunks of code in several projects. Functions are a great way to do this, up to a certain point. Functions have a number of limitations that make them simpler to understand and handle, but make them somewhat imperfect. Object Oriented VBA bridges this gap to perfection: they allow you to simplify, shorten and re-use your code very effectively. Let’s discover why and how you can easily use this power to your advantage!
Custom Classes in VBA are Objects, but what are Objects in VBA?
A silly object example to start with
Objects are a very important concept in computer programming. They are quite a difficult concept though, so let’s start with an analogy (you can skip this if you are confident you know what an object is). You are a real estate agent, your job is to build houses, so you design plans for a standard house. The house is designed to host one person and carry out several duties like: have that person sleep every night, clean that person, give breakfast to the person… (It is a bossy house) The house has a size, the color can vary, and the walls can be made of bricks or concrete. In programming terms, the house is an object.
The house has Properties:
- The size of the house,
- The color of the house,
- The material used for the walls
The house has Methods:
- Host a person,
- Have the person sleep,
- Clean the person,
- Clean the house
Creating an object class is like making the plans for this house which incorporate all these features, so you can easily replicate this house to construct several houses. They will come in various shapes, colors and sizes (different properties), and still have these methods that they can use independently. In nerd language, one could say that you made several “instances” of the house and gave different properties to each instance.
The analogy could be pushed further but the point here is only to make you understand that the house is an object in programming terms, it can be instantiated (replicated) indefinitely to create independent entities. It has properties that can vary among instances and can do actions on itself or on other objects using methods.
Objects are already everywhere in VBA
VBA has lots of native objects, many of which you already use, maybe without noticing it. Famous examples of native Excel VBA objects are:
- Worksheets with properties like Name, methods like Activate or Calculate
- Ranges with properties like Value or Interior.Color, methods like Select or Find
- PivotTables, workbooks, links…
So the good news is, if you have an intermediate level in VBA you already know how objects broadly work. But how can you create new objects? Let’s see this in the next section.
Object vs Class, what is the difference?
Later in this post, I will be talking about Classes but not really about objects. What? Why?! I thought this post was about objects! No, no stay, I am not wasting your time! Actually a class is the blueprint of an object, exactly as if we were creating the plans of a house like in the previous example. From this blueprint (the Class, a piece of code), you can create objects (which behave essentially like variables with super-powers). These objects are called Instances of the Class. This may not be very clear yet but do keep in mind that a class is a piece of code that describes the properties and functions of the object, and the object is a variable with these properties and functions.
Many people who are getting into Object Oriented programming (and not only in VBA) are already working with functions quite a lot and tend to oppose functions and custom object classes. And indeed customs classes can be seen as a natural evolution once you know your way into functions. But can you actually compare functions and classes?
What are the limitations of Functions vs Classes in VBA?
It is not obvious to say that classes are better than functions: it really depends in the specific situation you are in. And of course you can use both functions and classes simultaneously. They don’t have to be in competition in your code. Actually (as if it wasn’t complex enough) you can also create functions within classes!
You can't compare functions and classes
A function is great to do a simple job, use inputs and give an output. In a general way, it is great to make functions for very generic operations in your code.
A class is some sort of a bubble that embarks its own variables (properties), functions and subs (properties). Functions and Classes in VBA are different in this sense. To make a little analogy, if a function was a plane, a class wouldn’t be a larger plane but rather an aircraft carrier that embarks its own environment of planes, crews, helicopters, escort boats... So they are hardly comparable! but you already see in what way the custom class is more satisfactory for a programmer.
But classes allow to better structure your code
In practice indeed, classes allow to structure your code much better than when you only use procedures and functions. This is really the advantage of the custom objects classes. Lets say you need to generate several reports from an excel file, but they share similar features so you can actually write one class for all of them. Let us imagine this cReport class. Basic VBA programming would be enough to write a big procedure that imports data to the reports, formats them and then saves them. This code can be copy-pasted and slightly modified to save properly all the reports with one (huge) procedure. It is long, durty and hard to read.
Now just think that after writing the cReport class you could replace the inital code with:
Set Report1 = new cReport
Set Report2 = new cReport
Doesn't it look neat? It does!
You can always find excuses to not program in an Object Oriented way. You should try on this next macro you need to do! it may be a bit hard at first and then you will love it!
VBA also provide custom variable types, that can be a great bridge to get used to some Object Oriented concepts!
Let’s try to actually create and use one custom class one! Brace yourself!
Create new object classes in VBA
First, create the required modules in your VBA project:
- Create a Class module
- In the property window of this module, set the name of the Class
- In the Class module, write the code defining your Class of object
Then Within the Class module:
A) Declare Public and Private variables
These are the variables that will be used inside the object class, without necessarily be accessible from outside the object. For example the Height, Surface, Market value of the House object class…
B) Optional: Write a Sub class_initialize() procedure
It will run when the object class is “instantiated” (when an object is created from the Class).
This is extremely useful to set initial values for the variables you will be using later. It can really do anything that you believe necessary when the object is created. It can be seen as an event that triggers when you declare a new object from this class:
set ObjectTest = new ClassTest
C) Write Property Let and Property Get functions
These are special “functions” of the class that are designed respectively to assign a value to one of the object variables or to retrieve the value of one of the object variables. Actually they are not strictly speaking “functions”, I just called them like that because their behavior is somewhat similar. But one very important difference is the way they are being used in the object.
In the class module “cTest”:
Private Test as integer
Property Let LetTest (value as integer)
Test = value
Somewhere in a module or a sheet object:
Set oTest = new cTest
MyObject.LetTest = Value
D) Write functions and procedures linked to your object
You can also assign Sub and Functions to an object class that can be used only when the object exists (ie. it is instantiated).
Let us elaborate practically on the House object example. Here is the code to be found in the Class Module cHouse:
' Declare the variables that will be used inside the object
' a. Declare Public and Private variables
Public BuildingMaterial As String
Public Height As Integer
Public Surface As Integer
Private RoomColor As String
Private RealEstateValue As Currency
' b. Optional: Write a class_initialize() procedure
Private Sub class_initialize()
' This procedure triggers when the object is created
' We use it here to give inital value to the object variables
Height = 5
RealEstateValue = 10
Surface = 50
BuildingMaterial = "Concrete"
RoomColor = "Blue"
' c. Write Property Let and Property Get functions
Property Let Set_Height(newHeight As Integer)
' Increasing the height will increase the value of the house proportionally ;)
RealEstateValue = (newHeight / Height) * RealEstateValue
' More importantly, the new height is assigned to the house
Height = newHeight
Property Get Get_RealEstateValue()
' This is the simplest property get function you can imagine
' It just sends the value of the RealEstateValue variable
Get_RealEstateValue = RealEstateValue
' d. Write functions and procedures linked to your object
Public Sub Clean_Repair()
RealEstateValue = RealEstateValue * 1.05
' Cleaning and repairing the house will increase its value by 5%
Public Function PresentValue(NumberOfYears As Integer, _
Optional InterestRate As Single = 0.05) As Currency
PresentValue = Me.Get_RealEstateValue / ((1 + InterestRate) ^ NumberOfYears)
Use your custom VBA object
The previous chunk of code won’t do anything by itself: you still need to use it. Unlike a function that you can use seamlessly, you need to "instantiate" the object class that you created. This nerdy word only means that you need to create a variable that contains your custom class. This process is very similar to setting a variable. It is done by using the instruction Set. You actually use Set whenever you declare an object variable, be it a custom class object or a native VBA object (worksheet, workbook, range…).
Full instruction to instantiate your cHouse class:
Dim HouseObject as Object
Set HouseObject = New cHouse
Alternative way of doing it:
Dim HouseObject as New cHouse
You can have a look at our pompous nerdy foot note below if you are interested in the difference between these two methods (Early vs Late binding). In our case however, it is fine if you remember that it doesn’t make any serious difference.
Here is the full code to be found in the Module TryMyNewHouse:
Dim MyHouse As Object
Set MyHouse = New cHouse
MyHouse.Height = 5
' It is possible to set the property Height without using
' the property let function Set_Height, as it is a Public variable of the object
MyHouse.Set_Height = 10
' We update the height using the property let function Set_Height
' The previous line works, returns the updated height
' This is because the property RealEstateValue is Public
' The previous line throws an error and doesn't run
' with error message "Object doesn't support this property or method"
' This is because the property RealEstateValue is Private
' Instead using the property get function Get_RealEstateValue gives us the right result
Pompous nerdy foot note:
The first option is called Late binding and the second is called Early binding. They will likely do close to no difference in the case of custom classes but they can be interesting notions to explore when you are dealing with more complex and native VBA objects. If you are advanced enough in VBA programming you may know though that Early Binding of an exotic VBA object (Outlook emails, ADODB recorset, HTTP connections, Word application…) will require that you add the reference to this library in the VBA project, while Late binding doesn’t require that, and the reference will be create dynamically when you use the CreateObject or GetObject function… Interesting, no?
- For late vs early binding : http://mhubiche.developpez.com/vba/fiches/comprendre/binding/ (French)