Make sure that a string is exactly a 5 digit number

I want to return true if开发者_运维问答 some strings = 'XXXXX'

Where every X is a number 0 through 9

I know there must be a dozen ways to do this but I would like to know the best way.

yourString Like "#####"

If you want the simplest way, you can go with this:

Function MyFunction(myString As String) As Boolean
    MyFunction = ((Len(myString) = 5) And (IsNumeric(myString)))
End Function

If you want the more efficient way, you'd have to run some tests for the different methods people suggested.

Edit: The previous solution doesn't work well (see the first 2 comments) but I'm letting it there since it's what has been accepted. Here is what I would do :

Function MyFunction(myString As String) As Boolean
    Dim myDouble As Double
    Dim myLong As Long
    myDouble = Val(myString)
    myLong = Int(myDouble / 10000)
    MyFunction = ((Len(myString) = 5) And (myLong > 0) And (myLong < 10))
End Function

There is no error "protection" in that function, so if you try to check a too large number like 22222222222222, it will not work.

Similar question previously asked: link text

Basically want to check

(Len(s) = 5) And IsNumeric(s)

You can also use regular expressions to solve this problem. If you include Microsoft VBScript Regular Expressions 5.5 in your VBA project, you can use RegExp and MatchCollection variables as in the function below. (This is a modification of the response to this post at ozgrid.com.)

Public Function FiveDigitString(strData As String) As Boolean

On Error GoTo HandleError

Dim RE As New RegExp
Dim REMatches As MatchCollection

    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "^[0-9][0-9][0-9][0-9][0-9]$"
    End With

    Set REMatches = RE.Execute(strData)
    If REMatches.Count = 1 Then
        FiveDigitString = True
    Else
        FiveDigitString = False
    End If

    Exit Function
HandleError:
    Debug.Print "Error in FiveDigitString: " & Err.Description
    FiveDigitString = False
End Function