Excel Crazy

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

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)

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 *

top
 

© LOKESH PALIWAL 1987 - 2018

LOKESH@EXCELCRAZY[DOT]COM | 8826547882

Reporting Solutions | Data Consulting | Analytics Training

CONTACT US
close slider

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Message for us...

Skip to toolbar