+91-8826547882

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

 

This is a simple VBA example to create the user define function in excel VBA. I have used Regular Expressions in it and this is the best way to validate email address because it matches 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.

Copy the below coded function and paste it under 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.

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)

June 30, 2013

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

Leave a Message

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com