VBA: IsNumeric Function Returning True Even If Data Contains Letters. Any Other Way To Validate Entry?

[kelly mort]

I am using an inputbox to assign value(s) to my variable. After that I use the IsNumeric Function to check if the entry is a valid number.

During the testing process, I realized that I mistakenly entered 4e4 and instead of the function flagging that out as an invalid number, it went through as a number.

I have tried replacing the e with other letters and they were caught by the function yet the e always goes through when it comes between two numbers.

Is there a better way to validate my input?

Thanks in advance.

 

[Alex Blakenburg]

Are your entries always going to be whole numbers ie are you expecting decimal points or comma ?
@Rick Rothstein has posted a fairly comprehensive treatment in this thread on the forum: vba userform textbox numeric only

You may be able to get away with just this part of what was posted there:
(where strInput is the result of the Input Box)

    If Not (strInput Like "*[!0-9]*") Then
        MsgBox "Is Numeric " & strInput
    Else
        MsgBox "Is NOT Numeric, try again " & strInput
    End If

 

[bebo021999]

4e4 means 4 and four 0 (4000)
Try like this:

Sub test()
Dim IBox
IBox = InputBox("Input value:")
    If InStr(1, IBox, "e") = 0 And IsNumeric(IBox) Then
        MsgBox "Valid Number"
    Else
        MsgBox "Invalid Number"
    End If
End Sub

 

[Trebor76]

4E4 is scientific notation for 4 x 10^4 = 40,000

 

[kelly mort]

Are your entries always going to be whole numbers ie are you expecting decimal points or comma ?

I will be expecting decimals but no commas.

 

[Alex Blakenburg]

This version worked for me when I tried inserting decimal no.

If Not (strInput Like "*[!0-9,.]*") And IsNumeric(strInput) Then

Added IsNumeric to handle more than one decimal in the input

 

[kelly mort]

4e4 means 4 and four 0 (4000)
Try like this:

Sub test()
Dim IBox
IBox = InputBox("Input value:")
    If InStr(1, IBox, "e") = 0 And IsNumeric(IBox) Then
        MsgBox "Valid Number"
    Else
        MsgBox "Invalid Number"
    End If
End Sub

Is this code case sensitive?

 

[kelly mort]

This version worked for me when I tried inserting decimal no.

If Not (strInput Like "*[!0-9,.]*") And IsNumeric(strInput) Then

Added IsNumeric to handle more than one decimal in the input

If Not (strInput Like "*[!0-9.]*") And IsNumeric(strInput) Then

This version worked for me since the comma was treating entries like
2,3 as valid numbers.

 

[Alex Blakenburg]

Is this code case sensitive?

Yes it is. That part can be handled by changing it to this:-
InStr(1, IBox, "e",vbTextCompare)
My issue with that method was that 4d4, caused me the same issue as 4e4 and I didn't want to have to try out every letter.

 

[Alex Blakenburg]

If Not (strInput Like "*[!0-9.]*") And IsNumeric(strInput) Then

This version worked for me since the comma was treating entries like
2,3 as valid numbers.

Thank you for that.