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
Report1.import
Report1.format
Report1.save
Report2.import
Report2.format
Report2.save

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

Create a new Class Module

Rename the class module because this will be the name of your new class

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 that 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

End Property

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"

End Sub

 

'' 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

End Property

 

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

End Property

 

'' 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%

    ' AMAZING

End Sub

 

Public Function PresentValue(NumberOfYears As Integer, _

        Optional InterestRate As Single = 0.05) As Currency

    PresentValue = Me.Get_RealEstateValue / ((1 + InterestRate) ^ NumberOfYears)

End Function

 

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:

Sub test()

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

MyHouse.Clean_Repair

MsgBox MyHouse.Height

'' The previous line works, returns the updated height

'' This is because the property RealEstateValue is Public

'MsgBox Myhouse.RealEstateValue

'' 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

MsgBox MyHouse.Get_RealEstateValue

'' Instead using the property get function Get_RealEstateValue gives us the right result

MsgBox MyHouse.PresentValue(NumberOfYears:=10)

End Sub

 

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?

 

Refrence:

  • For late vs early binding : http://mhubiche.developpez.com/vba/fiches/comprendre/binding/ (French)
Matthieu Liatard
Remember this!
Se souvenir de cela
Play!
Tester mes connaissances

Bonjour :)
Avec Question-Player, apprenez de nouvelles choses et n'oubliez plus rien. Jamais. Et en plus c'est gratuit.

Pour nous aider, vous pouvez créer un compte:

Login / Créer un compte

Articles similaires