VBA – RegEx Used to Take the Valuable Data Out

Writing for RegEx in VBA is sometimes a good idea and sometimes a bad idea. Anyway, if you need to write one, you better be careful.

These are the RegEx articles in VBA in VitoshAcademy so far:

• https://www.vitoshacademy.com/vba-regex-in-excel-part-2/
• https://www.vitoshacademy.com/vba-regex-in-excel/

So, imagine that you have some interesting strings that need to be processed. The first thing to do is to see whether it could be processed and the second thing is to return the processed string. Something like these two similar functions would work out nicely:

Public Function ReturnCombination(regExStr As String, compareWith As String) As String

    Dim regEx           As Object
    Dim regExString     As String
    Dim matches         As Object
    
    Set regEx = CreateObject("VBScript.RegExp")

    With regEx
        .Pattern = regExStr
        .IgnoreCase = True
        .Global = True
        
        If .test(compareWith) Then
            Set matches = .Execute(compareWith)
            ReturnCombination = matches(0)
        Else
            ReturnCombination = False
        End If
        
    End With
    
End Function

Public Function CheckCombination(regExStr As String, compareWith As String) As Boolean

    Dim regEx           As Object
    Dim regExString     As String
    Dim matches         As Object
    
    Set regEx = CreateObject("VBScript.RegExp")

    With regEx
        .Pattern = regExStr
        .IgnoreCase = True
        .Global = True
        CheckCombination = .test(compareWith)
    End With

End Function

Public Sub Main()
    
    If CheckCombination("[0-9]{2}-[0-9]{2}", "abc123123-12-1") Then
        Debug.Print ReturnCombination("[0-9]{2}-[0-9]{2}", "abc123123-12-1")
    End If
    
End Sub

Cheers! 🙂