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.
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…
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
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.
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.
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
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
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...
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
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!