VBA Regex

Typical Replacement Example

Dim objRegExp As Object ' RegExp	 using Microsoft VBScript 5.5				
Set objRegExp = CreateObject(vbscript.regexp)					
Dim res As String					
res = Some text to stuff around with					
objRegExp.Global = True           ' match all instance					
objRegExp.IgnoreCase = True					
' Multi-space to space					
objRegExp.Pattern = [ ]+					
res = objRegExp.Replace(res	  )				

Matching Sub Strings

Dim regEx As New regexp Dim matches 					
Dim s As String Dim m As match 					
Dim sm As SubMatches ' sub matches 					
Dim ms ' match sub	 string regEx.pattern = ^(\w+)-(\d+)\s*(\w+)\s*(.*)\.(.*)$ 				
regEx.IgnoreCase = True 'True to ignore case 					
regEx.Global = True 					
'True matches all occurances	 False matches the first occurance 				
If regEx.test(myFilename) Then 					
Set matches = regEx.Execute(myFilename) 					
If matches.Count <> 1 Then GoTo matchFail 					
Set sm = matches(0).SubMatches ' MUST get submatches to retrieve the matched text. Usually get matches(0) (ie. the first match) 					
If sm.Count <> 5 Then GoTo matchFail 					
myCreditCat = sm(0) ' ie. This is matches(0)(0) : first match's first sub-match 					
myCreditNum = CInt_Safe(sm(1)) 					
myDocCode = sm(2) 					
myDocTitle = sm(3) 					
myFileExt = sm(4) 					
Else GoTo matchFail 					


Non-printable characters excluding \r \n
Break this down:
Wat-5   Mooses.suffix

Matching Simple Function

' Return the first match. For example
' pattern = "- (.*)"
' text = "- asd this"
' return "asd this"
Public Function RegExSimpleMatch(pattern As String, s As String) As String
    Dim re As New RegExp
    Dim ms As MatchCollection
    Dim sms As submatches
    On Error GoTo huh
        re.pattern = pattern
        re.IgnoreCase = True 'True to ignore case
        re.Global = True
        If re.Test(s) Then
            Set ms = re.Execute(s)
            Set sms = ms(0).submatches()
            RegExSimpleMatch = sms(0)
            RegExSimpleMatch = ""
        End If
    Exit Function
    Debug.Print Err.Description
    RegExSimpleMatch = ""
End Function

Private Sub RegExSimpleMatchTEST()
    Debug.Assert (RegExSimpleMatch("^-(.*)", "nothing") = "")
    Debug.Assert (RegExSimpleMatch("^-(.*)", "-something") = "something")
    Debug.Assert (RegExSimpleMatch("^-(.*)", "--something") = "-something")
    Debug.Assert (RegExSimpleMatch("^-(.*)", " - -nothing") = "")
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *