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

SyntaxDescriptionExampleMatch
.It signifies all the characters except vbNewLine (newline).d.ndan, 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.\wIt would match “c” in “@@#,c.”
\WIt represents any non-word character.\WIt would match “#” in “bb#bb”
\sIt signifies any white space character.\sIt will match ” ” in “Hi This is”
        \SIt represents any non-white space character\SIt 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”
\DIt represents all characters except non-decimal digit.\DIt would match j in “4723p”
\
This expression is followed by any special character – escapes special characters.  

\.  
It would match “.” in “889.paQ”
\tThis parameter represents tab.\tIt would match a tab character
\rThis expression represents the Tab value (vbTab)\r
It would match a carriage return (vbCr)  
\nThis expression represents the Carriage and returns a new line (vbNewLine).\nIt 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.

SyntaxDescription
*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.

Steps to create Regex in VBA

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.

Steps to create Regex in VBA

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.

Steps to create Regex in VBA

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
Steps to create Regex in VBA

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"
Steps to create Regex in VBA

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
VBA Regex

Output for the above program

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

VBA Regex