VBA – RegEx in Excel

Posted on October 20, 2017 by

There is a well-known joke in the IT world – if you have a problem and you try RegEx to solve it, now you have two problems.

Still, RegEx in VBA (and thus in Excel) exists as an additional library. If you are not a fan of asking people to add libraries to your project, in VBA you may use the late binding. This is a simple example of a custom excel formula for verification of an email through RegEx:

Option Explicit

Function RegExample(inputString As String, _
                    Optional globalRe As Boolean = True, _
                    Optional ignoreCase As Boolean = True) As Boolean
    
    Dim re As Object
    Set re = CreateObject("vbscript.regexp")

    With re
        .Global = globalRe
        .Pattern = "^[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})$"
        .ignoreCase = ignoreCase
        RegExample = .test(inputString)
    End With

End Function

Option Explicit

Function RegExample(inputString As String, _

Optional globalRe As Boolean = True, _

Optional ignoreCase As Boolean = True) As Boolean

Dim re As Object

Set re = CreateObject("vbscript.regexp")

With re

.Global = globalRe

.Pattern = "^[_a-z0-9-]+(.[a-z0-9-]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,4})$"

.ignoreCase = ignoreCase

RegExample = .test(inputString)

End With

End Function

And this is how it looks in Excel:

Tagged with: email verification, Excel, regex, VBA

VBA – RegEx in Excel

Related posts: