Understanding Interfaces in VBA

Introduction

Around the time the dinosaurs roamed the earth, APR released the first of its VBA mini-series. The subject? Class modules, a mythical beast in our industry, although certainly basic material for anyone who uses Python or other object-oriented languages in their work. In that article, I likened class modules to objects as blueprints to a car. They act as the template for objects, and objects are powerful in VBA, in that they can contain their own properties and methods.

In this article, we take classes a step further by introducing interfaces, which are only used when writing relatively complex code. Nevertheless, the language was designed to allow the functionality, and it does allow VBA to (sort of) meet one of the four pillars of object-oriented programming: polymorphism (the definition of this is beyond the scope of this article, but see here if you’re interested!). We will introduce interfaces conceptually, using a slightly different metaphor to cars, then take a look at the exact syntax in VBA for making them work. We will then move into some actuarial applications before wrapping up this mini-series!

The author recommends any readers who are not comfortable with class modules to briefly refresh themselves using the previous article; some of the below assumes a level of comfort with the concepts/terminology of VBA class modules.

An Introduction to Interfaces

Insofar as how classes abstract an object into its own self-contained code, Interfaces abstract classes into a template of what their code should look like.

If we go back to the class being a blueprint of a car (e.g. all cars share similar characteristics: top speed, number of wheels), you can think of an interface as being the blueprint of a vehicle, and a car as being an implementation of a vehicle. A car is a type of vehicle, but then, so is:

• A minivan
• A lorry (red or yellow)
• A jet
• A hovercraft

Now, some vehicles share characteristics with a car – for example, lorries also have wheels, while hovercraft don’t (the author’s browsing history now contains do hovercraft have wheels?). At the same time, jets have thrusters, and cars just… don’t. Different vehicles have different features. If you wanted to create a class that encapsulated a vehicle, you’d probably struggle to really capture all the different things vehicles can do.

However, there are some things that vehicles all have in common. For example:

1. Vehicles have to be able to start.
2. Vehicles have to be able to stop.
3. Because their primary purpose is to travel, it’s possible to measure their speed at any time.

The key here is that, while every type of vehicle might achieve these things differently, and the mechanisms might vary wildly, they all have to be able to start and stop (for example). Yes, your Jaguar XK120 might have a 6-cylinder engine that harvests power from a parallel dimension (again, I really do not know much about cars), and your baby’s first scooter (please don’t give scooters to babies) might be powered by 2 AA batteries, but both need to be able to go in a direction. This is where interfaces come in. When modelling a group of ‘implementations’ of the same ‘concept’, it can be helpful to list the things that all examples of that concept need to have. If I create a Car class, and I let the code know (more on this later) that a Car is also a Vehicle, it would be helpful if the code kept me honest, made sure I developed code for all the key features of a Car. If I forgot to include some code that described how a car starts, anyone who tries to use my code will soon find it absolutely useless. Interfaces can help to prevent this.

The crucial point here is that what’s going on under the hood doesn’t matter. Two implementations could be as different as night and day. But the interface needs to define the what, and each individual implementation of it has free reign over the how.

In a VBA context, Interfaces serve as a contract between different parts of a program, to ensure certain methods and properties are implemented in any class that ‘adopts’ it. This abstraction promotes consistency and reliability across different components of a project. We’ll see in more detail below where we build our own just why that is.

A Practical Example

When trying to program interfaces in VBA, there are 3 key areas to be considered:

1. The Interface itself, which specifies what implementations of it should be able to do.
2. The Class which implements the interface, and specifies how to do what it can do.
3. The Calling Code, which relies only on the interface.

To see this, and to pivot away from a laboured vehicle example and diversify the article, let’s imagine a coffee machine. Any coffee machine needs to be able to do a few things:

• Heat water
• Make coffee
• Pour out coffee

Readers who take their coffee very seriously will be firing out all sorts of new suggestions, but let’s keep the example relatively limited for now, as we will be building this in VBA!

Disclaimer: the examples below are simplified significantly for illustrative purposes. Any resemblance of the names of coffee makers to real-world equivalents is purely incidental.

You come up with a couple of possible implementations of the interface, which are basically types of coffee machine: let’s go with Nespresso and French Press. Now, we can place our key areas above with something more specific:

1. The Interface tells the world what any coffee maker should be able to do.
2. The Classes (FrenchPress and Nespresso) provide the details of how they are individually designed to do those things.
3. The Calling Code doesn’t care what machine it’s using, because you don’t really need to know what’s under the hood – you just want your morning coffee!

In VBA, the way interfaces are modelled is via specifically built class modules (which is why being familiar with classes is so helpful). The developer has to create a class module that contains no real code, but does contain methods/properties that they see as important to anything implementing that code. For example, let’s try that with our coffee maker example:

Here we’ve created a class (called ICoffeeMaker; the “I” prefix is optional and meant to separate interfaces from classes in some way, just so reviewers or people who have to inherit the code are aware there’s a distinction) and filled it with empty code. These sub-routines are public, because they will need to be accessed by the user eventually. However, they’re empty!

Now, let’s start to implement this interface. In VBA, a class module uses an interface as a template using the keyword Implements. See here:

I’ve put this code into a new class module called Nespresso. The key things to notice here, which differ from a normal class module:

• That second line “Implements ICoffeeMaker” tells the compiler that we want this class to be forced to use the structures defined by the Coffee Maker interface defined above.
• The sub-routines here are Private (and we will see when we write the calling code this does not cost us).
• The sub-routines are named in a particular way: the name of the interface (ICoffeeMaker), an underscore, and then the name of the method as defined within the interface (e.g. BoilWater).

As it happens, coffee aficionados will point out here that Nespresso doesn’t actually boil water, it just brings it close to boiling point. I could go a step further and point out that, for a French Press, the water boiling is actually manual. I hear you, I really do.

I’ve done the same thing with the new FrenchPress class:

Because I don’t actually know how these coffee makers work under the hood, I’ve made the output a message printed in the immediate window for illustration. Now let’s try to use these coffee makers. In a standard code module, see below:

Again, a few things to unpack here:

• When creating the variable which makes coffee, it is created as the interface name, NOT the class name.
• However, when setting (initialising) the object, we select which class which implements the interface to use (in this case Nespresso).
• Then, the calling code below that would look the same whether we had set the object as Nespresso or FrenchPress.

When run, this is the output:

Now, if I wanted to use a French Press instead of a Nespresso, I only need to change one word in the calling code: Nepresso to FrenchPress. Now the output changes:

The important thing here is that the person who’s using our ‘infrastructure’ doesn’t know how to do any of the things (BoilWater, BrewCoffee, PourIntoCup), but they’re able to create a simple few lines of code which do them all. What’s more, by changing one word in their code they can get something completely different! This is where the core power of interfaces comes in.

Now, the above is a really simplified version of Interfaces to make a clear point. There are a few questions readers might have around how much we can flex those features. Let’s address some of them below:

Properties

Interfaces can also support the creation of properties. For example, you might want a property that describes the size of the coffee cup (CupSize). You would define that property (and associated let/get procedures) in the interface, again leaving them blank. Then, in the implementation, you would include the property and put any actual code in there.

New Methods/Properties

So far, all we’ve done is create implementations that use the methods described in the interface. But what about making new properties or methods within that code? The good news is: you can and absolutely should be creating new properties. This could be for a number of purposes:

• Perhaps the BrewCoffee method is actually quite complex, and it would help code maintainability for the developer to split it into multiple sub-routines and functions.
• Perhaps this particular implementation of a coffee maker has snazzy extension features, like bespoke coffee maker music, a milk frother and an espresso foam art finish passed down through the generations.

Think of it as a conversation:

Interface: “If you want to call yourself a Coffee Maker, you have to know how to BoilWater, BrewCoffee and PourIntoCup”.

Implementation: “Okay, I’ll do those things, but I’ll also track the water level, whether the user wants milk in their coffee, and whether to play coffee music. I’m also fancy, so I’ll add a new method that cleans the filter.”

The interface is the bare minimum and the implementation can do a lot more.

The big drawback to this is that the user cannot write calling code and see all these additional features. The fundamental idea is that interfaces expose the object to the user (‘What can this coffee maker do?’) while the implementation is supposed to be invisible. You can get around this by defining the object in the first place as the implementation class:

But this completely removes the interface from the calling code and in doing so gives up the flexibility it provides. If you kept the interface definition, those limitations come back:

Implementing multiple interfaces

It’s possible to have an implementation be an example of two things. For example, the Nespresso class could implement ICoffeeMaker and ICleanable, which represents all objects which can be cleaned. The important thing to remember is that every method/property in BOTH interfaces is mandatory in the implementation.

In this next section, we will expand the view a little and think about general pros and cons.

Advantages/Disadvantages

The biggest and most notable advantage is the point above, the ability for someone who ‘uses’ the code you made to not need to distinguish at all between multiple implementations of the same thing. In Object-Oriented Programming terminology, this is where polymorphism can be covered somewhat within VBA. It’s a bit clunky, and it isn’t perfect, but it does work.

Not only can the user build something like above, calling code that is so easy to switch between implementations, but they can also rely upon the implementations, because the classes promise to follow a specific structure (as outlined in the interface). “If you’re going to call yourself a toaster, you’d better have a toast function”. To see this, try creating an implementation where you don’t include the functionality that exists in the interface:

This error happens when I comment out the code for PourIntoCup in one of the implementations. VBA refuses to allow an implementation that does not do the bare minimum, which is to have those three functionalities. Now, a developer looking to use a coffee machine can rest assured that it pours your coffee.

There are a number of other clear advantages, although not quite as practical as above:

• Code reusability: you can write your functions and subs with any object that implements an interface, making your code more modular and reusable.
• Separation of Concerns: Interfaces separate the ‘what’ from the ‘how’, which results in cleaner and more maintainable code (e.g. if you need to change the how, it doesn’t break anything about the what).
• Scalability: as your project grows, you can implement new classes on existing interfaces without rewriting core logic. Your work becomes more future-proofed as a result.
• Easier Testing: in terms of testing interface-specific logic, mock implementations can be created for testing purposes; you don’t need the full working class to test logic that depends on the interface.

There are also a number of disadvantages with interfaces, though:

• The UI is a bit clunky: the support for interfaces in VBA is quite limited. They are a sort of modified version of class modules, and the UI doesn’t provide any assistance around the syntax required to make them work in implementing a class. It makes them feel like an afterthought, or just squeezed into the available features.
• Verbose syntax: you are forced to prefix all interface method implementations with the interface name (e.g. ICoffeeMaker_BrewCoffee instead of BrewCoffee), which adds extra typing (although not significant), and may be more difficult to understand for more junior developers.
• Abstraction: it can be difficult for beginners to understand the idea of ‘declaring behaviour but not implementation’. A bit like writing a recipe without ingredients…
• Complex: the hoops you need to jump through make it impractical for smaller projects, especially when there is no clear set of implementations to create. For the most part, and especially within actuarial work, there’s a lot of overengineering made possible through their inclusion.
• No inheritance: unlike how the equivalent is present in fully OOP languages (e.g. Java, C#), there are limited features in VBA, which is missing useful tools such as multiple inheritance, extending interfaces, and default implementations. The detail here is beyond the scope of the article, though; just be aware if you’re using interfaces in VBA for a big project, ask yourself: why? Why not a different language that does this better?

If you’re developing a big piece of code in VBA, it’s worth really taking a step back before deciding to use interfaces and asking some questions:

Answering these questions may help decide whether an interface solution is helpful in this instance.

Actuarial Applications

Of course, a lot of the above veers close to proper development principles, and, in the actuarial world, most VBA uses are automation-inspired, to achieve one task quickly or reliably. It’s very rare to see genuinely complex VBA projects in our industry, designed to maybe perform a whole pipeline end-to-end. However, there are some areas where interfaces can be useful:

• Pricing: imagine trying to build a pricing model for different product types. While each product might act differently in practice, there will be shared aspects to how a premium is calculated (e.g. calculate NPV of future cashflows, apply loadings/expenses, uplift for profit). An interface approach may simplify logic for handling all these different product lines under one unified pricing system (say).
• Reserving methods: in a non-life context, claims projection work can be done using a variety of methods (Chain-Ladder, Bornhuetter-Ferguson, Cape Cod, …). A reserving tool built using interfaces may allow for switching between methods dynamically.
• Preparing reports: where regular reports are required for a number of different stakeholders with different levels of understanding, actuaries may want a Report Writer implementation, which allows for filtering documents down based on the audience, and then also might allow for different exporting templates (Excel, PDF, Word, etc.). A few lines of code could prepare a report intended to be bespoke for the audience.

Summary

As we call a close to this VBA mini-series (unless I really want to write another), we tie all the way back to class modules. We see that interfaces allow us to fine-tune our control over implementations of similar classes in a way that really helps our code become more future-proofed and maintainable. We also see that they can be a viable option for bigger projects, and are theoretically very easy to create.

While actuaries do not commonly use VBA in the context of large-scale projects, there are a number of areas within work we do that could benefit from an approach that is more flexible, and interfaces can be a solution in those places. Moreover, the technical theory behind them is transferable to other programming languages, some of which are simply better and easier to use in the first place.

I don’t expect many readers to want to try this out for themselves, and we can rightfully say niche things are niche for a reason; but being aware that they are an option can be really helpful when you are looking at designing a solution. If you do, I hope this helps.

As always, if you have any questions, please e-mail the author at [email protected]

John Nicholls

April 2025