Notice: WC_Cart::get_cart_url is deprecated since version 2.5! Use wc_get_cart_url instead. in /home/swatipaliwal/public_html/excelcrazy.com/wp-includes/functions.php on line 3839
Excel Crazy

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 11, 2015

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 - 2017

Reporting Solutions | Data Consulting | Analytics Training