1

So what I am looking to find out, because I'm new to regular expressions, is if it's possible to replace my "Invalid" return statement with a similar statement commented out on the next line, where the character and/or character # of the expression failed at.

Let's use "msteede48@hotmail@.com" as an example input for myEmail variable below...

Option Explicit

Function ValidEmail(myEmail As String) As String
    Dim regExp As Object

    Set regExp = CreateObject("VBScript.Regexp")

    If Len(myEmail) < 6 Then
        ValidEmail = ""
    Else
        With regExp
            .Global = True
            .IgnoreCase = True
            .Pattern = "^(?=[a-z0-9@.!#$%&'*+/=?^_`{|}~-]{6,254}$)(?=[a-z0-9.!#$%&'*+/=?^_`{|}~-]{1,64}@)[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:(?=[a-z0-9-]{1,63}\.)[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+(?=[a-z0-9-]{1,63}$)[a-z0-9](?:[a-z0-9-]*[a-z0-9])?$"
        End With

        If regExp.Test(myEmail) = True Then
            ValidEmail = "Valid"
        Else
            ValidEmail = "Invalid" 
            'ValidEmail = "Error at character:" & CodeToDetermineWhatChar#TheRegexpFailedAtGoesHere & " with a(n):" & CodeToDetermineWhatCharTheRegexpFailedAtGoesHere & "."
        End If
    End If
    Set regExp = Nothing
End Function

Where the output would be something like: Error at character:18 with a(n):@.

DragonPhantom
  • 11
  • 3
  • I don't know a straightforward way. [This answer](http://stackoverflow.com/a/33772848/2877364) suggests using capturing groups and testing whether they are populated, but that doesn't seem to work in VBA with the VBScript regexes. You could always implement your own regex engine... ;) – cxw Mar 31 '17 at 19:00
  • 1
    Validating email addresses with a regex [is EXTREMELY complex](http://stackoverflow.com/q/201323/1188513), and then being required to debug the pattern is enough to drive anyone to quit on the spot. Better just verify that the address contains a `@` and move on. See [this comment](http://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address#comment52166781_201323). – Mathieu Guindon Mar 31 '17 at 19:00
  • Also relevant: [regex use vs regex abuse](https://blog.codinghorror.com/regex-use-vs-regex-abuse/) – Mathieu Guindon Mar 31 '17 at 19:04
  • Okay, These are valid points. I guess I will just if statement some of the most common mistakes I find and move on. Thanks to you both – DragonPhantom Mar 31 '17 at 19:34
  • So you basically want to identify if the string is valid or invalid email address ? – 0m3r Apr 02 '17 at 01:03
  • No, my code already does that. My goal was to have some code that would capture where in the email address it failed to pass the expression, so my end user would be able to see where the likely mistake was. – DragonPhantom Apr 03 '17 at 15:46

0 Answers0