Regular Expressions Testing in VBA

There are two main functions for invoking Regular Expressions and checking if a given string matches a test pattern.

Instantiate the RegExp Object

Function GetRegEx() As Object
  On Error Resume Next
  Set GetRegEx = CreateObject("VBScript.RegExp")
End Function

Sample Usage

Sub TestRegex()
  Dim regex As Object  ' RegExp
  Set regex = GetRegEx
  If Not regex Is Nothing Then
    ' rest of your code here
  End If
End Sub

Test a given string against a given test pattern

This code requires three parameters:

• the RegEx object (which you create using the function above),
• the RegEx pattern, and
• the string to you want to test against the RegEx pattern.

Function TestRegEx(regex As Object, testpattern As String, stringtotest As String) As Boolean
  Dim regMatch As Object  ' MatchCollection

  With regex
    .Pattern = testpattern
    .MultiLine = False
  End With

  ' match test string against regex string
  Set regMatch = regex.Execute(stringtotest)
  TestRegEx = (regMatch.Count > 0)
End Function

The function returns True if the input string matches the pattern.

Sample Usage

Sub TestRegex()
  Dim regex As Object  ' RegExp
  Dim pattern As String
  Dim inputstring As String

  Set regex = GetRegEx
  If Not regex Is Nothing Then
    testpattern = "[0-9]{1,3}" ' 1 to 3 numbers
    stringtotest = "12"
    MsgBox TestRegEx(regex, pattern, inputstring)
  End If
End Sub