IsNumeric("5,4") returns TRUE.

[Mackeral]

I don't know why Isnumeric("5,4") is considered a number, but to fix it I wrote the following:

Function Is_Numeric(Orig_To_Test) As Boolean
    ' Does "To_Test" a number?
    ' 1/21/12 Modified. WML
    ' 5/9/20 Add more test conditions. WML

    Prog = "Is_Numeric"
        
    To_Test = Orig_To_Test
    
    Knt = 0
    For I = 1 To Len(To_Test)
        Char = Mid(To_Test, I, 1)
        If Not (IsNumeric(Char) Or Char = ".") Then
            GoTo Error
        End If
        If Char = "." Then Knt = Knt + 1
    Next I

    Is_Numeric = True
    If Knt < 2 Then Exit Function
    
Error:
    IsNumeric = False
    Msg1 = "The Input String is NOT a number:"
    Msg2 = Quote(Orig_To_Test)
    Call Msg_Err(Prog, Msg1, Msg2, False)
    Stop
    
End Function ' Is_Number

You can mess around with how you handle the error message.

 

[mikerickson]

IsNumeric("3,4") is True for the same reason that IsNumeric("3,400") is True. It ignores thousands separators.

Does this function do what you want?

Function Is_Numberic(aString As String, Optional NonNumericMessage As String = "") As Boolean
    Is_Numberic = Not (aString Like "*[!0-9.]*")
   
    Is_Numberic = Not CBool(InStr(1, Replace(aString, ".", vbNullString, , 1), ".")) Imp Is_Numberic

    If (Not Is_Numberic) And (NonNumericMessage <> vbNullString) Then
        MsgBox NonNumericMessage
    End If
End Function

 

[footoo]

Perhaps ISNumber does what you want :

Is_Numeric = Application.IsNumber(Orig_To_Test)

 

[Rick Rothstein]

IsNumeric is actually a terrible function to use to proof whether a text entry is a number or not unless you use is on one-digit text strings only. See my mini-blog article here for the complete horror that is known as IsNumeric...

Thinking About Using VBA's IsNumeric Function? Read this first.

.