Next-Level VBA: Code That Does More than Just Work
As the surprisingly candid VBA series comes to an end, we take a moment to celebrate it. In this series, we dived into the bits of VBA people often pretend to understand, but secretly Google. From class modules to parallel processing, from Regex to extensibility, we’ve unpacked what makes VBA tick, why actuaries keep coming back to it, and how, when wielded well, it can still surprise you with its muscle.
If you’ve ever stared down a 20MB spreadsheet and thought, “There has to be a better way,” this series is for you.
The series begins with an honest assessment of VBA. Its shortcomings—its clunky syntax, its lack of libraries, its inability to compete with R or Python for elegance or scale. But we also highlight its quiet strengths: VBA is embedded, accessible, and often the lowest-risk tool available in the actuarial toolkit. In short, it gets the job done.
In the first real dive of the series, we shift focus to class modules, introducing VBA’s take on object-oriented programming. While it lacks features like inheritance and full polymorphism, VBA still supports encapsulation—a powerful concept when building reusable and maintainable code. We walk through practical examples, showing actuaries where class modules can bring order to complexity, especially in scenarios like modelling cashflows or managing structured data.
Here we explain how to make your VBA better using its massively useful but little-known suite of extensions. Christopher Nolan might not be interested in it, but if VBA could dream, the Extensibility library would be its Inception moment.
Yes, it’s VBA writing about VBA.
Managing dozens or even hundreds of VBA-powered spreadsheets can be overwhelming—but in this article, we show how the VBA Extensibility library can help teams automate this exact task.
Ross examines the applicability of regular expressions in VBA for data analysis tasks in our first guest feature. While standard string functions handle most actuarial needs, regex shines when parsing complex formats or pulling meaning from unstructured data. It’s not for everything, but when you need it, nothing else will do.
Multi-Threading and Parallel Processing
Performance problems? It’s probably not you—it’s your computer doing things the slow way. This installment explains how multi-threading and parallel processing (outside VBA) can supercharge model performance. If your Excel file ever froze for 10 minutes, you’re probably going to want to read this.
Understanding Interfaces in VBA
Rounding out the series is a guide to interfaces in VBA—a logical next step after mastering class modules. While often overlooked, interfaces help create consistent, modular, and scalable VBA code. With practical metaphors and examples, we demystify a concept that can elevate your VBA from functional to elegant.
Who knew reading about VBA could be this much fun?
Now if only debugging were half as enjoyable.