Excel VBA RegEx formula

[wheelsguy]

Good morning.

I am new to Regular Expressions, and I am trying to validate a number like 012345-1234. I have the following:

Public Function ValidNumber(ByVal strNumber As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Global = True
        .Pattern = "^([0-9][0-9][0-9][0-9][0-9][0-9])-([0-9][0-9][0-9][0-9])$"
        ValidNumber = .Test(strNumber)
    End With
End Function

My code works, but I was hoping there was a better (shorter experession) way where it could be more recursive.

Thank you for looking, WHEELS

 

[Rick Rothstein]

Thanks Rick. I haven't had a chance to test it yet, but it looks pretty clean and powerful. It resembles SQL (slightly). What would this techinque be called, and how would it be utilized to replace Regular Expressions for more complex validations?

It is plain VBA coding... Like is an operator (the same as =, >, etc) which compares the text string on the left with a patterned string on the right and returns a simple True or False result. It is not a replacement for RegExp, but it can do a lot of helpful things which help to avoid having to call up the heavy machinery of a RegExp engine. Here is a link to the help files for the Like operator (it is marked for Office 2010, but Like has not changed since its VBA version 6 was introduced into Excel, so you can use it for whatever version of Excel you are running).