VBA Class: Introduction


You want to use classes in VBA? Seriously?

Let's be clear from the start: VBA is not the most advanced programming language in the world.

There are many programming features that VBA does not support, and that are considered common good in other programming languages. For instance, there are no namespaces, there is no class inheritance, building interface classes is not possible, operator overloading does not exist, ...

However, VBA does support classes.

And classes, even in their basic VBA form, are simply wonderful.

Explaining classes...

Here is the strange thing.

While we ourselves are deeply convinced that classes are absolutely helpful for writing good code - in whatever programming language that supports them - it is surprisingly difficult to irrefutable argument why exactly this is the case to a counter party that is slightly resistant. For instance, try to read the Wikipedia page on classes.

Image showing a the wikipedia page for class

Did this convince you?

While - being relatively experienced programmers - we can understand what is written there, and, through the academic terminology, see the practical value of it in our own coding projects, we highly doubt that any beginner, after having read this, will say "Yes, this has clarified a lot. Classes are obviously what I need to invest my time in." On the other end of the spectrum, it is equally hard to imagine that an experienced procedural programmer - as there are many in VBA - will, after having read this, decide spontaneously to move away from his (or her) old proven habits and start experimenting with classes.

Still, the fact remains that classes really are great.

What follows, is our go at explaining why classes - in VBA - are worth considering.

What are classes?

Classes are datatypes.

As with any other datatype, you can declare variables of that type. For example,

As a class is a reference datatype, you need the second line. The expression Set ... = New ... creates an instance of the class. After the second line, you can start using the variable.

Two things are special about classes.

  • First, the datatype can contain variables of any other datatype, also other classes.
  • Second, the datatype can also contain routines. Most of the time, these routines do something with the variables in the datatype.

Here is a visualization.

Image showing basic class

Classes can have variables, and no routines. Classes can have routines, and no variables. Most of the time though, they will have both.

Image showing the three basic class types

Why should you use classes, even in VBA?

Because classes help you to organize your code, and, your thoughts while coding.

By 'code', we mean both variables and routines.

Image showing the three basic class types, type 1

It is often stated that 'classes make you think in terms of objects'. While this is true, we doubt whether everybody will understand why this is important. Especially as you have to let go the idea that the objects need to be real life objects. Many times, the objects are abstract, like a logger, a connection manager, a request, a profile, an interpollator, a service reply, a data matrix, etc. Thinking in terms of objects, for us, comes down to organizing your thoughts while coding, assuming your main coding thoughts focus on how to partition your big coding problem into smaller, more manageable coding problems. Each smaller coding problem will potentially reduce to declaring some variables and writing some routines. Hence, potentially, each smaller problem can be solved by building a class.

Image showing a problem solving approach

Classes, when well designed and built, are black boxes: self-contained pieces of code that will solve small coding problems for you. Once built, you don't have to worry anymore about how it works but, rather how to use it (the interface). The latter is usually a lot simpler than the former. As such, classes help you to focuss your thoughts on the organisation of your code. Well organised code will usually be easier to understand, easier to maintain and easier to adjust. Noone likes spaghetticode. Classes helps you to avoid spaghetticode because they force you to focus on a very powerful organisational idea: divide and conquer.

Black box example 1

As an example of the black box idea, take the VBA cosine function - which is not a class by the way.

The function Cos is a handy black box. It is making a complex calculation - you probably don't want to dive into the advanced mathematics and the implementation details - while being very simple to use. Note that the only data it uses, the x-value, is passed as an argument. Inside the black box, there is only routine code, no variable code.

Black box example 2

Let's take another black box example: a VBA dictionary. This is a class.

Imagine that you would have to write the code for the VBA dictionary yourself. More concrete, this means building an object that can be filled with as many key / value pairs as needed and, subsequently allows for fast lookup. Honestly, that would be quite a challenge... The good news is that this is a well know problem in the coding community. Clever software engineers have thought about that same problem already a long time ago and the result of their joint effort, in VBA, is the VBA dictionary object. All YOU need to do is investment a small proportion of your time into the interface of the VBA dictionary - only the interface, not how it works inside - and you will be rewarded with a great object.

The dictionary black box constains both variables and routines. It is a class of the classic type.

Image showing the three basic class types, type 1

Example 1: Classes as namespaces

I have a good friend - let's call her J.V. Although J.V. doesn't use classes, she is an excellent VBA programmer. Whenever J.V. is up against a coding problem, she simply writes a routine to solve it. One problem, one routine. Another problem, another routine. All the routines have a unique name, and, she simply adds them to a VBA module, which, in time, has become a great library. To give you an idea, here is a screenshot of one of her VBA modules.

Image showing screenshot of VBE editor

J.V. is a nice person and lets me use her magical modules. Very handy. However, there is a small problem...

Name conflicts

Some of J.V.s routines have exactly the same name as mine. For instance, I have a sub ToLog and she has a sub ToLog. The result: my VBA code didn't compile anymore.

A bad solution: prefixing?

My first (not so bright) idea was to rename of all her routines by prefixing them with "JV". For instance, I kept my version of the sub ToLog and renamed hers as JVToLog. As some of her routines were calling other routines, this was a very annoying thing to do. It took me hours. When the result was there, I was pretty happy as 1) my VBA code compiled and 2) I could easily distinguish between my routines and J.V.s.

Image showing screenshot of class filled with routines
Then, she brought me an updated version of her modules... 🙁

A good solution: classes!

My second idea was to put her routines into a class, which I called cJV. I simply added a class to my project, pasted the VBA code of J.V.s module in there and created a variable of the type cJC whenever I wanted to use the routines. The following image visualizes the idea.

Image showing screenshot of class filled with routines

Now, whenever J.V. gives me an updated version of her routines, I simple delete the content of the class and replace it with the new one. Now and then, when an argument has been added or removed, I need to spend a little time adjusting my VBA code, but, until now, the approach works great.

In this example, we have used a class of this type.

Image showing the three basic class types, type 3

Example 2: Classes as arguments

Did you ever experience this situation?

You have built a function with a limited number of arguments, and, due to changing requirements, the number of arguments grows and grows and you end up with something ugly like this.

No excuse. Even if you could not have known from the start, this is bad code design.

The solution: classes!

Instead of passing all the variables as an argument one by one, try to create a class that contains all the arguments.

Image showing screenshot of the class

Now, pass an instance of the class as an argument. Like this.

This is a lot more elegant, no? Plus, the next time that your agile boss wants to change the requirements again, your function call will not change! Thanks to the class, your design is a little more robust. Admitted, you could not have known this from the start. And, it is not a good idea to anticipate this issue by creating such a class for every function or sub that you have in your VBA code. As a general rule, if you are getting confused yourself by the multitude of arguments, consider using a class. The class helps to organize your code.
You can access the variables inside the function Calculate by means of the class instance. As, in this example, all the variables are Public this is very simple.

Image showing screenshot of the module

Note that declaring all the variables in the class as Public is considered bad practice. Object oriented principles dictate that variables inside a class should preferrably be Private and approachable through Public routines. While this is certainly true, to keep the example simple, we have ignored this principle.

In this example, a class of this type was used.

Image showing the three basic class types, type 2

Example 3: Returning classes

Let's continue example 2 and start from this VBA function.

The Calculate function returns one value: a Double. Due to changing circumstances, the function Calculate now needs to return six more values. What can you do? Many VBA programmers would do the following: add six extra variables as arguments and pass them by ByRef.

It works fine. But...

...how misleading this looks! One result is in the return, and six other results are in the 'inputs' (as most programmers will intuitively assume that the arguments are inputs). Again, not a textbook example of elegant code design. Can't we organize this VBA code differently?

Of course: use classes!

How about creating a class that contains all the result variables and return an instance of the class? Here is how the class could look like.

Image showing screenshot of the class

And here is the new function call.

This is a lot better, wouldn't you agree? All the input variables are in the input class, all the output variables are in the output class. Adding new inputs variables is easy, adding new output variables also. Classes have improved the organisation of your VBA code, and, have made your life a little easier.

You can simply set the results by using the instance of the class.

Image showing screenshot of the module

Error handling

Now let's assume that something could go wrong in the Calculate function.

In that case, the function Calculate should return an error message and the other results should not be used. Assume the errors should end up in the field Result7 and always begin with "ERROR:". For instance, "ERROR: Convergence has not been achieved.".

How to manage that?

Easy, add some routines to the returned class. First, add the function IsValid that returns True if no error has occured and False otherwise. Second, add the (classical) function ToString that a String that contains all the fields of the class. Here is the VBA code of the class cResults.

Here is how the main VBA code could look like. Quite readable, no?

This example illustrates the use of a normal class.

Image showing the three basic class types, type 1

To end...

Did it help?

Are you convinced now that classes can really upgrade your programing skills?

If not, we apologize.

Consider talking to other VBA programmers and ask them whether or not they think classes are useful in VBA, and if so, why. Maybe they can explain better. Classes are really worth the time investment.