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've made changes to the core of the function.
EDIT:
This part is causing the problems
If oRegEx.Test(sEmailAddress1) Or oRegEx.Test(sEmailAddress2) Then
Undo those changes and call the function as intended:

    If IsValidEmail("xaxa@gmail.com") Or IsValidEmail("someone@web.com") Then

 

[RoryA]

You haven't supplied a pattern for your regex.

 

[Ark68]

Hi GWteb ... this function has two different patterns of email addresses for which the user's entry can be checked with. That was what I was checking for, if the one email address submitted matches either one of the two patterns, not to check two different addresses.

But, yes ... that line is wrong, there is only 1 email address (sEmailAddress). It is this single email address that needs to be checked against the two patterns for validity. (sEmailPattern1, sEmailPattern2), so that is what I need to fix.

Thanks!

 

[Ark68]

You haven't supplied a pattern for your regex.

That is correct, but how do I adapt this to check for both patterns?

 

[GWteB]

Simply comment out one of the patterns

'    sEmailPattern1 = "^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$" 'or
    sEmailPattern2 = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"

 

[Ark68]

This is returning the same ...

Function IsValidEmail(sEmailAddress As String) As Boolean
    'Code from Officetricks
    '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 = sEmailPattern1
    bReturn = False
 
    'Check if Email match regex pattern
    'first pattern
    If oRegEx.Test(sEmailAddress) Then
        'Debug.Print "Valid Email ('" & sEmailAddress & "')"
        bReturn = True
    Else
        'Debug.Print "Invalid Email('" & sEmailAddress & "')"
        bReturn = False
    End If
 
    'second pattern
    oRegEx.Pattern = sEmailPattern2
    If oRegEx.Test(sEmailAddress) 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

 

[Ark68]

Actually .... changed it to:

Function IsValidEmail(sEmailAddress As String) As Boolean
    'Code from Officetricks
    '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 = sEmailPattern1
    bReturn = False
   
    'Check if Email match regex pattern
    If oRegEx.Test(sEmailAddress) Then
        'Debug.Print "Valid Email ('" & sEmailAddress & "')"
        bReturn = True
        IsValidEmail = bReturn
    Else
        'Debug.Print "Invalid Email('" & sEmailAddress & "')"
        bReturn = False
        IsValidEmail = bReturn
    End If
   
    If bReturn = False Then
        'second pattern
        oRegEx.Pattern = sEmailPattern2
        If oRegEx.Test(sEmailAddress) Then
            'Debug.Print "Valid Email ('" & sEmailAddress & "')"
            bReturn = True
            IsValidEmail = bReturn
        Else
            'Debug.Print "Invalid Email('" & sEmailAddress & "')"
            bReturn = False
            IsValidEmail = bReturn
        End If
    End If

    'Return validation result
    IsValidEmail = bReturn
End Function

 

[joeu2004]

I made some adjustments in order to check the two different available patterns

First, put Option Explicit at the top of the VBA module. You will discover that you fail to declare (with Dim or ReDim) some variables.

That will highlight some mistakes, namely references to uninitialized variables sEmailAddress1 and sEmailAddress2.

I suspect you meant to type sEmailPattern1 and sEmailPattern2.

But based on the original design, those should not be the parameters for oRegEx.Test.

Instead, you should use oRegEx.Test(sEmailAddress), referring to the function parameter.

Second, you create two patterns, but you set oRegEx.Pattern to only one (sEmailPattern). And in fact, you do not set sEmailPattern at all(!).

Finally, the statement If oRegEx.Test(...) Or oRegEx.Test(...) is inefficient. In VBA, both regular expressions are evaluated, even if the first is true, unlike the left-to-right evaluation in Excel.

In sum, your implementation should be the following.

(Note: I am not sure what the difference between the two patterns is. I am not familiar with the syntax of VBScript regular expressions. In fact, it might be wrong to try both patterns. The difference might depend on the version of VBScript that you use. A VBScript expert will need to comment.)

Option Explicit

Function IsValidEmail(sEmailAddress As String) As Boolean
    'Code from Officetricks (https://officetricks.com/validate-email-address-vba-code-excel/)
    'Define variables
    'Stop
    Dim sEmailPattern1 As String
    Dim sEmailPattern2 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 = sEmailPattern1
      
    'Check if Email match regex pattern1
    If oRegEx.Test(sEmailAddress) Then
        'Debug.Print "Valid Email pattern1 ('" & sEmailAddress & "')"
        bReturn = True
    Else
        'Debug.Print "Invalid Email pattern1 ('" & sEmailAddress & "')"
        bReturn = False
    End If
   
    'Check if Email match regex pattern2
    If Not bReturn Then
        oRegEx.Pattern = sEmailPattern2
        If oRegEx.Test(sEmailAddress) Then
            'Debug.Print "Valid Email pattern2 ('" & sEmailAddress & "')"
            bReturn = True
        'Else
            'Debug.Print "Invalid Email pattern2 ('" & sEmailAddress & "')"
        End If
    End If

    'Return validation result
    IsValidEmail = bReturn
End Function

Uncomment the Debug.Print and Else statements for debugging purposes. Better, use f9 to set breakpoints and/or f8 to step through the execution.

 

[Ark68]

My changes in post #8 is assessing the email address (sEmailAddress) effectively and returning the right value for bReturn. (bReturn is declared publically as Boolean)
Something is happening with the value of bReturn after leaving the function.
When stepping through the code, the code is triggered true on the first pattern. At the end of the code bReturn = TRUE.
But the result of Msgbox bReturn in my routine that called the function returns FALSE.

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