VBA Regex

What is a Regex?

Regex stands for Regular Expression is basically a pattern matching strings within another string. They are supported in many languages, including .net, C++, Python, etc. They are necessarily a sequence of characters that act as a search algorithm and matches the pattern. Excel offers quite a bit of text functions, but none of them can compare with regular expressions. Regular Expressions are often used to are used to validate website input or user input matches with the data, search for word patterns in large strings/texts with a specific pattern.

Regex Expressions

The Regex expressions are evaluated through matching expressions and Quantifiers.

Matching Expression

Syntax	Description	Example	Match
.	It signifies all the characters except vbNewLine (newline).	d.n	dan, don, d@n, dwn
[character]	It matches the given characters present in between the brackets [ ].	[dn]	It Would only match “d” or “n” in don
[^character]	It is used to match the characters that are not being one of the provided between brackets [ ]	[^dn]	It would only match “o” in “don”
[start-end]	It matches any character belonging to the character range specified between brackets [ ]	[1-5]	It would match “1” and “5” in “815”
\w	It represents the word character including alphabets, modifiers, digits, punctuation and connectors.	\w	It would match “c” in “@@#,c.”
\W	It represents any non-word character.	\W	It would match “#” in “bb#bb”
\s	It signifies any white space character.	\s	It will match ” ” in “Hi This is”
\S	It represents any non-white space character	\S	It would match “K” and “h” in “K h”
\d	It represents any decimal digit in between 0-9.	\d	It would match “8” in “j8h”
\D	It represents all characters except non-decimal digit.	\D	It would match j in “4723p”
\	This expression is followed by any special character – escapes special characters.	\.	It would match “.” in “889.paQ”
\t	This parameter represents tab.	\t	It would match a tab character
\r	This expression represents the Tab value (vbTab)	\r	It would match a carriage return (vbCr)
\n	This expression represents the Carriage and returns a new line (vbNewLine).	\n	It would match a new line

Quantifiers

The quantifiers are used to specify the number of times the user wants to match the pattern against the string.

Syntax	Description
*	It signifies zero or more of values. It matches as many as values as possible.
+	It represents one or more of values and matches as many as possible values.
?	If it is zero or once it matches as many as possible
{n}	It repeats the values “n” many times
{n,}	It repeats the values at least “n” times
{n,m}	Between “n” and “m” times (GREEDY)
*?	Zero or more of (non-GREEDY). Matches as few as possible
+?	One or more of (non-GREEDY). Matches as few as possible
??	It signifies zero or once and matches as few as possible
{n,}?	At least “n” times (non-GREEDY). Matches as few as possible
{n,m}?	It represents values between “n” and “m” times and matches as few as possible values.

Regular Expression Language

The Regular Expression object has the following four properties:

• Pattern – It represents the pattern with regex expression that you want to match against.
• IgnoreCase – This property will ignore the letter case (upper case/ lower case). It will only match the pattern in the string irrespective of its case.
• Global – This property finds all the possible matches in the input string. If it is set to Boolean false, it only matches the first found pattern.
• MultiLine – This property helps to match the pattern across line breaks.

The Regular Expression object has the following 3 operations:

1. Test (string) – This operation returns a Boolean True if the pattern gets matched against the given string
2. Replace (search-string, replace-string) – This operation replaces the occurrences of the pattern in search-string with replace-string
3. Execute (search-string) – This operations returns all matches of the pattern computed against the search-string.

Steps to create Regex in VBA

The steps to create Regex in VBA are as follows:

Step 1: Open the Visual Basic Editor by pressing Alt + F11. Go to Tools and select the References option to enable the Regex functionality.

Step 2: The VBA references option list will appear to the VBA Project. Scroll down and select the “Microsoft VBScript Regular Expression 5.5” option.

Step 3: Now click on OK button on the right side of the dialog box. Now, we can access this Regex object in the VBA coding.

Step 4: Set the object for Regex variable.

Program:

Sub VBA_Regex_Program()
Dim stringVar As String
Dim regexVar As Object
‘Declaring the object for Regex
Set regexVar = New RegExp

Step 5:  Define the regex pattern and methods as per the program’s demand. Here in the below program we have set a pattern for “A.C” so find all the possible matches that includes A.C (A, any character, c).

Program:

Sub VBA_Regex_Program()
Dim stringVar As String
Dim regexVar As Object
‘Declaring the object for Regex
Set regexVar = New RegExp
‘defining the methods
regexVar.Pattern = "A.C"
regexVar.Global = True
regexVar.IgnoreCase = IgnoreCase
stringVar = "HIABC-1289C-AVC-1A289C-ANC"

Step 6: Setting the match for the regex variable.

Program:

Sub VBA_Regex_Program()
Dim stringVar As String
Dim regexVar As Object
‘Declaring the object for Regex
Set regexVar = New RegExp
‘defining the methods
regexVar.Pattern = "A.C"
regexVar.Global = True
regexVar.IgnoreCase = IgnoreCase
stringVar = "HIABC-1289C-AVC-1A289C-ANC"
‘setting the match value
Set theMatches = regexOne.Execute(stringOne)

Step 7: Running the foreach loop to find the match throughout the given string variable.
The entire scope of program is given below. Press F5 to run the output for the program.

Program:

Sub VBA_Regex_Program()
Dim stringVar As String
Dim regexVar As Object
‘Declaring the object for Regex
Set regexVar = New RegExp
‘defining the methods
regexVar.Pattern = "A.C"
regexVar.Global = True
regexVar.IgnoreCase = IgnoreCase
stringVar = "HIABC-1289C-AVC-1A289C-ANC"
‘setting the match value
Set theMatches = regexOne.Execute(stringVar)
For Each Match In theMatches
  Debug.Print Match.Value
Next
End Sub

Output for the above program

You will get the output at the immediate window. Click on insert -> Immediate Window.