<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Regex Validation Using VBA

Published on
16,714 Points
6,314 Views
4 Endorsements
Last Modified:
Awarded
MacroShadow
A man of stark contrasts, love of nature, curiosity of technology and passion of helping my fellow man.
Hello EE

Today I would like to show you how you can use one function to make multiple regular expression validations using parameters passed to the function.  

In many applications you build you may need/want to validate an expression to ensure it confirms with your expected outcome. It may be possible to do so by writing a custom function and then pass the expression to be validated.

For example you may want to validate an email address entered by a user, to insure it is in a valid format. One way of doing that is using a custom built function (know in Excel as UDF) to check the email address. There are many out there, here is one I found (unfortunately I don't remember the source):

Function IsEmailValid(strEmail As String)

    Dim strArray As Variant
    Dim strItem As Variant
    Dim i As Long, c As String, blnIsItValid As Boolean
    blnIsItValid = True

    i = Len(strEmail) - Len(Replace(strEmail, "@", ""))
    If i <> 1 Then IsEmailValid = False: Exit Function
    ReDim strArray(1 To 2)
    strArray(1) = Left(strEmail, InStr(1, strEmail, "@", 1) - 1)
    strArray(2) = Replace(Right(strEmail, Len(strEmail) - Len(strArray(1))), "@", "")
    For Each strItem In strArray
        If Len(strItem) <= 0 Then
            blnIsItValid = False
            IsEmailValid = blnIsItValid
            Exit Function
        End If
        For i = 1 To Len(strItem)
            c = LCase(Mid(strItem, i, 1))
            If InStr("abcdefghijklmnopqrstuvwxyz_-.", c) <= 0 And Not IsNumeric(c) Then
                blnIsItValid = False
                IsEmailValid = blnIsItValid
                Exit Function
            End If
        Next i
        If Left(strItem, 1) = "." Or Right(strItem, 1) = "." Then
            blnIsItValid = False
            IsEmailValid = blnIsItValid
            Exit Function
        End If
    Next strItem
    If InStr(strArray(2), ".") <= 0 Then
        blnIsItValid = False
        IsEmailValid = blnIsItValid
        Exit Function
    End If
    i = Len(strArray(2)) - InStrRev(strArray(2), ".")
    If i <> 2 And i <> 3 Then
        blnIsItValid = False
        IsEmailValid = blnIsItValid
        Exit Function
    End If
    If InStr(strEmail, "..") > 0 Then
        blnIsItValid = False
        IsEmailValid = blnIsItValid
        Exit Function
    End If
    IsEmailValid = blnIsItValid
End Function

Open in new window


As you can see, it is long and complicated. It may be better or worse depending on the expression you need to validate.

Another, shorter and easier way to validate data is using "Regular Expressions". Regular Expressions are very powerful, and exist in many programming languages. Fortunately, in VBA we may also use Regular Expressions, once we expose the VBScript.RegExp library.

The way you would use a regular expression in VBA is writing a simple function, within the function you define the pattern you need. For example to validate an email address you could use the following (this example uses late binding so there is no need to set a reference to the VBScript.RegExp library.):

Public Function IsEmailValid(strEmail As String) As Boolean
    Dim oRegularExpression As Object
    '   Sets the regular expression object
    Set oRegularExpression = CreateObject("VBScript.RegExp")
    With oRegularExpression
        .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
        '   Ignores case
        .IgnoreCase = True
        'Test expression string
        IsEmailValid = .test(strEmail)
    End With
End Function

Open in new window


As you can see, it is a lot shorter, in most cases a one line pattern will suffice. You may have some difficulty getting into the hang of creating Regular Expression patterns, but once you get it, it will make your validations so much easier. There are many online resources, and libraries containing ready to-go expressions. Make good use of them. After all, it is said: "Regular Expressions are either a Developer’s dream or his worst nightmare". Let's hope they will become your dream…
 
Now that we understand how to use Regular Expressions in VBA, let's take it one more step. What happens if you need to make more than one kind of validation in your application. You have to write a separate function for each expression you want to validate. That is a waste of your time and resources. Here I will present a function that can be used for making many regular expression validations.

Public Function fRegexValidation(ByVal strExprType As String, ByVal strExpr As String) As Boolean

    Dim oRegularExpression As Object

    '   Sets the regular expression object
    Set oRegularExpression = CreateObject("VBScript.RegExp")

    With oRegularExpression
        Select Case LCase(strExprType)
                'Sets the regular expression pattern
                'based on the ExprType passed in the parameter
            Case "email"    ' Validates an email address. See note at end of article.
                .Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
            Case "validthru"    ' Validates the valid thru date on a credit card (MM/YY)
                .Pattern = "((0[1-9])|(1[012]))/\d{2}"
        End Select
        
        '   Ignores case
        .IgnoreCase = True

        If .Pattern <> "" Then
            'Test expression string
            fRegexValidation = .test(strExpr)
        Else
            ' return False for invalid strExprType
            fRegexValidation = False
        End If
    End With

End Function

Open in new window


The crux of this function is the two parameters you pass to it; strExprType and strExpr. strExprType is the type of expression to be validated, for each type you must add a "case type" and a Pattern to evaluate it. strExpr is the actual expression to be validated.

To test it:
1. Open the immediate window (Ctrl + G)
2. Type fRegexValidation("Email", "MacroShadow@experts-exchange.com")

To use it:
1. Add your cases and patterns within the Select Case strExprType.
2.  In your code add:
If fRegexValidation("YourType", "Your Expression") = False Then yada yada End If

Open in new window

.

On a side note: Both the VBA function and the RegEx pattern are somewhat simplified approaches of validating an email, to validate an email address to fully comply with the international standards, much longer and complex functions/RegEx patterns are necessary.
For the valid email syntax read this.
For a complete email validation pattern see this.
A simple google search will show many patterns for validating emails, none of them (or at least the ones I saw) are complete. Try any pattern first to make sure it works for your needs.

NOTE: None of the above code has error handling. Never release code to your users without error handling.


Happy Coding:)

Thanks to matthewspatrick for helping improve this article.
4
Author:MacroShadow
1 Comment
LVL 35

Expert Comment

by:[ fanpages ]
Just for information regarding the e-mail pattern in the code listing above ("^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"), any domain name longer than three characters will be considered invalid.

There is a discussion of how effective the use of different patterns may be in certain circumstances at the following site:

[ http://www.regular-expressions.info/email.html ]

An updated pattern, for instance, is shown at the bottom of the page:

"[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?:[A-Z]{2}|com|org|net|edu|gov|mil|biz|info|mobi|name|aero|asia|jobs|museum)\b"
0

Featured Post

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Learn how to collaborate with office 365 Office Online

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month