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

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 (

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 *

    Social Media

    facebook page EC Analytics Consulting   Linkedin Page EC Analytics Consulting   Youtube EC Analytics Consulting


    0124- 4601426


    EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

    EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED