Tag Archives: class

VBA Class Tutorial, Custom Classes and Objects

1 Star2 Stars3 Stars4 Stars5 Stars (11 votes, average: 4.27 out of 5)

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.

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 :

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


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!

VBA Dictionary – Using the VBA Dictionary. Key Value pairs

1 Star2 Stars3 Stars4 Stars5 Stars (15 votes, average: 4.60 out of 5)

Visual Basic for Applications (VBA) facilitates many useful data structures – its not just VBA Arrays you can use. COM libraries provide additional data structures such as the VBA Dictionary, ArrayList, Queue, Stack or SortedList.
But before we jump into explaining how these more complex data structures work and how to use them, let’s start with the basics – VBA array. Often you needn’t reach out for more complex structures, so it’s important to understand first the limits of VBA Arrays.

VBA Dictionary example

VBA DictionaryThe VBA Dictionary is probably the second most often used data structure in VBA. Dictionaries are great at storing key-value pairs of data similarly as the VBA Collection object does.

The VBA Dictionary, however, offers some functionality that is not available with the VBA Collection object e.g. the option to specify a comparison method for Keys, which I will demonstrate below. This allows for a case-sensitive Key.

Ok, so let’s start with a simple example of how the VBA Dictionary can be used.

VBA Dictionary Late Binding

VBA Dictionary Early Binding

In case you prefer to want to declare variables as the Dictionary object you need to reference the Microsoft Scripting Runtime library. To do this go to the Tools menu and select References. From the pop-up window scroll down and select the library mentioned above. This will allow you to use the VBA Dictionary like this:

Both methods are ok with the difference that with Early Binding if you share your Excel file with another user he/she may have to first reference the Microsoft Scripting Runtime otherwise will be getting errors.

Hence I personally prefer Late Binding limiting any necessary actions from anyone I would be sharing my files with.For learning purposes, however, it may be easier to reference the library and use Early Binding allowing you to see the properties of the Dictionary using the suggestions (CTRL+Space).

Traversing items and keys

Below methods for traversing either all keys or items in a VBA Dictionary

Removing items

You can remove either a single specific item by key using the Remove property or RemoveAll to remove all items in the Dictionary:

Similar data structures

  • Hashtable — similar to the Dictionary class. Represents a collection of key/value pairs that are organized based on the hash code of the key. If you want to learn when it’s efficient to use Hashtables read here. More here.
  • SortedList — Similar to the Hashtable. Represents a collection of key/value pairs that are sorted by the keys and are accessible by key and by index. More here.

Creating your own structures (VBA Class)

If none of the above data structures are suitable for your needs you can always create your own VBA Class. A Class is a VBA Module that can be instantiated as an object (similarly as the objects above), have its own properties etc. Classes are a good way of encapsulating abstraction and complexity.

Alternatively, consider also a stack of any of the mentioned data structures. For example let’s say we want to manage a structure which would store words in different languages. Later we will want to quickly check if there is a word in a certain language in our multi-dictionary. How to do this? A Dictionary of Dictionaries should do the trick. The first Dictionary will have keywords mapping to different languages. The keyword will be e.g. “English” while the value will be an object of type Dictionary in which you can store the word in the “English” language. Cool right?