VBA Regex

Regular Expressions are used to match patterns in strings of text. To work with Regular Expressions in VBA use the VBScript RegExp object in the Microsoft VBScript Regular Expressions 5.5 Library.

Option Explicit

Public Sub Example()

    Dim Regex           As Object 'RegExp
    Dim Matches         As Object 'MatchCollection
    Dim M               As Object 'Match
    Dim ReplacedText    As String
    Dim SearchText      As String
    Dim SearchPattern   As String

    SearchPattern = "\w+"
    SearchText = "Hello, World!" & vbNewLine & "Hello, World!"

    Set Regex = CreateObject("VBScript.RegExp")

    With Regex

        .Pattern = SearchPattern
        .Global = True
        .IgnoreCase = False
        .MultiLine = True

        Debug.Print .Test(SearchText) 'Prints True
        Set Matches = .Execute(SearchText)
        ReplacedText = .Replace(SearchText, "Test")

    End With

    Set Regex = Nothing

    For Each M In Matches
        Debug.Print M.Value, M.FirstIndex, M.Length
        'Prints:
        '  Hello          0             5
        '  World          7             5
        '  Hello          15            5
        '  World          22            5
    Next M

    Debug.Print ReplacedText
    'Prints:
    '  Test , Test!
    '  Test , Test!

End Sub