Making your VBA code more flexible with interfaces

Interfaces are essential to object orientated programming – especially when we start to use design patterns. They allow programs to be flexible and easily extended without huge changes to the code base. They can be a tricky concept to get your head around though and I often find they are best explained with examples.

Throughout this post I am going to use a specific problem as an example and slowly build a solution to it.  Lets say we have an application that is used to obtain quotes for car sales.  The quote calculation is different for each car (based on make and model for example).  When we build the code we don’t know which car we need to obtain a quote for – this is selected by the car salesperson when they run the application.  We want the code to be able to easily choose the car to quote and obtain the quote.  We also want the flexibility to change a specific car quote calculation without impacting other car quotes and add new cars easily.  There are many ways around this and our solution we build is flexible and can be extended as need be with minimal changes to our core code base.  As usual it’s not a perfect solution (there are other changes I would make before I would call it final) but it demonstrates the concept of interfaces and how they can be used.

What is an interface?

Interfaces are a way to enforce a specific shape and structure on classes.  They are a contract a class can agree to adhere to.  You can’t create an object from an interface – instead you define a class and explicitly state the class will obey the rules of the interface (implements the interface).  We then create an object of that class type.

We can have multiple classes which implement a given interface and because of this each will obey these same shape and structure rules.  If two classes obey the same shape and rules (implement the same interface) we should be able to easily swap one class for another without huge changes to our code base … so long as we program to the interface and not the specific classes.  This is very important and one of the key reasons for using them.

How we can define an interface

To create a new interface we add a class module to our project and give it a name.  For our example I created a class module and called it ICar.  Within this class module we put our signatures for the common aspects of each class that will implement our interface.  We have to think ahead a bit here and ask what member fields and methods we want each individual class to have.  Typically I would add:

  • Property Get and Let method signatures for each of the common member fields;
  • Any function or subroutine signatures for common methods;

By doing this we ensure any class that implements the interface will provide the same set of methods (if they don’t provide them we get an error when we try to compile).  Each are defined as empty methods in the interface and the individual classes that implement them will provide the details for how they work.  The important parts to keep in mind are:

  • Each class has to implement/provide the same method;
  • The method has to have the same signature;

Below is the interface I created for the cars.

 

The video below gives some additional explanation and talks through this part of the example.

 

Building a class to implement an interface

Now we have constructed our interface for a car we want to construct some classes that use this interface.  We do this by creating a class module and putting the statement  Implements ICar  right at the top of the class module.  We have the usual class fields, methods and properties but we also have to provide implementations of the interface methods.  These have the syntax of  ICar_MethodName and a straightforward way to create and code these up is:

  1. At the top of the code window in the VBE there are two combo boxes with drop-down arrows;
  2. In the left most combo box select “ICar”;
  3. In the right combo box you will then have a list of methods you have to implement.  When you click on each it will generate an empty method of the correct shape;
  4. Once generated we can delegate to the relevant class method using the  Me.ClassMethod syntax (this assumes we have a class method which matches);

An example of this is given below for the Ford car class.

 

In my own application I created classes for a Toyota Avensis and a Ford KA using two different class modules.  They are a bit artificial here and only have slight differences.  In reality we could provide completely different (and complex) methods for obtaining the value of the car.

Our individual classes can have additional methods or fields that are not defined in the interface … however, when we are programming to the interface (next section) we can only use those methods defined in the interface.

The video below summarises some of the key points from this stage and talks through some example code.

Programming to the interface

Once we have set up our interface and built our classes to implement this interface our job becomes much easier at run time.  Most of the code for the quotation application can rely on the interface only and not the specific details of how a given car provides the details of the interface methods.

In our car quotation application I built a userform with a single combo box to select the car you want a quote from and two buttons: obtain quote and cancel as per below.

CarQuoteUserFormWhen the user clicks on “Get Quote” the application code decides which specific type of car to create – either a new car of “CToyotaCar” or “CFordCar”.  The rest of the code is independent of this choice so long as we use the methods defined in ICar (and not any additional methods defined in the classes that are not in ICar).  Because of this they will work with either of the car types without any problems.   The code is placed in a separate module which I called “MEntryPoints”.

 

Note: in reality I would break up these into different parts (so the showCarQuotationForm would only deal with showing the form and getting the data out of it).  For now it will do!

From this we can now see some of the benefits of using the interface: If we want to add a new type of car we can do this easily by creating a new class that implements ICar and adding it to the select case statement.  If we had instead hard coded the car valuation into our code we would have to hunt around for and change a lot of  If ... Then ... Else  and  Select Case statements.  Although we do need to make changes to our code here they are isolated and easy to find making the code easy to extend and flexible.

The video here again gives some additional explanation and talks through part of the example.

For completeness the code I used inside the userform is given below and I also include a download for the spreadsheet containing the whole example for you to look through.

 Interfaces example spreadsheet

All of the mp4 videos for this post can be downloaded from here.

Errata

I would like to say a big thank you to Steven Bohlken for pointing out a few typos in the code and an unhandled run-time error in the code.  As I result I have updated the code listings and the example download file.  I have yet to update the videos but will do in the fullness of time.  A list of specific fixes are given below.  Thanks a lot Steve!

  1. In the quotation userform there was a typo in a  With ... End With block which led to a new form being created and used on the fly (instead of the one already created and initialised).  While not a big problem the code is much cleaner after fixing this;
  2. The userform contained an unhandled error when an invalid car was chosen and the user clicked “Get Quote” (since the code tries to honk the horn of an invalid car).  To fix this I included what I call a “soft” user input validation on the combo box selection change event and on the “Get Quote” click event;

3 thoughts on “Making your VBA code more flexible with interfaces

  1. In building the above post I have taken on board a lot of feedback from readers so far:
    * How the post is structured and the code included;
    * Breaking up the videos into smaller parts;
    * Colours and fonts used in vidoes;
    * Using a combination of presentation slides and examples in vidoes;
    * Removing the “click” sounds when recording;

    Hopefully this makes the content easier to follow and the concepts easier to pick up – as usual feedback from readers is very welcome! 🙂

  2. Have you tried the open-source add-in for VBA, Rubberduck-VBA (disclaimer, I’m a contributor)? It has an Extract Interface capability (along with a plethora of other features). It makes writing OOP in VBA so much easier, as well as parsing, resolving and inspecting your code. It would have caught some of the subtle bugs that were found by Steven.

    • Hi Andrew – yes I have tried Rubberduck-VBA in the past and you are right it would have caught such subtle bugs. I think it’s a fantastic idea and a great project: I’m a real fan of ReSharper and see Rubberduck as the “VBA version of ReSharper”.

      The main reason I don’t use it very often is stability problems. Whenever I install Rubberduck and try using it’s functionality Excel starts crashing (mainly when I close it down but sometimes when I’m using it). I found this first on older versions but have also tried the newer release just a few weeks back. I tried different versions of Excel (2010 and 2016 both with 32 and 64 bit) … again the same. It could be I’m using Windows 8 or that I have had multiple versions of Excel on my machine (so messed up the registry). Maybe a new machine or complete new build would fix it and I will keep trying periodically to see if future versions of Rubberduck fix the problem.

      Note: I did notice similar issues had been flagged on the Rubberduck issues log (on the git repository) but there were problems recreating them so I don’t think it was every really looked into.

Leave a Reply

Your email address will not be published. Required fields are marked *