VBA Class Tutorial

The VBA Class allows you to define your own objects with unique properties and methods in VBA. 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.

Head First Object Oriented Analysis and Design — Best book to learn OOP concepts (link to Amazon)

Why use Classes?

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

VBA Class example

Before I explain more let us see a very simple Class example. To create a class insert a “Class” module to the “Class Modules” folder:

Next I inserted some example code to my class below and renamed my class (in the Properties panel) to MyClass.

Public name As String

Public Sub Hello()
    Debug.Print "Hello my name is " & name
End Sub

If you want to use your class you can create a simple VBA Sub in any module:

Sub Main()
  Dim class as New MyClass
  class.name = "John"
  class.Hello
End Sub

Output:

Hello my name is John

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.

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:

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:

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.

Sub TestCarClass()
Hello

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.

Public Speed As Integer
Public LicensePlate as String

Sub DriveForward()
    Speed = IIf(Speed < 0, -Speed, Speed)
End Sub

Sub DriveBack()
    Speed = IIf(Speed < 0, Speed, -Speed)
End Sub

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

Sub TestCarClass()
    Dim Car As CarClass
    Set Car = New CarClass
    Car.LicensePlate = "34344W"
    Car.Speed = 100 'set speed to 100 mph
    
    Car.DriveBack 'set speed to -100 mph

    Car.DriveForward 'set speed to 100 mph
End Sub

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:

Public Speed As Integer
Public LicensePlate as String

With this:

Dim vSpeed As Integer
Dim vLicensePlate As String

Public Property Get Speed() As Integer
    Speed = vSpeed
End Property

Public Property Let Speed(sp As Integer)
    vSpeed = Application.WorksheetFunction.Min(sp, 100)
    vSpeed = Application.WorksheetFunction.Max(vSpeed, -100)
End Property

Public Property Get LicensePlate() As String
    LicensePlate = vLicensePlate
End Property

Public Property Let LicensePlate(lp As String)
    If Len(lp) <> 6 Then Err.Raise (xlErrValue) 'Raise error
    vLicensePlate = lp
End Property

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:

Dim car As CarClass
Set car = New CarClass
car.Speed = 10
Debug.Print car.Speed '10
    
car.Speed = 12345
Debug.Print car.Speed '100
    
car.LicensePlate = "123456"
Debug.Print car.LicensePlate '123456
    
car.LicensePlate = "1234567" 'Error!

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.
```
Set car = New CarClass
```
Private Sub Class_Terminate() – fired when the Class object is destroyed e.g.
```
Set car = Nothing
```

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:

Dim SpeedRegister As Collection

Private Sub Class_Initialize()
    Set SpeedRegister = New Collection
    vSpeed = 0 'Set speed to 0
    vLicensePlate = "XXXXXX" 'Set unknown License Plate
End Sub

Private Sub Class_Terminate()
    Set SpeedRegister = Nothing
End Sub

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

Dim vSpeed As Integer
Dim vLicensePlate As String
Dim SpeedRegister As Collection

Public Property Get Speed() As Integer
    Speed = vSpeed
End Property

Public Property Let Speed(sp As Integer)
    vSpeed = Application.WorksheetFunction.Min(sp, 100)
    vSpeed = Application.WorksheetFunction.Max(vSpeed, -100)
    SpeedRegister.Add sp
End Property

Public Property Get LicensePlate() As String
    LicensePlate = vLicensePlate
End Property

Public Property Let LicensePlate(lp As String)
    If Len(lp) <> 6 Then Err.Raise (xlErrValue) 'Raise error
    vLicensePlate = lp
End Property

Sub DriveForward()
    Speed = IIf(Speed < 0, -Speed, Speed)
End Sub

Sub DriveBack()
    Speed = IIf(Speed < 0, Speed, -Speed)
End Sub

Private Sub Class_Initialize()
    Set SpeedRegister = New Collection
End Sub

Private Sub Class_Terminate()
    Set SpeedRegister = Nothing
End Sub

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:

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 :

Public Function New_CarClass()
  Set New_CarClass = New CarClass
End Function

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!

VBA 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

Conclusions

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.

If you feel you mastered Classes in Excel macros do check out my VBA Implements Tutorial as well!

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

Related posts: