VBA VBScript Regex Methods Regex

The VBA Regex supports 3 methods which are as follows:

Execute
Replace
Text

Execute

The execute method is used to extract a match from the given based on the defined matching Regex pattern. It returns an Object which retains all the matches, unlike an Array.

In the below program we have created a Function to collect all the matches found in the given string which will match the regex Pattern string and will return an object holding all the matches found in the string (unlike an array). At last, we have looped through the returned object to read all the matches found.

Syntax

Execute (String value)

Program:

Function VBA_Regex_executeMethod(regexVar As String, stringVar As String)
'***************************************************************
' Example for Execute Method of Regular Expression
'***************************************************************
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
Dim matchedObj As Object
With regex
    .Pattern = regexVar
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' The matchesObj object will store all the matches as
' returned by execute method of RegEx
Set matchedObj = rgx.Execute(stringVar)
'Loop to read all the matches found
For Each Item In allMatches
    Debug.Print Item.Value
Next
End Function

2. Replace

The Replace method is used to search a character or string based on a defined pattern, and if the pattern is found, it is replaced with a new string or character as defined by the user (i.e the replacement string takes the place of each matched string). If the regex pattern is not matched with the string, the string remains unchanged. This method returns a new string with all the matched string replaced with provided string and replaces all strings that match a regular expression pattern with a specified replacement string.

In the below program, we have created a Function to replace all the matches found in each string, which matches the regex Pattern - with a given string. This program will return a new string where all the matches are replaced with the given string.

Syntax

Replace (string input, string replacement)

Program:

Function VBA_Regex_ReplaceMethod(regexVar As String, stringVar As String)
'***************************************************************
' Example of Replace Method of Regular Expression
'***************************************************************
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
'DEefining the regex properties
With regex
    .Pattern = regexVar
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
End With
' This method will return a string wherein
' all the matched string will be replaced
' with the given string
Debug.Print regex.Replace(stringVar, "***vb@***tutori@ls***")
End Function

3. Test Method

This method is used to find whether a Regex pattern is matched in a given string. This method returns a Boolean value True if the match is found else it returns a Boolean False.

In the below example, we have created a function to check if the specified String matches with the given regex pattern and return Boolean - True or False based on the match found result.

Syntax

Test (String value)

Program:

Function VBA_Regex_TestMethod(regexVar As String, stringVar As String) As Boolean
'***************************************************************
' Example of Test Method of Regular Expression
'***************************************************************
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Pattern = regexVar
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With
    ' The test metho returns a boolean true if the match has found
    ' else it returs Boolean False for no match
    testMethodRegEx = regex.test(stringVar)
End Function