vba class

VBA Class Tutorial, Custom Classes and Objects

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 3.75 out of 5)
Loading...

Classes in at the core of all Object Oriented Programming languages. Although we can argue how much is VBA actually an Object Oriented Programming language, there is no doubt that VBA allows you to create Classes similarly as in Java or C#. But what is a Class? A Class allows you to encapsulate any level of abstraction and complexity into a single object and facilitate only an interface (certain procedures or methods if you prefer) to this object.
vba class example
Personally I am a fan of (VBA) Classes as they require abstract thinking and a modular approach to writing code (in this case VBA). If you have seen a lot of VBA code, not once were you probably horrified to see a junkyard of random Subs and Functions with no obvious relationship between them. Classes whereas “require” that you encapsulate a certain “amount of functionality” into a single object which maps similarly to the world around us which is represented by single objects which can help us yield certain results. In my opinion this is an easy concept to grasp as everyday we are dealing with individual tools and objects that allow us to achieve certain goals. Sometime we are not necessary sure how these objects work (a PC) but we know how to use their interface to achieve an objective (send an email). Let’s use a simple analogy as we move forward through this tutorial…

Other VBA custom objects

The Car – Class Analogy

One of my personal favorite analogies of a Class is the Car Class. Let us say we want to create a Class for a Car object. Now an object of this class (a Car) will have certain properties like the brand of the Car, the color, the license plate numbers, as well as will have certain behaviors (called methods in Computer Programming) like driving forward, turning left, breaking etc. See below a simple example of how this translates to a VBA Class.

car class
VBA Class: Car analogy

This approach makes it easier for someone who does not understand/know the underlying implementation to easily start using the Car Class object, as the Car object encapsulates everything that has to do with a single Car object the user can seamlessly reuse this object.

Creating a new VBA Class

Let’s start our journey by creating a new empty VBA Class. To add a new VBA Class go to the menu and select Insert then select Class Module.

Alternatively, right-click on any item in your VBA Project and select Class Module like shown below:

vba class
VBA Class: Create a new VBA Class

Classes in VBA are similar to regular VBA modules. They have their own namespace and can consist of procedures, functions, variables etc. There are other things you will find in a VBA Class, but we will get to that.

Next let’s make sure to name our Class appropriately:

vba class name
VBA Class: Name your Class

Make sure that the Class name is not identical to a module or procedure within your VBA project or this will make it complicated to use this Class.

Now you have a new empty Class. It doesn’t do much except exist. But before we put some life into it let’s test that indeed it works. I have created a simple VBA Class which I named CarClass. The example below will create the new class.

Notice that when defining the Car variable I used the Set clause. This is because each Class is an object and the Car variable contains only a reference to this object. If you are not familiar with handling VBA Objects read here. Now let’s animate our class by adding some procedures (behaviors/methods) and some properties.

Variables, Procedures and Functions

A VBA Class can contain variables, procedures and functions just like regular VBA modules. You can read more here on procedures, functions and variables.

Let’s extend our VBA Class CarClass with some additional procedures and variables common to our abstract model of a car.

We can now create a new object of Class CarClass and start working with it:

Properties – Get, Let and Set

A Class should encapsulate abstraction and complexity. With our current CarClass we don’t have much control over the values users provide for the Speed and LicensePlate variables. What if the user sets the speed to some non-sense value like 1000mph? What if we want to validate the LicensePlate before setting it? Fortunately, the VBA Class introduces Get, Let and Set procedures that address that.

  • Get – return value of the property
  • Let – set the value of the property
  • Set – set the object value of the property (if applies)

The Get and Let procedures manage the process of retrieving and defining the value of a specific Class property. The Set procedure is basically a Setter aimed at properties which are objects not native VBA data structures. Let look at another simple example with our CarClass:

Let’s replace this:

With this:

Notice the highlighted rows above where I introduced the new Get and Let property procedures. As you can see now I am getting and setting the Speed and LicensePlate value via the property procedures. This is more convenient than making a variable Public within the class, as it gives you more control on the value of the variable. Remember that a Class should encapsulate abstraction and complexity. A perfect Class object prevents the user from any restricted behaviors with this object.

Notice that you use properties similarly as you would use Public variables:

Events – Initialize and Terminate

The Class module provides 2 default event procedures Class_Initialize() and Class_Terminate():

  • Private Sub Class_Initialize() – fired when the Class object is initialized e.g.
  • Private Sub Class_Terminate() – fired when the Class object is destroyed e.g.

These 2 procedures are meant to facilitate the following objectives:

  • Initializing the Class object to prepare it for use i.e. initializing objects associated with the Class like Collections, setting default value of Class variables
  • Terminate the Class object and any objects associated with the Class i.e. free allocated memory

Let’s now extend our CarClass with a the following event procedures:

We will use the SpeedRegister to record any changes in the speed of our Car. See full code of our CarClass collapsed below:
 

VBA Code (CarClass)

 

Instancing – Private vs. PublicNonCreatable

VBA In the properties section of the VBA Class you will find a property call Instancing. You are probably wondering what this property does. There are two available options – Private and PublicNonCreatable:

vba class instancing
VBA Class: Instancing alternatives

  • Private – the Class cannot be created and referenced outside the VBA Project. It is “Private” to the VBA Project
  • PublicNonCreatable – the Class is Public and can be referenced out the VBA Project. It cannot be however created outside the project using the New clause

But why would you want to set your Class to PublicNonCreatable? Whenever you want to use it outside your VBA Project e.g. from a separate Excel, Access, MS Office VBA Project file currently open. This is especially useful if you have a Class you want to use within multiple VBA Projects and not having to copy the Class code to every project. You can consider creating a simple Excel AddIn and include the Class code in the VBA Project. If the AddIn is placed in your C:\username\AppData\Roaming\Microsoft\AddIns directory it will open automatically with your MS Office applications.

But how are we supposed to use this Class if we can create this object? We need to create a dedicated function that will return the object. Let’s make an effort now to reuse our CarClass Class within a separate VBA Project:

Add a new Module and add a function

First we need to use an existing or create a new Module in which we will place our function which initializes the CarClass :

Notice that the Class needs to be Public so it can be referenced from the other VBA Project.

Set the Class to PublicNonCreatable

Click on the Class (CarClass) and change Instancing from Private to PublicNonCreatable.

Create the Class using the defined function

You are ready to reuse the CarClass in a different VBA Project:

Dim car as Object
Set car = New_CarClass
'Use the Class normally...

That’s it!

Class Module vs. Standard Module

What is the difference between a standard Module and a Class Module? A Class instance is basically and Object and has all the properties of Objects in VBA. Here are some key differences worth highlighting:

  • There is only one copy of standard module data. A Class module, however, is created separately for each instance of the class. Hence each Class instance has it’s own set of Class variables individual to the object
  • Data in a standard module exists as long as your program scope – the life of you VBA Project. Only when you close your Excel/Access/Word/PowerPoint file will the data be lost for all global variables. In a Class module, however, data for each instance exists only for the lifetime of the object. It ceases to exist the moment the Class Object is destroyed e.g. when the Class object is set to Nothing
  • Variables declared Public in a standard module are visible anywhere in your project. However, Public variables in a Class module are only accessed if you have an object variable containing a reference to a particular instance of a class

Summary

Hopefully this tutorial has familiarized you with the VBA Class enough to start using it. As mentioned I think it is really worth making the effort to learn the VBA Class as using classes makes your code more readable and manageable. Classes encapsulate abstraction and complexity – it makes life easier when you or others need to reuse your code without necessarily needing to review the code and implementation.

Let me know what you think! Be sure to follow me on Twitter and Facebook for upcoming posts and tutorials!

Related Posts

4 thoughts on “VBA Class Tutorial, Custom Classes and Objects”

  1. Another great article! I’d only suggest, instead of making a standard module for creating Car instances, I’d make a CarFactory class with a single Create method (perhaps even give it a few parameters to initialize some properties); the CarFactory class would also be PublicNotCreatable, but then I’d export it and set its PredeclaredId attribute to True, and re-import it – this little trick basically simulates a static class, and makes client code able to to things like “Set myCar = CarFactory.Create”.

    VBA is COM, COM loves factories! Note that this also unlocks creating truly immutable types in VBA (exposing only get-only properties, with Friend setters, which are only accessible within the VBA project they’re declared in), since the factory can be effectively used as what essentially amounts to a constructor. The beauty is that is keeps the creational responsibilities inside a dedicated type, which respects the Single Responsibility Principle.

    1. Interesting idea Chris. I certainly do like the Factory pattern, and this actually sparks me an idea of a post of recommended programming patterns for VBA Devs.
      For this post however that would be too much – wanted to make it an easy introduction for someone who never had anything to do with Classes in general. Aside from a Singleton pattern others would scare my less advanced readers right away :)!

  2. Great tutorial. Just a couple of things.

    Should line 9 Private Sub Class_Initialize() read Private Sub Class_Terminate()

    And in the Instancing section I needed to add the external class add-in as a reference in the calling workbook in order to call the class. Is there another way to call an external class or is that a step needed in the above tutorial? Thanks

    1. Hi Rory,

      thanks! Corrected! For your question – Yes, you need to follow the above steps when wanting to use External VBA Classes. Unfortunately that is just how it is with VBA. Personally, I my projects need external functions/procedures I prefer to Create a C#/VB.NET COM visible DLL (compile both 32 and 64 bit) and use it from VBA. You can check out my VBA Compiler, however it does not currently support Classes.

Leave a Reply