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

 

[jamcall]

Could be:

"^([0-9]{6})-([0-9]{4})$"

Cheers,
~ Jim

 

[wheelsguy]

Could be:

"^([0-9]{6})-([0-9]{4})$"

Cheers,
~ Jim

Perfect. This is exactly what I was looking for. Thank you Jim.

 

[jamcall]

Looks like Excel also accepts '\d' as a substitute for '[0-9]'.

 

[wheelsguy]

Looks like Excel also accepts '\d' as a substitute for '[0-9]'.

Fantastic. I can be even more cryptic. I do recall seeing something about this. I have a great JavaScript RegEx tester here: Javascript Regular Expression Validator | Regular Expressions Library and Testing Tool. Thank you.

 

[pgc01]

Hi

I'm glad your problem is solved.

Since the purpose of the udf is just to test that pattern, I'd just add some remarks:

1 -

        .IgnoreCase = True

In this case your pattern does not have letters so you can just ignore this property

2 -

        .Global = True

In this case this property doesn't matter, so ignore it

3 -

"^([0-9]{6})-([0-9]{4})$"

You don't need the parentheses, and, like Jim said, you can use "\d" for a digit.

Conclusion:

You can try a simplified version of the code:

Public Function ValidNumber(ByVal strNumber As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .Pattern = "^\d{6}-\d{4}$"
        ValidNumber = .Test(strNumber)
    End With
End Function

Last remark: the code could still be made more efficient if you use this udf a lot in the worksheet, by making the object persistent.

 

[wheelsguy]

Hi

I'm glad your problem is solved.

Since the purpose of the udf is just to test that pattern, I'd just add some remarks:

1 -

        .IgnoreCase = True

In this case your pattern does not have letters so you can just ignore this property

2 -

        .Global = True

In this case this property doesn't matter, so ignore it

3 -

"^([0-9]{6})-([0-9]{4})$"

You don't need the parentheses, and, like Jim said, you can use "\d" for a digit.

Conclusion:

You can try a simplified version of the code:

Public Function ValidNumber(ByVal strNumber As String) As Boolean
    With CreateObject("VBScript.RegExp")
        .Pattern = "^\d{6}-\d{4}$"
        ValidNumber = .Test(strNumber)
    End With
End Function

Last remark: the code could still be made more efficient if you use this udf a lot in the worksheet, by making the object persistent.

Great suggestions. Thank you pgc01</SPAN></SPAN>.

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[Rick Rothstein]

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.

Are you aware you can do this without resorting to using a Regular Expression engine...

Function ValidNumber(ByVal strNumber As String) As Boolean
  ValidNumber = strNumber Like "######-####"
End Function

And, because the function is nothing more than a simple one-liner, you really do not need the function housing at all... you can simply use the right side of the code line in an If..Then test directly within your own code..

 

[wheelsguy]

Are you aware you can do this without resorting to using a Regular Expression engine...

Function ValidNumber(ByVal strNumber As String) As Boolean
  ValidNumber = strNumber Like "######-####"
End Function

And, because the function is nothing more than a simple one-liner, you really do not need the function housing at all... you can simply use the right side of the code line in an If..Then test directly within your own code..

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?