London

81 Chancery Lane
London
WC2A 1DD
0207 112 8493
View map

Edinburgh

1 Lochrin Square
92 Fountainbridge
Edinburgh
EH3 9QA
0207 112 8493
View map

Dublin

24A Baggot Street Upper
Ballsbridge
Dublin
D04 V970
0207 112 8493
View map

Registered Office

7 Bell Yard,
London,
WC2A 2JR
0207 112 8493
View map

Send us a message

CLOSE X
Contact Us
05.06.2024

VBA Mini-Series – Extensibility

While the popularity of R and Python in the actuarial world has spiked in recent years, VBA remains a useful tool for a lot of actuarial teams. Its connection to Excel keeps its popularity high despite some clear drawbacks compared to the more powerful languages, and it remains a prime choice for quick automation of simple tasks – data cleaning, data re-formatting, bulk runners, etc.

Some actuarial teams take it a step further. Instead of simple procedures designed to accomplish a task in a spreadsheet, they design more elaborate solutions, some of which will be looked at in this mini-series. However, there is a simple extension to VBA which can have some very powerful effects if you regularly develop in multiple spreadsheets and crave some sweet shortcuts for doing so efficiently.

In the first of this mini-series of articles on lesser-known VBA techniques, we looked at class modules. During this, we recapped how objects work in VBA and examined the motivation for creating our own. One example of a set of objects is the Excel object model (Application -> Workbook -> Worksheet -> Range). In this article, we look at a different set of objects which are potentially useful for certain actuarial teams.

VBA References

Many VBA users will be familiar with VBA references. Simply put, base VBA contains most of the functionality we’re used to seeing in the Excel object model. You can do a fairly broad set of things, such as adding, removing or renaming worksheets or reading to and writing from Excel (via the Range object). Even though it isn’t in the above hierarchy, users can add or remove named ranges, cycle through comments on a worksheet or even write code that triggers on certain events (such as the opening of a specific workbook).

Many readers will be familiar with R as well (being, as it is, in the actuarial syllabus). Base R also has a lot of useful functionality, and a user can do a lot of the heavy lifting without extending the language. However, much as R can be extended through installing packages (dplyr, ggplot2, lubridate), VBA can also be extended in a similar fashion. The standard Microsoft installation of Excel comes with a pre-defined set of extensions (which we will call references for the rest of this article) which can be enabled on a spreadsheet to boost VBA’s capabilities!

The difference is: VBA doesn’t come with simple installation commands. If an enterprising developer creates a useful library of extra functionality (e.g. a VBA Tidyverse), they can typically host the code on some management system, like Github, but you don’t just run an install.packages command in VBA to bring that functionality into your machine. Instead, the package has to be structured in a specific way and downloaded to a specific folder (this is not always true, but is beyond the scope of this article), after which it is considered a reference. That said, this is a sidenote, as the reference we will focus on in this article comes as a standard library.

In the VBA window, if you go to Tools -> References, you can see the list of available ‘extra functionality’ that comes with your installation of Excel.

Some of these are already ticked, as you can see; this screenshot is pulled from a default empty workbook, and so this is the default state of things on my version of Excel. Each reference exposes some functionality, with these four being crucial to the way VBA operates. For example:

In general, the greatest advice around these four would be to leave them selected at all times. As it happens, it’s not possible to untick the first (feel free to try – you get an error!). The important thing to note is that most of these additional references are libraries; that is to say, they provide a set of new classes (each with its own functionality) which can be used in your VBA projects.

Users with more experience may be familiar with certain additional libraries already. Some of the more common examples include:

The key to using these additional libraries is to identify where they might contain functionality you would otherwise find complex to build on your own and to then use them as ‘freebies’. To do so, understanding of what library contains what features is important. These Microsoft references aren’t a monolith, and the quality of their documentation varies. There also is not a single place which houses all support. It helps to read up on references thoroughly before using them. Additionally, not everything will be fully tested and completely reliable, so advocation of their use comes with a (small) warning! However, they shouldn’t be ignored; some of the functionality is incredibly useful in most office or actuarial contexts.

This article won’t go any deeper into the general libraries that are available. Instead, we will focus on one in particular.

VBA Extensibility

The Extensibility library focuses on using VBA to program VBA programs. On the surface, that sure sounds complicated, rather exciting, and perhaps highly attractive to a filmmaker such as Christopher Nolan. Rest assured, VBA lovers: those of you lamenting the movement of their precious VBA into the mainstream need not cry for long. This isn’t a sequel to Inception, nor will it be attracting the Di Caprios of the world (apologies to anyone reading this whose surname is Di Caprio). In fact, two layers of code-ception is about as far as we can go.

To start off, go to Tools -> References and tick ‘Microsoft Visual Basic for Applications Extensibility 5.3’. Note that you don’t need to tick anything else outlined below!

The Extensibility library is now available to use and explore as you wish. Like the Excel object library, the first thing you can do is have a look at what objects are available. This is done by pressing F2 to view the object browser and selecting ‘VBIDE’ in the top left dropdown.

There are quite a few objects here, but we will largely focus on a hierarchy not unlike that in the Excel object library.

In the same way as Excel is composed of open workbooks, which in turn contain worksheets, which in turn contain cells, the structure to the right acts similarly (as with all object structures). The Project window in the editor (shown below) provides a handy proxy for this.

Let’s unpack:

Generally, code written using this reference can do one of two things:

  1. Get information about one of these objects (manipulate the properties).
  2. Change something about one of these objects (use methods).

Notice here the similar line of thinking as with classes, which underpin all object structures in VBA.

At this point, readers would be forgiven for thinking this is all a bit theoretical, and rest assured for the practical among you we will put together a worked example for you below. But the theory is important because, as with a lot of these referential libraries, the user has to shift mindset from the Excel object model to another object model entirely. The fundamentals are always the same, though – if future mini-series articles touch upon other object models, the theory will remain the same. That’s great for you, because you only need to understand it once, and it’s great for me, because I can shamelessly recycle content and re-package it as insight.

Let’s dive into a worked example. Let’s say you’re working for a life insurer that maintains a series of manual calculation spreadsheets (that would be most life insurers at some point or another). Your manager asks you to do a quick exercise to assess the library of c.100 spreadsheets; they want to know whether those spreadsheets have any VBA in them, and if so, they want a list of each function and sub-routine (including the module it’s in). This way, the team can better understand if there is any overlap in code and how consistent the module, function and sub naming conventions are within the library. As a manual task? Sounds like a nightmare, right? Open each one, manually trawl through the code and copy down boring things about it like its name and module of origin. That sounds like the worst kind of job – time-consuming and menial.

Well, with VBA, it’s much simpler and much less time-consuming. The code below opens a series of workbooks (representing this library of c.100 spreadsheets), and for each one outputs the set of functions/procedures in it.

Without going into too much detail, the key algorithmic steps here are:

  1. Once the workbook is open, loop through all VBComponents.
  2. In each one, locate the first procedure and return its name and length in lines, x.
  3. Move x lines down to get to the next procedure.
  4. Output the name of the module and procedure, and decode the type of procedure into something readable (the ProcTypeName function does this job).

This is a relatively simple example that can be powerful in an office setting. It could potentially save hours of effort and a lot of human error, and it leaves behind an audit trail of the steps followed to gather the information, which might make peer review and eventual reporting easier.

Unlike the class modules mini-series article, there’s nothing conceptually new above. The code logic is standard VBA; there are no properties that have to be wrangled and no interaction from normal module to class module that needs to be set up. Instead, we’ve shifted from working with normal Excel objects to VBA objects. Instead of Worksheets/Ranges (although you can’t get away without using those – even the example above has to output the information somewhere!), we use Components/Lines. It might feel like a broken record at this point, but the logic is the same.

There are some wider points that could be explored using the object hierarchy laid out above. Here are some examples of the more interesting aspects of each object.

VBE

The VB Editor object doesn’t have that much useful material. It’s possible to use the CommandBars object attached to it to manipulate your editor to something that suits you more. But there is very little reason this would need to be applied in code rather than as a one-off personal exercise. The best it can do is serve as a reminder that there has to be a clear benefit and purpose to using these objects.

VBProject

It’s possible to check whether the VBProject is protected. This might be (a) a useful reporting output and (b) a way to stop early before trying to modify things leads to errors arising.

Additionally, with VBProject.References, it’s possible to check what references are enabled on a workbook (as at the beginning of this article), as well as add/remove references. This can be useful for situations where you want to replace a known reference with a better, more thoroughly tested one, or check for any bespoke code that wouldn’t be compatible with a different version of VBA, e.g. one on a MacBook.

VBComponents

While the VBComponent object doesn’t offer much beside access to the CodeModule, the collection object of VBComponents allows a user to add/remove code modules. This isn’t really possible (or recommended) for sheet objects, but tidying up empty standard modules can be helpful. It’s also potentially useful to import a module which has been saved/exported from another workbook.

CodeModule

This is where the bulk of useful things can be done. The above is a really good example of the kind of thing offered by CodeModules, but in the next section we’ll explore some actuarial applications.

Some Actuarial Applications

Below we outline some areas of actuarial work where the Extensibility library can be really helpful.

Code Management

If a company relies quite heavily on VBA, say in the example of manual calculation tools above, there’s going to be multiple developers who work with different styles and who may vary in quality of development. It’s possible to use the Extensibility library to apply some level of consistent controls to this problem. For example:

  1. You could do something like the above worked example, where management can quite easily obtain information about the extent to which there is inconsistency in the naming of subs/functions, or extend this to variable naming or module naming.
  2. You could provide a tool that actually fixes some aspect. For example, it would be relatively achievable to build a code indenter that automatically applies a company-wide policy on code indentation to all the in-scope spreadsheets. This would ensure good spacing and better readability.
  3. For large projects which are more based on VBA than Excel components, the size of the workbook isn’t a useful proxy for the scale of the model, and therefore the difficulty of maintaining it. Returning the total lines of code in the VBProject object for each spreadsheet might provide some core context when, for example, estimating timelines or resource requirements relating to making a regulatory change to these spreadsheets.

Shared/Common Functionality

There are plenty of circumstances in which someone (usually an enterprising employee) builds a piece of functionality which is really quite useful and portable in their model. A simple example of this is a function in a set of projection spreadsheets which rounds outputs to a custom number of significant figures.

With the Extensibility library, it’s very much possible to map that function across to any number of spreadsheets so it can be used liberally and consistently. Of course, an alternative and perhaps more elegant solution would be to maintain it in some sort of add-in that you could then attach to each spreadsheet (as a reference, actually!) – but there are good business reasons why that may not be a desirable outcome.

The piece of code could either:

Of course, not every insurer will find these uses helpful. Many insurers don’t maintain so many spreadsheets, or already have a fairly mature system for managing them. Others may steer clear of using VBA, and it’s entirely possible to mostly avoid it within actuarial work. A good rule of thumb is: the more heavily VBA is relied upon, the more useful something like the Extensibility library will be.

Equally, as we will see when other libraries are explored, there’s no imminent need to delve deep into practising with the Extensibility library. As mentioned above, the skills are transferable from regular VBA; you’re just working with a slightly different set of objects capable of different things. It’s more useful to be aware of the possibilities that can be opened up. The Extensibility library is not, in general, a very well known part of the language.

Nevertheless, very few people in our industry are able to entirely avoid spreadsheets and VBA, especially in the earlier stages of their careers. I won’t be placing a bet that you’ll use the code/structures outlined in this article, true. But it may not be advised to bet against that either.

Summary

Let’s recap what has been covered:

If there is a clear takeaway from this article that it would be useful for you to digest, it’s that the Extensibility library exists and it can be useful, even if, in most circumstances, it won’t be. That said, any company that relies quite heavily on large quantities of VBA code might want to think about where they can get some significant wins at very little cost. In that sense, the Extensibility library can, at the very least, be a useful string to your bow alongside more commonly known VBA practices.

John Nicholls

June 2024