VBA Mini-Series – Regex

Introduction

I have always been confused by the term ‘regular expression’. Perhaps it’s just me, but I’ve always found I use expressions such as ‘the early bird catches the worm’ or ‘you can’t teach an old dog new tricks’ much more regularly than I would ever use:

‘Ross(‘(s?)\sarticle)?\s(changed\smy\slife)|(((was|is)\s)(rubbish|inspiring|so[-\s]so))’.

Personally, I’d at least remove ‘rubbish’ from that last one but perhaps I’m biased.

Compared to other programming languages, it is also often much more irregular to see regex used within VBA. This article first aims to give an overview of regex as a general concept. Many readers will not be familiar with much of the notation, but the opening section will serve as a useful introduction to the basics. We’ll go on to focus on using regex within VBA, taking a whistle-stop tour of the functionality of the Regex module. Fortunately, a whistle-stop tour is more than enough to cover pretty much everything the module has to offer.

Of course, we will also consider the practicalities of regex and using the VBA Regex module. Learning how to use regex to pattern-match in VBA will not provide solutions to all of your problems. But learning when and how it could be best to apply it is certainly a useful trick for dogs both young and old to have up their sleeves.

Regular Expressions (Regex)

Regular expressions, or regexes, are patterns to match within strings. When written down, these sequences are denoted using characters that come in two types:

1. Literals – interpreted ‘literally’ for pattern-matching purposes, in the same way you or I would read them.
2. Metacharacters – not to be interpreted literally, but rather as part of a generalised pattern-matching rule.

The best way to understand regexes is through examples. With this in mind, we’ll reference the following example throughout this section:

I\s(really(,?)\s)*love\s[ABV]{3, 4}

If you have no previous experience with regular expressions, this will almost certainly be jibberish to you. Fear not, as we will build up in stages, so it will (almost) certainly be a little more understandable by the end of this section.

The above is a regular expression designed to match with a class of strings including:

‘I really love VBA’
‘I really, really, really love VBA’
‘I love ABBA’

So if you’re looking to use regexes to quickly filter out CVs from application processes whilst also looking for the star of your next team karaoke night out, look no further!

Literals in our example regex would be characters like the ‘l’, ‘o’, ‘v’ and ‘e’ in love, or the ‘A’, ‘V’ and ‘B’ in ABV. These characters can be read directly in the string they are matching within. Metacharacters would include the ‘\’ and ‘s’ in \s, or ‘*’, ‘[‘ and ‘{‘. These are just representing a certain pattern in a concise way.

Because of this, literals are the easiest starting point to look out for when breaking down what a regex is trying to match. In our above example, hopefully it is clear that I, really and love are all just groups of literals that are looking for those words exactly where they are present in the pattern. How could we know this? Well, generally a series of consecutive letters in a regex should be an exact match to a pattern, unless:

1. It is surrounded by square brackets, [characters].
2. Any character in the series is preceded by \.

In the regex, love does not meet condition (1) or (2) above, so it needs to be found exactly as written. This is to say that our example above would not match to either of:

‘I really vole VBA’
‘I really lov VBA’

Let’s now look at where condition (1) applies, which leads us to our first metacharacter, the square bracket. Where a pattern is contained in square brackets, it will match with any literal contained in the pattern. So, [ABV] would match with ‘A’, ‘V’ or ‘B’. However, when considered in isolation, it would only match with a single character, i.e. [ABV] on its own would not match with ‘VBA’ or ‘ABV’.

So how then do we match patterns like ‘VBA’ or ‘ABBA’ above? The key lies in {3, 4}. Curly brackets are used to encase the number of characters over which the previous pattern in the regex should apply and the fact we’ve specified two numbers indicates a range of possible lengths. Similarly:

[ABV]{3} matches ‘VBA’ or ‘AAA’ (but not ‘ABBA’)

[ABV]{4} matches ‘ABBA’ or ‘BAVA’ (but not ‘VBA’)

[ABV]{3, 4} matches ‘VBA’, ‘ABBA’ and all patterns the two above match

(love){1, 3} matches ‘love’, ‘lovelove’ and ‘lovelovelove’

Moving on now to condition (2): a backslash, \, is used to indicate that the character immediately following should be interpreted as a metacharacter, not a literal. Different characters have different meanings when considered in this way. In our example, \s is used to search for whitespace of length one. This means that the regex would not match:

‘IreallyloveVBA’
‘I really,really love VBA’

After all, we can’t be hiring actuaries with poor ability to use AutoCorrect, no matter how great their burning passion for VBA.

Here is a list of other regex syntax that can be used in combination with a backslash:

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:

• Pattern – this is the most important property. It is a string representing a regex pattern that will be used to match against target strings.
• Global (default: False) – a Boolean indicating whether all instances of the pattern should be matched in the target string (set to True) or whether only the first instance should be matched (set to False).
• IgnoreCase (default: False) – a Boolean indicating whether matching is case insensitive, i.e. ‘b’ would match with ‘B’ when set to True.
• Multiline (default: False) – a Boolean indicating whether the pattern should be matched in each line of the target string, if the target string consists of multiple lines.

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

• Test(<string>) – simply matches a regex against a target string and returns True if there is a match.
• Replace(<old_substring>, <new_substring>) – where the regex matches within the target string, replace the old substring (the substring that matches the regex pattern) with a new substring.
• Execute(<string>) – returns a MatchCollection object that contains all the substrings within the target string that match with the regex pattern.

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:

• We have designated the type of our RegExp object variables in a similar way to how we designate types for traditional variables.
• Likewise, we have ‘set’ these variables as new objects in a similar way to how we would often ‘set’ new objects like worksheets or new items like worksheets or workbooks.
• With each RegExp object, we are setting its properties in the normal way as well.

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