Create your own User Define Function to Validate Email Address in Excel VBA

Share Now


Here, We will talk about Advanced Excel Training. Get more information below about Advanced Excel Training in Gurgaon.

This is a simple VBA example to create the user define a function in Excel VBA. I have used Regular Expressions in it and this is the best way to validate email address because it matches the pattern.

For using Regular Expressions you need to add “Microsoft VBScript Regular Expressions 5.5” under Tools in VBA code editor.

Open VBA code editor (press Alt+F11 in excel environment), Click Tool Tab then References.

Advanced Excell Training in Gurgaon

Copy the below-coded function and paste it into modules.

Option Explicit
Public Function ValidateEmailAddress(ByVal strEmailAddress As String) As Boolean

On Error GoTo Err1

Dim objRegExp As New RegExp
Dim blnIsValidEmail As Boolean

objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = “^([a-zA-Z0-9_-.]+)@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,3})$”

blnIsValidEmail = objRegExp.Test(strEmailAddress)
ValidateEmailAddress = blnIsValidEmail

Exit Function

Err1:
ValidateEmailAddress = False
MsgBox Err.number & vbcrlf & Err.description

End Function

Now you can use the function in your excel worksheet as you use other functions.

objRegExp.IgnoreCase = True means email address is not case sensitive.

 

Reference:

In the above code email address pattern we have taken from here (http://www.tipsntracks.com/117/validate-an-email-address-using-regular-expressions.html)



Share Now

0 responses on "Create your own User Define Function to Validate Email Address in Excel VBA"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Tableau Training in Delhi

    All Rights Reserved. EC Analytics Consulting 2014 - 2019.

    NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy