How to match a simple number pattern in VBA using RegEx

Question

How do I check if a string is either a one digit number OR a two digit number and otherwise return false?

Answer 1

How about:

Function OneOrTwo(i As Integer) As Boolean
Dim objRegEx As Object
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.IgnoreCase = True
objRegEx.Global = True

objRegEx.Pattern = "^\d{1,2}$"

OneOrTwo = objRegEx.Test(i)
End Function

See: http://msdn.microsoft.com/en-us/library/ms974570.aspx

Answer 2

You can also do this using VBA LIKE:

Function OneOrTwo(Digits As Variant) As Boolean
    OneOrTwo = Digits Like "#" Or Digits Like "##"
End Function

Answer 3

IF CInt(myNumberString) < 100 Then
    MsgBox "String must be either 1 or 2 digits"
Else
    Msgbox "Failed"
End IF

Should work for you.

Answer 4

Remou had it right. Here is a RegexContains function so you can use it with all sorts of patterns, not just the one you need now.

Function RegexContains(ByVal find_in As String, _
                       ByVal find_what As String, _
                       Optional IgnoreCase As Boolean = False) As Boolean

Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = find_what
RE.IgnoreCase = IgnoreCase
RE.Global = True
RegexContains = RE.Test(find_in)

End Function

Following the example from Remou, assuming the cell is A1, you'd write:

=RegexContains(A1, "^\d{1,2}$")

Answer 5

Here is what I would try. /d for digit, ? for option 2 digits.

/d/d?

or

/d{1,2}