validating text as numeric 1

[rds747]

Gentlemen,

Is IsNumeric the best function to validate whether the user has entered a numeric text in a textbox?

Thanks!

 

[Golom]

It's pretty good but there are some unexpected results

? isnumeric("002.5E-7")
True
? isnumeric("-002.5D+7")
True

for example. This happens because scientific notation has the form "n.nnnE±m" or "n.nnnD±m"

Also it does not evaluate expressions

? isnumeric("3+2")
False

 

[gmmastros]

I've never been a fan of isnumeric because it returns true for scientific notation (which I don't like).

Example:

? IsNumeric("1e4") ' Returns True

Instead, I prefer to use the Like comparison.

Public Function BetterIsNumeric(ByVal Value As String) As Boolean
    
    If Value Like "*[!0-9.]*" Then
        BetterIsNumeric = False
    Else
        BetterIsNumeric = True
    End If
    
End Function

notice the pattern that we are searching on: "*[!0-9.]*"

Because of the period, we allow for fractional numbers. If you want integers only, then the search pattern should be... "*[!0-9]*"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[johnwm]

Although it doesn't catch Cut& Paste, I find it worthwhile to ALSO catch keypresses in the textbox, only allowing 0-9, decimal point and backspace

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[rds747]

validation would be done on text like "A2345B6". User would not be entering scientific notation.

But I'll definitely keep that in mind. Thanks.

 

[vladk]

How about this?

Public Function BetterIsNumeric(ByVal pstrValue As String) As Boolean
Dim blnReturn As Boolean

blnReturn = Not pstrValue Like "*[!0-9.]*"

If blnReturn Then
blnReturn = IsNumeric(pstrValue)
End If

BetterIsNumeric = blnReturn

End Function

 

[strongm]

>It's pretty good but there are some unexpected results

For further discussion of exponential numerics (including the revelation of the fact that + or - are not required, so 123D4 is a legitimate numeric) see thread222-1071392

 

[ItIsHardToProgram]

I might be wrong but the function provided does not take decimals in charge, and you would need to add the ascii caracter to the IF statement?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[Golom]

vladk

I think your function needs

[blue]blnReturn = Not pstrValue Like "*[!-+0-9.]*"[/blue]

because things like "+1.5" or "-4.3" should return TRUE.

 

[frothytoad]

How about something like this:

Public Function IsNum(ByVal InVal As String) As Boolean

IsNum = (StrComp(Trim(InVal), Val(InVal), vbTextCompare) = 0)

End Function

 

[Golom]

Don't think so
[tt]
? IsNum("+1.5") ---> False
? IsNum("1.50000") ---> False
? IsNum(".5") ---> False
[/tt]

 

[gmmastros]

The 'problem' with IsNumeric is that you can have a D, d, E, or e in the string and still return true. So, perhaps the best isnumeric function is one that uses the built-in version and adds another check (using the like keyword).

Something like this...

Public Function IsNumeric(ByVal Value As String) As Boolean
    
    IsNumeric = False
    
    If VBA.IsNumeric(Value) Then
        If Not (Value Like "*[deDE]*") Then
            IsNumeric = True
        End If
    End If
    
End Function

If the string passes the built-in IsNumeric check, we then check to see if there is a d, e, D, or E within the string. Is not, then the string is numeric.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[taupirho]

Does this work?

Function isnum(a As String)
On Error GoTo notanum:
Dim n As Double

n = a
isnum = 0

Exit Function
notanum:
isnum = -1

End Function

 

[Golom]

taupirho

Has the same old problem
[red][tt]
isnum("1E5") ---> True
[/tt][/red]

 

[strongm]

Mind you .. 1E5 IS numeric ...

One of the problems here is not the routine itself, but agreeing what should be considered numeric in the first place

 

[Golom]

And yet another wrinkle

? IsNumeric("$1000$")
  True

but

? Val("$1000$")
  0

What's that about?

 

[strongm]

>What's that about

Here's a clue:

? IsNumeric("$1000$")
True

depends on your regional settings ...













IsNumeric basically recognises and accepts the currently selected currency symbol in trailing or leading position (or, indeed, both) as a legitimate part of a number

Val on the otherhand does not, and merely reads up to the first non-legit character (in this case $)

 

[gmmastros]

Hmmmm... I noticed this faq's recently faq222-5901

There's a pretty nifty solution to the exponential notation problem and also solves the $ issue.

Since we start off with a string, simple add "e0" to the end of the string. 1 E (or d) is valid scientific notation. 2 e's (or 1 d and 1 e) is NOT valid, so... this seems to work pretty well.

Public Function IsNumeric(ByVal Value As String) As Boolean
    
    IsNumeric = VBA.IsNumeric(Value [!]& "e0"[/!])
    
End Function

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom