Alternative to IsNumeric

[winshent]

Is there an alternative function to IsNumeric as its not reliable. Code below demonstrates this:

    For i = 65 To 90
        Debug.Print Chr(i) & " " & IsNumeric("1" & Chr(i) & "3")
    Next

 

[namliam]

E definately can be a number, it is used in a scientific number notation for either extreemly large or small values. Not sure how D may come into a number?

Something along these lines may help you?

Function myNumeric(anyNumber As String) As Boolean
    Dim i As Double
    Dim x As String
    x = Trim(anyNumber)
    myNumeric = True
    For i = 1 To Len(x)
        If InStr(1, "0123456789.", Mid(x, i, 1)) = 0 Then myNumeric = False:             Exit Function
    Next i
End Function

 

[pr2-eugin]

I think you might need a function to get this ! The following should work.

Public Function IsActualNumeric(Expression) As Boolean
[COLOR=Green]'**********************
'Code Courtesy of
'  Paul Eugin
'**********************[/COLOR]
    Dim retBool As Boolean, iCtr As Long
    
    If Not IsNull(Expression) Then    
        For iCtr = 1 To Len(Expression)
            Select Case Asc(Mid(Expression, iCtr, 1))
                Case 48 To 57, 46
                    retBool = True
                Case Else
                    retBool = False
                    Exit For
            End Select
        Next
    End If
    IsActualNumeric = retBool
End Function

Tested against

? IsActualNumeric(Null)
False
? IsActualNumeric(12.36)
True
? IsActualNumeric(112)
True
? IsActualNumeric("H")
False
? IsActualNumeric("1D3")
False

EDIT: When did you sneak up namliam ? Well have to say your code is quiet simpler. Will check it.

 

[namliam]

For the E, used in scientific notation:
http://en.wikipedia.org/wiki/Scientific_notation
http://www.nyu.edu/pages/mathmol/textbook/scinot.html

 

[winshent]

Thanks guys

Although I don't need it at the moment, I may have a look at adapting this to dealing with leading and trailing '+-' and '£$€' symbols.

 

[namliam]

Would be simple to add allowed characters in "my" function... simply add them here:
If InStr(1, "0123456789.+-£$€", Mid(x, i, 1))

Kind regards from Amsterdam

 

[gemma-the-husky]

the truth is, isnumeric IS reliable, but your data may not be.

why do you have numbers with embedded letters. What are you trying to do exactly. Maybe val() would give you what you want.

 

[JANR]

Not sure how D may come into a number?

differential operator - probably

http://en.wikipedia.org/wiki/Notation_for_differentiation

JR

 

[winshent]

the truth is, isnumeric IS reliable, but your data may not be.

why do you have numbers with embedded letters. What are you trying to do exactly. Maybe val() would give you what you want.

Just want an all emcompassing function that will handle data entry, so anything a user inputs. I'm actually using this in excel and I want to improve the validation.

 

[namliam]

1,5E6 is a valid number, like it or not, just another way of writing 1.500.000

 

[nanscombe]

Depending on what you are inputting, another couple of possibilities:

% percent (20%)
^ to the power of (2^3 = 8)

Would it be freeform entry or could you apply an input mask to the field?