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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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! 🙂