Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Regex Validation Using VBA

Joe Howard
CERTIFIED EXPERT
A man of stark contrasts, love of nature, curiosity of technology and passion of helping my fellow man.
Published:
Updated:
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
7,883 Views
Joe Howard
CERTIFIED EXPERT
A man of stark contrasts, love of nature, curiosity of technology and passion of helping my fellow man.

Comments (1)

CERTIFIED EXPERT

Commented:
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"

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.