RegEx Errors

[psycoperl]

When running a sub that calls the ValidateState function (see below) I get a VBA Error:
Run-time error '5017': Method 'Test' of object 'IRegExp2' failed

When I click on "debug" it highlights the line:

        ValidateState = .Test(sState)

However, I am not certain what is wrong.

Especially as the Function ValidateEmail which I used as the starting point for this function works... all I just did was change the regex string.

Thank you for your assistance.

Function ValidateState(ByVal sState As String) As Boolean
  'Function Source: http://www.msofficegurus.com/post/How-to-VBA-email-validation.aspx
  
    Dim oRegularExpression As RegExp
  
'   Sets the regular expression object
    Set oRegularExpression = New RegExp

    With oRegularExpression
'   Sets the regular expression pattern

'Original Pattern        .Pattern = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"

'New Pattern from http://regexlib.com/RETester.aspx?regexp_id=471
    'The RE match U.S. state abbreviation used by the U.S. Post Office.
        .Pattern = "^(?-i:A[LKSZRAEP]|C[AOT]|D[EC]|F[LM]|G[AU]|HI|I[ADLN]|K[SY]|LA|M[ADEHINOPST]|N[CDEHJMVY]|O[HKR]|P[ARW]|RI|S[CD]|T[NX]|UT|V[AIT]|W[AIVY])$"
  
'   Ignores case
        .IgnoreCase = True

'       Test State string
        ValidateState = .Test(sState)
    End With
    
End Function

Function ValidateEmail(ByVal sEmail As String) As Boolean
  'Function Source: http://www.msofficegurus.com/post/How-to-VBA-email-validation.aspx
  
    Dim oRegularExpression As RegExp
  
'   Sets the regular expression object
    Set oRegularExpression = New RegExp

    With oRegularExpression
'   Sets the regular expression pattern

'Original Pattern        .Pattern = "\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"

'New Pattern from http://regexlib.com/RETester.aspx?regexp_id=269
        .Pattern = "^[\w-]+(\.[\w-]+)*@([a-z0-9-]+(\.[a-z0-9-]+)*?\.[a-z]{2,6}|(\d{1,3}\.){3}\d{1,3})(:\d{4})?$"
  
'   Ignores case
        .IgnoreCase = True

'       Test email string
        ValidateEmail = .Test(sEmail)
    End With
    
End Function

 

[Scott Huish]

What are you testing for exactly? What is this part (?-i: in the regular expression for?

 

[xenou]

I think it is likely you are using a regex pattern for a different "flavor" of regex. VBScript uses what is basically a Javascript/JScript flavor - you might be using a pattern for .NET regex. Where did you get the pattern?


Side note: I've not seen the "IRegExp2" in an error message. What reference did you set?

 

[psycoperl]

What are you testing for exactly? What is this part (?-i: in the regular expression for?

HotPepper,
Thank you for your response.

The expression is for "The RE match U.S. state abbreviation used by the U.S. Post Office."

I am not sure regarding the RegExp I got the string from: Regular Expression Library

 

[psycoperl]

I think it is likely you are using a regex pattern for a different "flavor" of regex. VBScript uses what is basically a Javascript/JScript flavor - you might be using a pattern for .NET regex. Where did you get the pattern?


Side note: I've not seen the "IRegExp2" in an error message. What reference did you set?

HI Xenou, thank you for your assistance. I got the pattern from regexlib.com as noted in the comments in the code.

For the Reference I have set: Microsoft VBScript Regular Expressions 5.5

 

[xenou]

I can't get the pattern to work on any regex testers - I think it's a bad one!
ξ

 

[psycoperl]

@Xenou
Thank you... I created a new table and change the function that queries the table to take care of the validation... I was just trying to avoid another ADODB call.

Thanks again for your assistance!