London

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

Edinburgh

Scott House,
10 South St Andrew Street,
Edinburgh
EH2 2AZ
0207 112 8493
View map

Dublin

24A Baggot Street Upper
Ballsbridge
Dublin
D04 V970
+44 207 112 8493
View map

Registered Office

Phoenix House,
1 Station Hill,
Reading
RG1 1NB
0207 112 8493
View map

Send us a message

CLOSE X
Contact Us

VBA Mini-Series – Regex

To think about standard brackets, ( or ), think about your high school Maths lesson in BODMAS – a standard technique to remember the order of various mathematical operations (or BIDMAS if you like – I’m not here to start a war). Under the BODMAS system, any operations enclosed in brackets are prioritised ahead of others. Exactly the same is true when working with regexes. Similarly, when brackets are enclosed within brackets, the expressions in the innermost level of brackets are evaluated first. So, in our example, we would work out what it means by considering ,? first and then considering it as part of the expression really(,?)\s.

We’re almost at the end of deciphering our example fully. All we need to know is what this part means:

(really(,?)\s)*

Looking at this in order of precedence:

  1. A question mark, ?, indicates that the immediately previous expression should be present exactly 0 or 1 times. So, within the innermost brackets above, we are fine for either ‘really, ’ or ‘really ’ to appear (noting there is whitespace at the end of both strings).
  2. An asterisk, *, indicates that the immediately previous expression could be present 0, 1, 2, … times in a row. So we are fine for any number of repetitions of the expression within the outermost bracket to appear. This ranges from the appearance of an empty string (e.g. in ‘I love VBA’) right through to the appearance of ‘really, really, really ’ or indeed ‘really really really really really really really ’ (again noting the whitespace at the end of these strings).

And with that, we have fully deciphered our example! At this point, try piecing everything we’ve covered above together just to confirm you’re happy that the example regex would indeed match with strings used at the start of this section, or even come up with some more.

It is fair to say that the limits of regex do not stop with this example. There are several other techniques and symbols that aren’t demonstrated here, but hopefully we’ve put you in the right mindset with which to think about the rules of regular expressions and how to break these down. For those interested to learn more, an extensive list of rules can be found here online.

Regex in VBA

Once you have mastered the notation of regexes, they are actually rather simple to use in VBA. To get started, you need to add the Regex library to your workbook. To do this, go to Tools -> References and tick ‘Microsoft VBScript Regular Expressions 5.5’.

This gives you access to all the ingredients you need to begin working with regexes in VBA. Rather simply, there are only a few such ingredients. Similarly to what we recommend in previous articles in this series, we can first check what new objects are available by pressing F2 to view the object browser and selecting ‘VB_Script_RegExp_55’ in the top-left dropdown.

Unlike in the last article on the Extensibility library, there are only a small number of objects here – four in fact. The main object of interest is the RegExp object. This has the following properties:

While we’re at it, let’s list the three functions associated with the RegExp object since there are so few:

But of course, it’s going to take some examples to truly understand these functions and properties. We demonstrate all except the Multiline() property over three examples below.  Firstly, here is the VBA code used to set these up (here, the Regex module has already been added):

Note the following key points from the code:

Looking at these three points, it’s easy to see that this code is not revolutionary compared to what you’re likely used to seeing. We have just introduced a new type of object, with associated functions and properties.

Example 1 – Test() function

The Test() function is a straightforward check of whether a Regex pattern is found within a string. We have three ‘target strings’ and we are looking to check whether our Regex pattern exists within them.

The output in the Immediate window means the first two strings returned a match and the third did not, just as we wanted.

We’ve performed the matching just by setting the .Pattern() property of regexObject1 to the regex we are interested in. All of the other properties have been left as their defaults for now.

Example 2 – Replace() function

As you’d expect, the Replace() function allows you to replace any text that matches with the regex pattern.

There are three different parts to this example, demonstrating the IgnoreCase and Global properties:

2a. (IgnoreCase = False, Global = False)

Two substrings match the pattern here, as regexObject2 is looking for ‘love’ in lower case specifically. Only the first instance has been replaced, because Global = False.

2b. (IgnoreCase = False, Global = True)

The same two substrings match the pattern here, as regexObject4 is still looking for ‘love’ in lower case specifically. But now both instances have been replaced, because Global = True.

2c. (IgnoreCase = True, Global = True)

Three substrings now match the pattern, as regexObject3 does not care what case letters are in. All of these substrings have been replaced because Global = True. Note also in this case that the final match was with ‘REALLY love’, in contrast to Example 2b, in which the final match was just with ‘love’ since case is now not a consideration.

Example 3 – Execute() function

The Execute() function returns an object with all substrings that match the regex pattern.

Above is the same combination of regex pattern and target string that we used in Example 2c, and we can see that the three substrings replaced in that example are those in the output object.

Note that love_match and love_matches were both set up as objects. In the above code, love_matches is set to what is called a MatchCollection object.

Practicalities

We’ve seen some simple examples of the functionality that regexes and the VBA Regex module can provide, but now we draw attention to how that functionality can be used for practical purposes.

Actuarial applications (or lack thereof!)

First of all, we recognise that it is perhaps not immediately clear how the Regex module could fit into an actuary’s day-to-day work. Admittedly, there are few actuarial use cases that would not be better solved using a simple solution using (more commonly recognised) string operations, e.g. ‘find all results where [string variable] = X’.

However, in selected cases where you wish to find more complicated patterns, the Regex module would be the appropriate choice. The skill is knowing where and when to use the hammer, rather than using the hammer on every nail. This might not sound like the most satisfying and exciting conclusion, but it’s a nuanced issue.

That said, there are many useful takeaways from looking into the VBA Regex module. For starters, it provides a useful way for those familiar with VBA to get used to learning regex syntax. This is useful because the same syntax is used across many languages, and is rather common in data analysis work, which actuaries often undertake. This type of work is more common in languages like Python, and it is rare that VBA would be the tool of choice – hence understanding regex conceptually is perhaps the more helpful thing.

As we’ve been going through the functionality of the Regex module, hopefully you have been able to form your own opinion on whether it could be useful for you practically.

Zooming out, though, one major advantage of the VBA Regex module is its simplicity. In this article, we were able to list the four properties and three functions associated with a RegExp object relatively quickly and, after a few examples, it is not too difficult to see how you might create something to pattern-match. In this way, for little cost, you can add a useful tool to your kit.

Considering the above arguments, regex syntax is probably one of those things that you should be aware of, but should be googled as and when needed to remind yourself of specific notation or rules. It would be difficult for most actuaries to flawlessly input the syntax at the drop of a hat. It should also not be shoehorned into every situation when there are simpler solutions available.

So there you have it – a new trick for you to take away. Why not try it out for yourself today? The early bird does catch the worm, after all.

 

Ross Witney-Hunter

October 2024

 

 

 

 

 

-->
April 2026 Exam Results

The April exam session got off to a familiar start, with the return of tube strikes to really challenge our staff’s organisational skills in reaching exam centres. To add insult to injury, some staff ended up travelling much further than originally intended! That certainly didn’t stop our candidates from performing well! APR’s overall pass rate […]

READ MORE
Self-Driving Cars: A New Revolution for Motor Insurance

Jaden Fivaz explores how driverless cars could reshape the motor insurance industry.

READ MORE
Testing Actuarial Models

Johnny Breen provides a crash course in testing actuarial models. Dive in to learn more!

READ MORE
Football Predictions
Predicting the Premier League

Can Excel really predict the Premier League? Tom Uden put that idea to the test, using a simple model to track the title races and relegation battles this season.

READ MORE
23rd Conference on Bulk Annuities Recap

Kevin Li recaps some of the key topics in the 23rd edition of the Westminster and City Bulk Annuities Conference

READ MORE
User Experience in Pricing Model Design

In this article Jacob Warbrick highlights some key considerations when designing pricing models with user experience in mind.

READ MORE
Embedded AI in Actuarial Workflows: Opportunities, Controls, and the New Discipline of Prompt Engineering

David McCarthy explores the opportunities AI brings to actuarial work and shares how to best take advantage of them through prompt engineering

READ MORE
APR 2025 Charity Recap

Last year was a great year for APR charity involvement, we continue to support charities that are important to our staff and it has been insightful learning about the charities, the work they do and just how important donations are to them. Lots of staff got involved with Give As You Earn (GAYE) which means […]

READ MORE
GIRO Conference 2025

Ross, Morgan & Jack provide an engaging recap of GIRO 2025. If you couldn’t make it, get the key takeaways here.

READ MORE
Sinter Klaus

Maximising My Expected “Present” Value This Christmas Sinter Klaus is a present-swapping game that my (Tom’s) partner’s family play every year, where a die is rolled to determine whether you can steal (swap) someone else’s present in exchange for the one you were allocated. By making assumptions about the quality of the presents, and the […]

READ MORE