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
Dublin 4
Ireland
D 04 N5 28
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
30.01.2024

VBA Mini-Series – An Introduction

Introduction

If you had to summarise the office environment in the 21st century with one piece of hardware, it would be the computer. It you had to do the same with one piece of software, it would be the Microsoft Suite. The most obvious part of this suite is possibly Outlook – after all, aren’t we all familiar with the morning experience of opening your Inbox, ignoring the 2,431 unread e-mails, and catching up on anything new in the last 24 hours? Just me? Microsoft Outlook as an experience is nothing if not relatable, so I certainly hope it isn’t just me!

In the actuarial industry, perhaps the most iconic member of that Microsoft Suite is Microsoft Excel. Every office uses spreadsheets – some are 3KB glorified lists designed to keep a one-time set of tasks. Others are 20MB behemoths meant to process volumes of data that really ought to be processed by literally anything else (one colleague once testified they’d rather do it all by hand than wait for one more re-calculation, but I have my suspicions about their honesty).

Some spreadsheets contain the kind of cutting-edge wizardry Gandalf would be proud of (it was a coin flip deciding whether a Lord of the Rings reference should be assumed knowledge here), and others pre-date, among other things, me (I was born in 1993). They’ve been around a long time, and that explains why VBA has as well.

Why is Excel so popular?

In the actuarial world, there’s definitely been a phase shift away from Excel-based solutions in the last half-decade or so. We are seeing a particular pivot to R, which is equipped to handle larger datasets better, faster, and with more flexibility than Excel has traditionally been able to; such is the commitment to it, the IFoA’s exams have included an R component since 2019. Most actuaries reading this will be familiar with the irritation of Excel’s 1,048,576 row limit; the modern world puts big data front and centre of its problem-solving, and, as difficult to define as the word “big” is, we know it’s more than that!

A focus on other languages has been common, with Python and SQL notable candidates for building models and systems which can process information in a more flexible manner. These go alongside stalwarts in the industry such as Prophet/MoSes, specialist software designed to run hundreds, maybe thousands of iterations of the same model. Such is the love, however, for Excel, you’d be forgiven for thinking that were it not for its very basic limitations, we’d be seeing it doing all of that modelling too! That said, it’s difficult to depart from Excel, for a number of reasons:

  1. Most people are trained in it to some extent.
  2. It’s more flexible than specialist software, which usually is third-party produced and requires an expensive license (not to mention training/experience).
  3. It’s not as hostile as programming language IDEs for R/Python, which often have a mystique about them, and often have very unfriendly troubleshooting (debugging).
  4. It’s more than adequate for the vast majority of things one might need to do with numbers in the office – whether that’s tables, pivot tables, simple statistics or graphs, Excel has the functionality for it, and it’s quite intuitive.

For the reasons above, VBA, which is a rather old-fashioned programming language, and lags miles behind R/Python (to take common examples), remains very popular.

Why VBA? Why not?

To be clear, this article isn’t suggesting VBA can produce the same functionality as R or Python. There are many reasons why one should NOT use it if possible:

At the same time, VBA remains the least ‘mystical’ of the various languages in an actuarial context – because most actuarial students live and breathe Excel, they’ve invariably come across having to read, edit or write VBA code through their careers (this particularly biases by age, because R and Python are relatively new to the industry mainstream) – this fact makes it the lowest risk choice for a lot of contexts. Additionally, there’s no point relying on R or Python for simple automation of tasks in Excel (although it’s worth noting that more modern Excel technologies like Power Pivot are the true competitors to VBA for these types of task); it would be a case of over-egging the pudding!

Introducing the VBA Mini-Series

For the reasons outlined above, we’ve decided to provide a series of articles performing a shallow dive into some more advanced concepts in VBA and how they might be useful in an actuarial context. These articles will broadly cover:

  1. The mechanics of the concept
  2. Examples in the industry where they may be useful
  3. Thoughts on any limitations or alternatives

If you’re reading all this and thinking “this doesn’t interest me at all”, you sound cooler than I’ve ever been. If you’re reading it and thinking “this is exactly what I needed”, well… ONE OF US. ONE OF US. We won’t spoil the topics for you just yet, so if you’re interested, please stay patient and keep an eye out!

We’re also always keen to get feedback from our readers, so if you have any suggested topics, either because you’re a bit of a VBA whiz yourself and want to share that knowledge, or because there’s something you’ve always wanted to know how to do, please get in touch with John at John.Nicholls@aprllp.com.

John Nicholls

January 2024