VBA Regular Expression

VBA Regular Expression

Open Excel VBA project, @Tools\Reference…
Check “Microsoft VBA Regular Expression 5.5”, click “Ok”

Now the Regular Expression engine is at your service.

Add a module in your VBA project, copy & paste below code snippets to test the power of Regular Expression:

‘—————————————————————————————————
Const BatchNoPattern As String = "^[1]\d{7}(\(\d*\))?$|^[0,2-9,a-z,A-Z].*$"

Function GetUserName()
GetUserName = Environ$("Username")
End Function

Function IsValidPattern(ByVal MyData As Variant, Optional ByVal MyPattern As String = BatchNoPattern, Optional ByVal MyIgnoreCase As Boolean = False) As Boolean
' // defines an Object used for Regular Expression (late binding)
Dim objRegExp As Object
Set objRegExp = CreateObject("VBScript.RegExp")

' // set Case
objRegExp.IgnoreCase = MyIgnoreCase

' // set Pattern
objRegExp.Pattern = MyPattern

' // return True for a valid match or False for an invalid match

IsValidPattern = objRegExp.test(MyData)
Set objRegExp = Nothing
End Function

Private Sub test()
Dim b1 As Boolean
Dim b2 As Boolean
Dim b3 As Boolean
Dim b4 As Boolean
b1 = IsValidPattern("12345678", "^[1]\d{7}$")
b2 = IsValidPattern("1aa234567873737x", "^[0,2-9,a-z,A-Z]\w+$")
b3 = IsValidPattern("12345678d", "^[1]\d{7}$|^[0,2-9,a-z,A-Z]\w+$")
b4 = IsValidPattern("12345678d", "^[1]\d{7}$|^[0,2-9,a-z,A-Z].*$")


b4 = IsValidPattern("112345(a)", "^[1]\d{7}(\(\d*\))?$|^[0,2-9,a-z,A-Z].*$")
'MsgBox CStr(b1) & vbCrLf & CStr(b2) & vbCrLf & CStr(b3) & vbCrLf & CStr(b4)
MsgBox CStr(b4)
End Sub
‘—————————————————————————————————