psycoperl
Active Member
- Joined
- Oct 23, 2007
- Messages
- 338
- Office Version
- 365
- 2016
- Platform
- Windows
- MacOS
- Web
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:
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.
Run-time error '5017': Method 'Test' of object 'IRegExp2' failed
When I click on "debug" it highlights the line:
Code:
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.
Code:
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
Code:
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