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.
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.
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,
Dim D As Dictionary
Set D = New Dictionary
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.
Classes can have variables, and no routines. Classes can have routines, and no variables. Most of the time though, they will have both.
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.
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.
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.
Cos(0.5) 'Result is 0,877582561890373
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.
Public Sub Test()
Dim D As Dictionary
Set D = New Dictionary
D.Add "Russia", "Moskow"
D.Add "Japan", "Tokyo"
D.Add "United States", "Washington"
MsgBox GetCapital(D, "China")
Set D = Nothing
Private Function GetCapital(ByRef D As Dictionary, ByVal Country As String) As String
If (D.Exists(Country)) Then
GetCapital = D.Item(Country)
GetCapital = "Capital of <" & Country & "> cannot be found."
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.
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.
J.V. is a nice person and lets me use her magical modules. Very handy. However, there is a small problem...
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.
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.
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.
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.
Public Function Calculate( _
ByVal Field1 As Boolean, _
ByVal Field2 As Long, _
ByVal Field3 As Double, ByRef Field4 As Double, ByRef Field5 As String, _
ByRef Field6 As String, ByRef Field7 As String, _
ByRef Field8 As Boolean, _
ByVal Field9 As Long, _
ByRef Field10 As Double) As Double
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.
Now, pass an instance of the class as an argument. Like this.
Public Function Calculate(ByRef Arguments as cArguments) As Double
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.
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.
Example 3: Returning classes
Let's continue example 2 and start from this VBA function.
Public Function Calculate(ByRef Arguments as cArguments) As Double
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
Public Function Calculate(ByRef Arguments As cArguments, _
ByRef Result2 As Long, _
ByRef Result3 As Integer, _
ByRef Result4 As Double, _
ByRef Result5 As String, _
ByRef Result6 As Currency, _
ByRef Result7 As String) As Double
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.
And here is the new function call.
Public Function Calculate(ByRef Arguments as cArguments) As cResults
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.
Now let's assume that something could go wrong in the
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
Public Result1 As Double
Public Result2 As Long
Public Result3 As Integer
Public Result4 As Double
Public Result5 As String
Public Result6 As Currency
Public Result7 As String
Public Function IsValid() As Boolean
If (InStr(1, Result7, "ERROR:", vbBinaryCompare) > 0) Then
IsValid = False
IsValid = True
Public Function ToString() As String
ToString = ""
ToString = ToString & CStr(Result1)
ToString = ToString & CStr(Result2)
ToString = ToString & CStr(Result3)
ToString = ToString & CStr(Result4)
ToString = ToString & CStr(Result5)
ToString = ToString & CStr(Result6)
ToString = ToString & Result7
Here is how the main VBA code could look like. Quite readable, no?
Public Sub Main()
Dim Arguments As cArguments
Set Arguments = New cArguments
Arguments.Field1 = True
Arguments.Field2 = 10
Arguments.Field3 = 0.1
Dim Results As cResults
Set Results = Calculate(Arguments)
If (Results.IsValid()) Then
'the results are ok, do something with them
This example illustrates the use of a normal class.
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.