Validate Email Addresses with Excel VBA

Objective:

We want to create a VBA macro that will validate email addresses based on common rules such as:

• Presence of « @ » symbol.
• Proper domain name.
• Proper structure (local part, @ symbol, domain part).

This script will allow us to check whether an email address in a given cell is valid.

Steps:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. In the editor, go to Insert → Module to create a new module.
3. Paste the following VBA code into the module.
4. You can then call this function from an Excel worksheet to validate email addresses.

VBA Code for Email Validation:

Function ValidateEmailAddress(ByVal email As String) As String
    ' Declare variables
    Dim regex As Object
    Dim isValid As Boolean
    Dim resultMessage As String
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")   
    ' Regular expression pattern for validating email
    ' This pattern ensures:
    ' - At least one character before the @ symbol
    ' - A single @ symbol
    ' - At least one character after the @ symbol (domain name)
    ' - A period (.) in the domain name (to separate the domain from the top-level domain)
    ' - At least two characters in the top-level domain (e.g., .com, .org)
    regex.IgnoreCase = True
    regex.Global = True
    regex.Pattern = "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"   
    ' Test the email address against the regular expression
    If regex.Test(email) Then
        ' If valid, return a success message
        resultMessage = "Valid Email Address"
    Else
        ' If not valid, return an error message
        resultMessage = "Invalid Email Address"
    End If   
    ' Return the result message
    ValidateEmailAddress = resultMessage
End Function

Explanation of the Code:

1. Function Declaration:
   • The function ValidateEmailAddress takes a single argument email (the email address to validate) and returns a string that indicates whether the email is valid or not.
   • The return value will be either « Valid Email Address » or « Invalid Email Address ».
2. Creating a Regular Expression Object:
   • We use the VBScript.RegExp object to apply a regular expression (regex). Regular expressions are patterns that allow you to match text strings in a flexible way. In this case, it is used to validate the structure of the email address.
   • regex.IgnoreCase = True ensures that the email address is case-insensitive.
   • regex.Global = True allows the regex to search the entire string.
3. Regular Expression Pattern: The pattern used to validate the email address is:
4. « ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$ »
   • ^[a-zA-Z0-9._%+-]+:
     • ^ asserts the start of the string.
     • [a-zA-Z0-9._%+-] matches any alphanumeric character, dot (.), underscore (_), percent (%), plus (+), and minus (-) characters.
     • + means « one or more » of the preceding characters.
   • @: This is the @ symbol that must appear in the email address.
   • [a-zA-Z0-9.-]+:
     • This matches the domain part of the email (after @), which can contain alphanumeric characters, periods (.), and hyphens (-).
     • + again means « one or more » of these characters.
   • \.: This matches a literal period (.) between the domain name and the top-level domain (e.g., .com).
   • [a-zA-Z]{2,}$:
     • This matches the top-level domain, which must be at least two characters long, and it can only contain alphabetic characters (e.g., .com, .org).
     • $ asserts the end of the string.
5. Testing the Email:
   • The regex.Test(email) method checks if the input email string matches the regular expression pattern.
   • If the email matches, it returns « Valid Email Address ». Otherwise, it returns « Invalid Email Address ».
6. Returning the Result:
   • The function returns the appropriate message indicating whether the email address is valid or not.

How to Use the Function in Excel:

1. After adding the VBA code, close the editor by pressing Alt + Q.
2. In any cell in your Excel sheet, you can now use the function ValidateEmailAddress just like any regular Excel function.

For example, if you have an email address in cell A1, you can use the following formula to validate it:

=ValidateEmailAddress(A1)

This will display either « Valid Email Address » or « Invalid Email Address » based on whether the email format matches the regular expression.

Potential Improvements:

• Advanced Validation: This script checks the basic structure of an email address. If you want more advanced validation (e.g., checking if the domain actually exists), you’ll need to use additional methods such as DNS lookup, which is beyond the capabilities of regular expressions.
• Empty Email: If you want to handle empty cells or null values, you can modify the code to return a message like « Please enter an email address » if the input is empty.

Conclusion:

This VBA code for email validation checks the basic structure of an email address using regular expressions, ensuring it follows a format with a local part, @ symbol, domain name, and top-level domain. It provides an easy and effective way to perform quick email validation within Excel.