London

81 Chancery Lane
London
WC2A 1DD
01235 821 160
View map

Edinburgh

1 Lochrin Square
92 Fountainbridge
Edinburgh
EH3 9QA
01235 821 160
View map

Dublin

24A Baggot Street Upper
Ballsbridge
Dublin
D04 V970
01235 821 160
View map

Administrative Office

BH Office
Church Street
Ardington
Wantage
Oxfordshire
OX12 8QA
01235 821 160
View map

Send us a message

CLOSE X
Contact Us
27.03.2024

VBA Mini-Series – Class Modules

Introduction

The majority of VBA written in the industry is relatively simple, and does not require the complexity of class modules. That said, there will always be times when having an understanding of them, both conceptually and practically, might be useful!

In the first of our VBA mini-series, John Nicholls dives into class modules in a VBA context, comparing VBA to other languages with classes and recommending areas where regular actuarial work might benefit more from using them.

What are Classes?

To really tackle classes in a VBA-specific context, it is useful to zoom out a bit and look at classes in programming in general. To understand how they work, it’s important to outline their relationship to objects. In languages which support their use, objects essentially represent the building blocks of a program. To start off with an analogy, say you want to build something which models/simulates a race in Formula 1. There are a few things to model:

  1. Each individual driver will have different skills and will be good at different things. For those who grew up playing video games, this is represented by an individual rating.
  2. Each driver will drive a specific car, which will vary at turning, top speed, acceleration, etc. If this were a video game, it would be sensible to let the player choose to have any driver they want in any car they want.
  3. We could model one race (one track), but it would be sensible in a fuller model to have a selection of tracks, say, the 24 races which will comprise the 2024 F1 season.

Of course, there will be a lot more to model if the object is to simulate reality, but let’s focus on these things for now. In coding terms, a beginner might be tempted to write out all the ‘variables’ they need (car constructor, top speed, acceleration, weight, driver, driver age, driver height, etc.) and then have a variable for each one. This is the bare minimum requirement – if you want a model to include these things, they need to be storable somewhere. Variables are designed to store information.

However, to paraphrase the Beach Boys, wouldn’t it be nice if we could associate variables which are linked to one another (their lesser-known track that never made it to number one)? For example, if you have ten cars and each one needs a top speed, you might want ten variables for that. You could make variables called Car1 and TopSpeed1, but then that relies on you choosing to associate them with each other in later code, and imagine if you had to make 50 cars!

It would be much nicer if we had a hierarchical way to store this data. The car could be at the top of the hierarchy and specific variables could be attached to it as we branch outward. Maybe variables could be below other variables in the hierarchy; for example, a variable SeatbeltWorks representing whether a seatbelt is functional could attach to the Seat variable, which is itself attached to the Car class.

In VBA, there’s already a structure of data which caters to this need: a User-Defined Type (UDT). Also known as a struct in other languages, these are a way to group related information under one umbrella. Let’s say we create a variable of the UDT Car. This will have attached variables to it (such as TopSpeed or Seat). These variables can be of any type, including other UDTs.

On the surface, this provides all of the structure we need. If you want to model a Car, a UDT can contain all the facts about it, information a user might want to access, specific to the car they’re looking at. However, the key drawback here is that, if you want to model a car properly, you can’t just do it with information that describes the car.

Imagine if we had a UDT for the Car that contains key information, like CurrentSpeed. In the video game scenario outlined above, the user can press buttons on their controller to change the car. For example, they might press X to accelerate the car. In this situation, the only piece of information about the car that needs to change is its CurrentSpeed. It needs to increase by 10, so the programmer implementing this makes a function in their code which increases the speed of a car. This function takes two arguments:

  1. A variable of the type which has been created above (the User-Defined Type).
  2. A variable which is numeric, which holds how much the car accelerates by.

This function can be used by someone building the larger model to accelerate any car, for example linking the pressing of a button (X in the example above) to an acceleration of +5mph. The problem is, what if a different model wanted to use the Car UDT as well? Let’s say the studio is developing 3 or 4 car-related games, all of which could benefit from having a Car ‘object’. The lead developer sends across the definition of the UDT, but then they have to send that function as well, or the Cars in the other game can’t accelerate (or other teams have to build their own code to accelerate a car when perfectly good code just sits there). That’s just an extra function, no big deal. But what if it were an entire ecosystem? What if the “Car model” had over 100 functions detailing how it does things? We would have to ensure the structure of each is appropriately ported across. It could be time consuming, and such a large task it requires further testing just to ensure nothing goes wrong.

It would be useful if we could somehow encapsulate the concept being modelled. By this, we mean one large piece of code which contains:

  1. All the data about the Car variable we want to model.
  2. All the processes it can go through which mutate the state of the variable (i.e. change existing data – with the accelerate example above being a version of this)

While encapsulation in more detail is beyond the scope of this introductory article, many view it as synonymous with object-oriented programming, so it’s a principle we recommend reading up more on.

This hierarchical structure, where a variable is directly attached to the car, and it has a self-contained logic that performs some function that changes the current state of the structure, is an object. It’s a more sophisticated way to hold information, so that some of the information can be intrinsically linked to other aspects. We tend to refer to objects as an instance of a class, in that classes contain the code that models how an object works.

Going back to the analogy, the car driven by Max Verstappen (current F1 champion) is an object, but it belongs to the Car class (as would the car I drive, a Nissan Qashqai). The object can be thought of as the physical thing in the world you’re thinking about right now, and the class can be thought of as the blueprint of the object, as you can see below:

Classes generally can hold different types of content:

  1. Basic data which tells you something about the class. This can be read-only or editable. We call these properties. Examples of these for the Car class are Make and Model, or the CurrentSpeed highlighted above.
  2. Things a class should be capable of doing. We call these methods. An example of this for the Car class is Accelerate, as outlined above.

Let’s say you wanted to flesh out the Car class a little bit more. You could come up with a series of methods/properties you feel all cars can do/hold:

In this way, you can define a class fully by what’s in it. This car situation is quite a nice example, because it’s human instinct to group together the same information about things. For example, imagine your friend Bob (if you don’t have a friend called Bob, imagine your imaginary new friend Bob). Tell me about him.

Probably some of the first things that come to mind here might be things about him; perhaps his hair colour, age or occupation come to mind. These naturally fit as properties of the class Person, because most people have all those things; of course, not everyone has a hair colour or occupation, but the idea of it makes sense. Because of how naturally we associate information with concepts, imagining objects when programming is quite natural, so hopefully you can see why this is such a tempting way to arrange code.

Object-Oriented Programming (OOP) languages have the facility to handle classes and objects. There are specific pillars/requirements for a language to fit this mould, but these are out of the scope of this article. For now, we will turn our attention to how classes are implemented in VBA.

How do Classes work in VBA?

The first thing to note about VBA is that, to some extent, we already have objects. If anyone reading has experience with the language, Excel itself provides an object model environment. The diagram below gives some examples of the objects inherent to Excel:

Excel is arranged in a hierarchical way. The application refers to all Excel workbooks open at a time (more experienced readers will know how to create new instances of Excel; this creates another application). Each workbook has several worksheets on it, and each worksheet has a set of cells (in this case, Range). When writing VBA, users will be very familiar with using these existing object structures.

These objects have their own methods and properties. The worksheet, for example, has a Name, and a Copy method. We won’t focus much further on these in-built objects. Instead, we’re going to model a very simple Car class. In VBA, most users will be familiar with writing code in a module, which we can see here:

Classes need to be written in a specific class module (which inspires the title of this article), as below (just right click in the same way as adding a module and select Class Module):

In VBA, when class module code is written, the user becomes able to create an object named after that class module. With the example above, it is possible to create an object which has the type NotThatManyPeopleWriteCodeHere:

In that module, the user needs to store any properties and methods the class needs to have. In the below examples, we will use the Car class.

Properties

Let’s start with properties. In VBA, these are defined by two procedure types:

This is where the syntax may be unfamiliar to most VBA programmers, because property let/get are exclusive to class modules. They look something like this:

A few observations to make here:

  1. The Property Let procedure has an argument/input (“value” in this case). This is the value the user feeds in, saying to the module “I’d like the TopSpeed property to be set to this value, please”.
  2. The variable pTopSpeed is private to the class, and essentially exists to hold the top speed of that instance of the class.
  3. While it may seem that Property Let and Get come hand in hand, there is no practical requirement to have a Let clause. Where a property has only a Get clause, that means the user can obtain the current value but can’t overwrite it. In other words, we see an example of a “read-only” property.
  4. Similar to this, it is possible for a property to have a Let but no Get clause, meaning it can be modified/overwritten, but never read by a developer using that class. In practice, there are very few scenarios where this would be useful.

In practice, we would be able to create an object which is of the type of the Car class, and read/set this property. For example:

Now, this property set-up relies on the pTopSpeed variable being available to hold that data. In our Car class, it is defined as a Private variable (i.e. it cannot be accessed from the outside). It is theoretically possible to define it as a Public variable, and users can then access it as if it were a property (in the VBA auto-complete, it shows up as a property). One would be forgiven for thinking that it seems like the inventors of toast had better prepare for an all out war for the position of greatest idea of all time, given you save so many lines of code. However, there are two clear reasons why we do not want this to happen:

  1. Classes ought to encapsulate the concept they capture; that is to say, the designer of the class should be able to decide what can and cannot be accessed by a user. Setting these variables public would just mean full accessibility to overwrite and read.
  2. Often, developers will want to apply some level of control (data validation) to the values that can be fed in. The value of the Property Let procedure is that it is a procedure, so one can contain other code on it. For example, one could:
    • Validate that the Top Speed input by the user is above zero but below some non-sensical figure such as 1000mph.
    • Validate that the Current Speed never exceeds the Top Speed (or, indeed, cap the Current Speed at the Top Speed)

In the example below, we validate the first:

It’s important to remember that not every variable on the class is a property. The name of the property is defined by the Let and Get procedures, and the variable is just the piece of data used to hold the appropriate value of the property. Classes will, in practice, contain many other variables, most of which do not correspond to properties but might be necessary in the mechanics of the class.

Methods

Methods will be more familiar to most VBA developers, as they use the same syntax as normal modules. Every sub-routine or function defined as Public (in scope) in the class module will appear as a method to a user. These are no different to normal module sub-routines or functions in terms of syntax, but it is generally recommended they only use variables contained within the class module itself. Other sub-routines may use public variables from multiple modules on the workbook, and this is to be discouraged, because classes are generally built to be self-contained and therefore portable. Relying on some public variable from the specific workbook the class happened to be built in will make it incompatible with other workbooks.

An example of a method would be to accelerate a car:

Note that in this example the Accelerate sub-routine must take an input; the user has to specify how much the speed of the car increases. However, inside the sub-routine, every used piece of data is a variable on the class, NOT the name of the property (e.g. pCurrentSpeed instead of CurrentSpeed). As such, the property is purely for the benefit of whoever uses the class. No functions or sub-routines within the class use any properties. Instead, they directly interfere with the variables which hold the information those properties contain.

These methods CAN return an actual value (where they are functions) or can perform a series of steps (where they are sub-routines).

Class Events

In VBA, events are pre-defined moments in the execution of code where specific code can be triggered. For example, if a reader is an actuary, they’ve likely come across code that triggers when a workbook is opened or just before it saves. These are pieces of code written in specific sub-routines that trigger every time a specific event occurs.

For class modules, each class comes with two events, and the developer can write code to trigger when either of those events happen:

An example of the syntax follows below. Here, we are setting default values for variables:

It is possible for users to define events within the class which can then trigger, but that is beyond the scope of this article. One thing to note here is that Class_Initialize (and Class_Terminate) are the exact syntax that has to be used; there can only be one of each sub-routine on a class module and it must be named that way.

By combining properties, methods and events, a developer can:

  1. Create the default state of a class, providing default properties and running any other useful start-up code.
  2. Expose to the user a powerful user-defined object which encapsulates a concept that feels very intuitive and natural.
  3. Provide a way for a user to reduce their boilerplate code by using functionality all built into the class.

These classes can be portable and sent to other workbooks to be used in a similar manner, or attached to an Excel add-in and used by multiple workbooks/teams. There are many aspects of class modules (such as interfaces, default properties, access permissions) that are not covered here, but the above should outline the basics.

How does this differ to other languages?

Some languages, for example Python and C++, are considered fully OOP. This article won’t explore the precise definition of OOP languages (for more information, see here), but VBA doesn’t quite meet the bar, as it doesn’t appropriately handle inheritance (and struggles to handle polymorphism well). However, that doesn’t mean VBA is completely unsuited to object-based programming. In fact, as we saw earlier, Excel comes with its own hierarchy of objects which VBA is specifically able to use out of the box. Because of this object-based structure, most general-purpose VBA code won’t need bespoke class modules; this is because the classes that most pieces of VBA require are worksheets, ranges and sometimes workbooks. These already come pre-packaged with no additional workload!

Additionally, in a language like Python, you can create a new class using very few lines of syntax:

In VBA, this class would be held in a bespoke module and there’s quite a lot of syntax to set up all of the properties noted above. Other limitations include:

Ultimately, however, the most impactful limitation is that VBA cannot be de-linked from Excel, which means most tasks VBA is asked to perform are in some way related to Excel. This in turn already provides most of the key object structures users need. That doesn’t mean there are no uses, though!

Some actuarial applications

Below we outline some areas of actuarial work where class modules can be a natural fit.

Cashflow Models

Where more complicated cashflow models are built, such that Excel isn’t quite enough (say, if the model involves projecting multiple coverages on a policy and each is subject to many different charges/fees), the structure of a policy is a good fit for an object. For example, you might have a structure (for a unit-linked policy) that has a hierarchy like this:

Because of the way tranches attach to individual policies and funds attach to tranches, this structure naturally synergises with an object interpretation in VBA. The policy could be the class and each item in the hierarchy above could be attached to it as a property. Making the policy paid-up (PUP) could be an example of a method on the policy.

Dataframes for Data Manipulation

One of the most common things actuaries do is relatively simple data manipulation. However, certainly since the IFoA syllabus change in 2019, the advent of other languages (R, Python) has provided a more powerful way of managing dataframes. Actuaries can add/drop columns, re-define/re-name columns, filter out rows, and re-arrange with ease. In Excel, this is a little bit more difficult (and particularly difficult to automate). With VBA, it’s possible to hold large data structures and manage them like dataframes, but the syntax of VBA makes it quite onerous to write this kind of code.

A class module attempting to simulate how dataframes work in other languages is quite intuitive. The dataframe is the main object and columns and rows are easily accessible and manipulated. This module could wrap up some of the more onerous VBA requirements, such as the need to loop through each row/column to find the right one. This can make manipulating large tables of data much easier for VBA users.

Limitations

We’ve already covered some of the weaknesses of VBA class modules compared to other languages above. Chiefly, the lack of inheritance makes more complex modelling of objects difficult without more boilerplate code, and the Excel object model already exists to help with most of the object needs a user might have in Excel (VBA code is almost always written for Excel).

A few more limitations are described below:

  1. VBA classes are just clunky. Because they are their own module and the syntax for properties is quite intensive, developers who want to encapsulate something in a class often need to spend a lot more time doing so than if they were to write a one-off piece of code.
  2. Because of the rarity of class modules in the actuarial world, the expertise doesn’t always exist to understand and edit classes. At APR, we always think about the client’s needs first, and our staff don’t stay with clients forever! So, leaving behind something that the client may not be comfortable maintaining is something we certainly would discourage.
  3. One of the key benefits of classes is that you can encapsulate an idea completely. A well-designed class should be portable and a user should not need to look “under the hood”, per se, to use it effectively. VBA classes are portable, but it is difficult to design something that fully encapsulates a concept when most solutions are designed with Excel in mind. For example, they may require a worksheet or named range of a certain name to exist.

Summary

Hopefully, this article has briefly recapped what objects are in a programming sense, and introduced the necessary syntax/structures for a VBA developer to start thinking about designing their own objects in their work. For 90% of developers, this may not bear fruit; most VBA code performs tasks that simply do not need to veer outside of the Excel object-model outlined in this article. However, for the other 10%, there may be things which come to mind that would work nicely as class modules.

We have explored the key drawbacks of classes in VBA as compared to other languages but also in a practical sense. We then highlighted a couple of areas where an actuary may, in their day-to-day job, find a real use for introducing classes into their VBA code.

This author generally recommends that developers only use classes when they see a real need for them. It can be difficult, in larger projects, to produce well-designed classes which don’t introduce further complexity down the line with maintenance and code re-factoring. This might sound like real discouragement, but it’s useful to always be aware of the option, because classes can make improvements to VBA for a developer. Consider the dataframe example above; every “normal” way to get it done in VBA would take hundreds of lines, be very specific to the problem being solved, and be difficult to visualise without thinking about objects.

Additionally, it’s always worth thinking about the drawbacks of a solution when considering it. Everything has a balance to it, and classes can feel like a real power-up to beginner VBA programmers. They can therefore be tempting to use, even when they aren’t the optimal solution. Of course, it would seem rather counter-productive to end an article introducing class modules in VBA with a few reasons why someone shouldn’t use them! We will finish by noting that class modules can make a lot of development easier. A well-designed class with well-named properties and methods can make code using it read almost like simple English, which can aid clarity for readers. Therefore, it’s always worth considering them for larger, more complex, projects!

John Nicholls

March 2024