VBA Regex

This tutorial will demonstrate how to use Regex in VBA.

What is Regex?

Regex stands for regular expression. A regular expression is a pattern made up of a sequence of characters that you can use to find a matching pattern in another string. In order to use Regex in VBA you have to use the RegExp object.

A pattern such as [A-C] can be used to search for and match an upper case letter from A to C from a sequence. Regex patterns have their own syntax and can be built using a character or sequence of characters.

Matching Characters

The following table shows the syntax which will allow you to build Regex patterns.

Pattern Syntax	Description	Example	Matches Found
.	Matches any single character except vbNewLine	f.n	fan, fon, f@n, fwn
[characters]	Matches any single character between brackets[]	[fn]	Would only match “f” or “n” in fan
[^characters]	Matches any single character that is not between brackets[]	[^fn]	So would match “j” in “fjn”
[start-end]	Matches any character that is part of the range in brackets[]	[1-5]	Would match “4” and “5” in “45”
\w	Matches alphanumeric characters and the underscore, but not the space character	\w	Would match “c” in “,c.”
\W	Matches any non-alphanumeric characters and the underscore	\W	Would match “@” in “bb@bb”
\s	Matches any white space character such as spaces and tabs	\s	Would match ” ” in “This is”
\S	Matches any non-white space character	\S	Would match “T” and “h” in “T h”
\d	Matches any single decimal digit	\d	Would match “7” in “a7h”
\D	Matches any single non-decimal digit	\D	Would match j in “47j”
\	Escapes special characters which then allows you to search for them	\.	Would match “.” in “59.pQ”
\t	Tab	\t	Would match a tab character
\r	Carriage Return	\r	Would match a carriage return (vbCr)
\n	vbNewLine(vbTab)	\n	Would match a new line

Quantifiers

You can use quantifiers to specify how many times you want the pattern to match against the string.

Quantifier	Description	Example	Matches Found
*	Matches zero or more occurrences	fn*a	fna, fa, fnna, fnnna, fnfnnna
+	Matches one or more occurrences	fn+a	fna, fnna, fnfnna
?	Matches zero or one	fn?a	fa, fna
{n}	Matches “n” many times	d\W{4}	Would match “d….” in “d….&5hi”
{n,}	Matches at least “n” number of times	d\W{4,}	Would match “d….&” in “d….&5hi”
{n,m}	Matches between n and m number of times	d\W{1,8}	Would match “d….&&&&” in “d….&&&&5hi”

Grouping

Grouping or capturing allows you to use a pattern to capture and extract a portion of a string. So not only is the pattern matched, but the part of the string that matches the pattern is captured.

Pattern	Description	Example	Matches Found and Captured
(expression)	Groups and captures the pattern in parenthesis	(\W{4})	Would group and capture “@@@@” from “1@@@@1jlmba”

How to Use Regex in VBA

In order to use Regex in VBA, you first have to set the reference in the VBE editor. In the VBE editor, go to Tools > References > Microsoft VBScript Regular Expressions.

These are the properties of the RegExp object:

• Pattern – The pattern you are going to use for matching against the string.
• IgnoreCase – If True, then the matching ignores letter case.
• Global – If True, then all the matches of the pattern in the string are found. If False then only the first match is found.
• MultiLine – If True, pattern matching happens across line breaks.

These are the methods of the RegExp object:

• Test – Searches for a pattern in a string and returns True if a match is found.
• Replace – Replaces the occurrences of the pattern with the replacement string.
• Execute – Returns matches of the pattern against the string.

Testing a Pattern for a Match Against a String

You can use the Test method to check whether a pattern matches a sequence in the input string. The result is True if a match is found. The following code will show you how to test a pattern against a string:

Sub RegexTestingAPattern()

Dim stringOne As String
Dim regexOne As Object
Set regexOne = New RegExp

regexOne.Pattern = "f....a"

stringOne = "000111fjo88a8"

Debug.Print regexOne.Test(stringOne)

End Sub

The result is:

Replacing a Pattern in a String

You can use the Replace method to replace the first instance of a matching pattern in a string or all the instances of a matching pattern in a string. If Global is set to False, then only the first instance is replaced. The following code will show you how to replace a pattern in a string:

Sub RegexReplacingAPattern()
Dim stringOne As String
Dim regexOne As Object
Set regexOne = New RegExp

regexOne.Pattern = "This is the number"
regexOne.Global = False
stringOne = "This is the number 718901"

Debug.Print regexOne.Replace(stringOne, "That is the new number")
End Sub

The result is:

To replace only the number portion of the string used above, you would use the following code:

Sub RegexReplacingAPattern()
Dim stringOne As String
Dim regexOne As Object
Set regexOne = New RegExp

regexOne.Pattern = "[^\D]+"
regexOne.Global = False
stringOne = "This is the number 718901"

Debug.Print regexOne.Replace(stringOne, "777192")
End Sub

The result is:

To replace every instance of a certain pattern in a string, you would set the global value to True. The following code shows you how to replace every instance of -A1289C- in the string:

Sub RegexReplacingEveryInstanceOfAPattern()
Dim stringOne As String
Dim regexOne As Object
Set regexOne = New RegExp

regexOne.Pattern = "\W\A\d+C\W"
regexOne.Global = True
stringOne = "ABC-A1289C-ABC-A1289C-ABC"

Debug.Print regexOne.Replace(stringOne, "IJK")
End Sub

Matching and Displaying a Pattern in a String

You can use the Execute method to match one or all instances of a pattern within a string. The following code shows you how to match and  display all instances of the pattern from the string:

Sub RegexMatchingAndDisplayingAPattern()
Dim stringOne As String
Dim regexOne As Object
Dim theMatches As Object
Dim Match As Object
Set regexOne = New RegExp

regexOne.Pattern = "A.C"
regexOne.Global = True
regexOne.IgnoreCase = True
stringOne = "ABC-A1289C-ADC-A1289C-AJC"

Set theMatches = regexOne.Execute(stringOne)

For Each Match In theMatches
  Debug.Print Match.Value
Next

End Sub

The result is:

Let’s say we only wanted to match -ADC- from the above string. The following code shows how to match and display only  -ADC- from the string:

Sub RegexMatchingAndDisplayingAPattern()
Dim stringOne As String
Dim regexOne As Object
Dim theMatches As Object
Dim Match As Object
Set regexOne = New RegExp

regexOne.Pattern = "\-\A.C\-"
regexOne.Global = False
regexOne.IgnoreCase = True
stringOne = "ABC-A1289C-ADC-A1289C-AEC"

Set theMatches = regexOne.Execute(stringOne)

For Each Match In theMatches
  Debug.Print Match.Value
Next

End Sub

Regex can take some time to learn, but it’s an extremely powerful tool for identifying/manipulating strings of text. It’s also broadly used across programming languages.