Function Returning Wrong Result

[Ark68]

I found this webpage providing me with a Function to check email address validity. (Validate Email Address - VBA Code - Excel)

I made some adjustments in order to check the two different available patterns, but even when I put in a valid email address (xaxaxaxaxa@gmail.com), the result is always false. I'm wondering if someone could advise what part of my code is contributing to the wrong answer.

Function IsValidEmail(sEmailAddress As String) As Boolean
    'Code from Officetricks (https://officetricks.com/validate-email-address-vba-code-excel/)
    'Define variables
    'Stop
    Dim sEmailPattern As String
    Dim oRegEx As Object
    Dim bReturn As Boolean
   
    'Use the below regular expressions
    sEmailPattern1 = "^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$" 'or
    sEmailPattern2 = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
   
    'Create Regular Expression Object
    Set oRegEx = CreateObject("VBScript.RegExp")
    oRegEx.Global = True
    oRegEx.IgnoreCase = True
    oRegEx.Pattern = sEmailPattern
    bReturn = False
   
    'Check if Email match regex pattern
    If oRegEx.Test(sEmailAddress1) Or oRegEx.Test(sEmailAddress2) Then
        'Debug.Print "Valid Email ('" & sEmailAddress & "')"
        bReturn = True
    Else
        'Debug.Print "Invalid Email('" & sEmailAddress & "')"
        bReturn = False
    End If

    'Return validation result
    IsValidEmail = bReturn
End Function

 

[GWteB]

You are making a mistake within thinking, because bReturn only has scope within the procedure of the function you're calling.
This should give you the right result:

If Target.Address = "$K$12" Then 'email
    sEmailAddress = Target.Value
    MsgBox IsValidEmail(sEmailAddress)
End If

 

[Ark68]

Thanks GWteB, that was what I needed!
And thank you all for your contributions. There was some good education in your responses that I took away.

 

[GWteB]

You are welcome and thanks for the feedback.

 

[RoryA]

I’d suggest you amend the function to take the pattern as an argument, then call it twice (if necessary) passing each pattern in turn from a wrapper function.

 

[Ark68]

Thanks Rory, that is a good alternative. Is their any particular benefit to doing it in this manner? Always taking opportunity to learn how to avoid pitfalls, cleaner alternatives, and/or improve the efficiency of my code (for instance I still rely far too much on "if/thens", "For/Next" etc)

 

[RoryA]

It will make the function much more reusable since you can pass any pattern and any value to test, not just email addresses

 

[Ark68]

Ahhh ... multipurpose! Great idea. Are there specific/limited patterns for things defined by Microsoft, or is it up to one's imagination? ie, is there a pattern recognized by MS for telephone numbers, or would the user have to create that pattern? I'm not sure if the patterns in my email code are from an internal library, or drawn up by the coder.

 

[RoryA]

Those are regular expressions. You can pretty much create a pattern for anything you can think of once you know the syntax.