none
VBA IsNumeric Function

    Question

  • I have a textbox for user to enter a number. If users enter a invalid number an alert will popup. I used VBA function IsNumeric to check whether the entered number is valid or not. But the function returns TRUE if the text entered are "12d3" or "123e5" etc. For example, msgbox(IsNumeric("12d3")) returns TRUE.

    In Excel 12d3 and 123e5 are indeed valid mumeric. How to identify such entered text as invalid number?

    Thanks


    Qingping Cheng
    Thursday, April 07, 2011 5:46 AM

Answers

  • hi,

    yes, sorry I forgot to select this one
    http://cjoint.com/?2ekoWrAltlo
     Function ValeurTextbox(Textbox As MSForms.Textbox, Presse_Papier)
    If Presse_Papier = "" Then ValeurTextbox = Textbox.Text: Exit Function
    With Textbox
    If Left(Presse_Papier, 1) = "-" Or Left(.Text, 1) = "-" Then
        .Text = Replace(Left(.Text, .SelStart) & Presse_Papier & Right(.Text, Len(.Text) - .SelStart), "-", "")
    Else
        .Text = Left(.Text, .SelStart) & Presse_Papier & Right(.Text, Len(.Text) - .SelStart)
    End If
    End With
    ValeurTextbox = Textbox.Text
    End Function

    .

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-10 01:10, Qingping Cheng a écrit :

    Hi Isabelle,

    I do need to check the value of entry from clipboard. Could you please provide the detail for the function ValeurTextbox? It seems that it is not a standard VBA function.

    Thanks!




    • Marked as answer by qp Cheng Sunday, April 10, 2011 1:53 PM
    Sunday, April 10, 2011 12:48 PM

All replies

  • try this

    if Val(mynum) = mynum then

     


    jdweng
    Thursday, April 07, 2011 10:53 AM
  • Hi Joel,

    Thanks for your response. Val still cannot identify "12d3" as invalid number. I trid the following codes:

    sub Test()
    
    dim mynum
    
    mynum="12d3"
    
    if val(mynum)=mynum then
    
    msgbox("Yes")
    
    else msgbox("No")
    
    end if
    
    end sub

    msgbox returned "Yes". If mynum = "12s3" then it returns "No". This result is the same as using IsNumeric:

    sub Test2()
    
    dim mynum
    
    mynum="12d3"
    
    msgbox(isnumeric(mynum))
    
    sub

     

    It returns "true". If mynum="12s3" it returns "False".


    Qingping Cheng

    Thursday, April 07, 2011 11:43 AM
  • Maybe you could use:
    application.isnumber()

    if application.isnumber(me.textbox1.value) then
      ...

    Qingping, Cheng wrote:


    I have a textbox for user to enter a number. If users enter a invalid number an alert will popup. I used VBA function IsNumeric to check whether the entered number is valid or not. But the function returns TRUE if the text entered are "12d3" or "123e5" etc. For example, msgbox(IsNumeric("12d3")) returns TRUE.

    In Excel 12d3 and 123e5 are indeed valid mumeric. How to identify such entered text as invalid number?

    Thanks

    --
    Qingping Cheng

    --

    Dave Peterson

    Thursday, April 07, 2011 12:08 PM
  • hi,

    try with a worksheet function

    If Application.IsNumber(mynum) Then

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-07 07:43, Qingping Cheng a écrit :

    Hi Joel,
    Thanks for your response. Val still cannot identify "12d3" as invalid number. I trid the following codes:

    sub Test()
    
    dim mynum
    
    mynum="12d3"
    
    if val(mynum)=mynum then
    
    msgbox("Yes")
    
    else msgbox("No")
    
    end if
    
    end sub

    msgbox returned "Yes". If mynum = "12s3" then it returns "No". This result is the same as using IsNumeric:

    sub Test2()
    
    dim mynum
    
    mynum="12d3"
    
    msgbox(isnumeric(mynum))
    
    sub



    It returns "true". If mynum="12s3" it returns "False".

    Thursday, April 07, 2011 12:10 PM
  • Thanks guys for your responses. The worksheet function application.isnumber is not working.

    msgbox(application.isnumber(1234)) returns TRUE, but msgbox(application.isnumber("1234")) returns FALSE.

    I also trid below code

     

    sub test()
    dim mynum as Long
    mynum=textbox1.value
    msgbox(application.isnumber(mynum)) 
    end sub

    If enter a valid number, msgbox returns TRUE; if enter 12d3 I got missing type error message.

    I removed "as Long" from declare of mynum, msgbox always returns FALSE even I entered 1234.


    Qingping Cheng
    Thursday, April 07, 2011 1:02 PM
  • You're right.  The value of your textbox will always be a string and return false for isnumber().

    I think I would loop through the string looking for digits (and maybe up to one decimal separator).

     

     

    Thursday, April 07, 2011 1:06 PM
  • hi

    a possibility is to impose a numeric entry
     Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = InputChecking(TextBox1, KeyAscii)
    End Sub

    Function InputChecking(Textbox As MSForms.Textbox, ByVal Char As Integer)
    'imposing a numeric entry
    If Char < 48 Or Char > 58 Then
        InputChecking = 0
    Else
        InputChecking = Char
    End If
    End Function
     isabelle
    ------------------------------------------------------------------

    Le 2011-04-07 09:02, Qingping Cheng a écrit :

    Thanks guys for your responses. The worksheet function application.isnumber is not working.

    msgbox(application.isnumber(1234)) returns TRUE, but msgbox(application.isnumber("1234")) returns FALSE.
    I also trid below code

    sub test()
    dim mynum as Long
    mynum=textbox1.value
    msgbox(application.isnumber(mynum))
    end sub

    If enter a valid number, msgbox returns TRUE; if enter 12d3 I got missing type error message.

    I removed "as Long" from declare of mynum, msgbox always returns FALSE even I entered 1234.

    Thursday, April 07, 2011 1:25 PM
  • with decimal,

    Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = InputChecking(TextBox1, KeyAscii)
    End Sub
     Function InputChecking(Textbox As MSForms.Textbox, ByVal Char As Integer)
    'accept only a numeric entry with decimal or not
    If Char > 47 And Char < 58 Or Char = 46 Then
        InputChecking = Char
    Else
        InputChecking = 0
    End If
    End Function
     isabelle
    ------------------------------------------------------------------

    Thursday, April 07, 2011 1:40 PM
  • Hi

    Could you not link the text box to a cell, lets say A1?  Then you could use the formula =Type(A1*1).  When the text box contains a number this will return a 1 (Numeric) and for anything else 16 (error).

    Then you could use the value of this cell in your code may be like this

    If Activesheet.Range("A2").value = 1 then

    ' it's a number do this

    else

    ' it's not numeric do something else

    end if


    G North MMI
    Thursday, April 07, 2011 1:53 PM
  • Another one -
     
    msgbox CStr(Val("12d3" )) = "12d3"
     
    Peter Thornton
     
     
    Thursday, April 07, 2011 5:43 PM
    Moderator
  • Thank you all for your responses.

    Peter's one is a good idea but would not work with "12345d678".

    I trid the following code 

                if InStr(Ucase(mynumber), "D") > 0 Or InStr(Ucase(mynumber), "E") >0 Or IsNumeric(mynumber) = false


    Qingping Cheng

    Thursday, April 07, 2011 11:21 PM
  • Another one piggy backed on Peter Thornton's suggestion...


      Dim vNum As Variant
      On Error Resume Next
      vNum = Val("12345d678")
      On Error GoTo 0
      MsgBox CStr(vNum) = "12345d678"

    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (Permutations add-in:  with valid words highlighted)

    Friday, April 08, 2011 2:03 AM
  • Hi Jim,

    if vNum = Val("1234567891234567"), MsgBox CStr(vNum) <> "1234567891234567"

    Thanks


    Qingping Cheng
    Friday, April 08, 2011 4:02 AM
  • Yes that looks good. Reason of course for the overflow error is that
    "12345d678" or say 10E+682 is far to big for the processor to handle. To put
    it into context there are only about 10E+80 atoms in the universe
     
    BTW, should currency and dates be considered as numeric, eg "$123.45" (where
    $ is the local currency symbol), if so need a bit more work.
     
    Peter Thornton
     
    Friday, April 08, 2011 9:01 AM
    Moderator
  • Good question Peter. mgsbox(isnumeric("$123.45"))=TRUE, but msgbox(isnumeric(Now()))=FALSE. The textbox is for users to enter a numbers with 3 to 11 digits. when testing my application, I intended to enter "123456" but actually I entered "12e456" by mistake. And I set up the textbox with Autotab and its value was to be used as SQL server quey parameter value so "123e456" is not acceptable. Probably I should get back with isabelle's solution.


    Qingping Cheng
    Friday, April 08, 2011 10:56 AM
  • 'also you can check the value of entry from clipboard

    Private Sub TextBox1_BeforeDropOrPaste _
    (ByVal Cancel As MSForms.ReturnBoolean, _
    ByVal Action As MSForms.fmAction, _
    ByVal Data As MSForms.DataObject, ByVal x As Single, _
    ByVal Y As Single, ByVal Effect _
    As MSForms.ReturnEffect, ByVal Shift As Integer)

    Me.TextBox1.Text = ValeurTextbox(Me.TextBox1, GetOffClipboard())
    Cancel = True
    End Sub
     Function GetOffClipboard() As Variant
    Dim A As Variant
        Dim MyDataObj As New DataObject
        MyDataObj.GetFromClipboard
        GetOffClipboard = Extract(MyDataObj.GetText())
    End Function

    Function Extract(ByVal x As String)
    Dim Text As String, NbCar As Integer
    Dim S As Variant, R As String, Sep As String
    Dim P As Integer, T As Integer
    Sep = Format(0, ".")
    With Application
    Text = .Substitute(.Substitute(x, vbCrLf, ""), vbCrLf, "")
    End With
    NbCar = Len(Text)
        For A = 1 To NbCar
            S = Mid(Text, A, 1)
            Select Case S
                Case 0, 1, 2, 3, 4, 5, 6, 7, 8, 9
                    R = R & S
                Case "-"
                    If T = 0 Then
                        R = "-" & R
                        T = T + 1
                    End If
                Case ".", ","
                    If P = 0 Then
                        R = R & Sep
                        P = P + 1
                     End If
                Case "$", "€"
                Case Else
                    If Len(Text) > 20 Then Text = Left(Text, 20) & "..."
                    MsgBox "The value of clipboard" & vbCrLf & " ( " & Text & ")" & vbCrLf & _
                            "is not a number" & vbCrLf & _
                            "try again.", vbCritical + vbOKOnly, "Attention"
                    Extract = ""
                    Exit Function
            End Select
        Next
    Extract = CLng(R)
    End Function

    isabelle
    ------------------------------------------------------------------

    Friday, April 08, 2011 12:09 PM
  • Hi,

    There's nothing wrong with the IsNumeric function from my point of view. In the case of 1e3, which is scientific notation, IsNumeric returns true because 1e3 (in scientific notation) equals 1000. This is indeed a numeric value and it's OK that the function returns true. For the IsNumeric function, 'e' and 'd' mean the same: that you're entering data in scientific notation. Note that if you put 2 es, the function returns false.

    What I want to tell you, is that the function doesn't return true for every number mixed with letters. It returns true when that number mixed with letters can be interpreted as a number in scientific notation. If you don't want the user to enter numbers in scientific notation, just check for the 'e' and 'd' characters before using the IsNumeric function.

    Public Function MyIsNumeric(Number As String) As Boolean
    
    ' Check for numbers that could be in scientific notation, like 1e3.
    If 0 < InStr(Number, "e") Or 0 < InStr(Number, "d") Then
      MyIsNumeric = False
      Exit Function
    End If
    
    MyIsNumeric = IsNumeric(Number)
    
    End Function
    

    Kind regards,

    Carlos Mallen

     



    Saturday, April 09, 2011 2:34 PM
  • Hi Isabelle,

    I do need to check the value of entry from clipboard. Could you please provide the detail for the function ValeurTextbox? It seems that it is not a standard VBA function.

    Thanks!

     

     


    Qingping Cheng
    Sunday, April 10, 2011 5:10 AM
  • hi,

    yes, sorry I forgot to select this one
    http://cjoint.com/?2ekoWrAltlo
     Function ValeurTextbox(Textbox As MSForms.Textbox, Presse_Papier)
    If Presse_Papier = "" Then ValeurTextbox = Textbox.Text: Exit Function
    With Textbox
    If Left(Presse_Papier, 1) = "-" Or Left(.Text, 1) = "-" Then
        .Text = Replace(Left(.Text, .SelStart) & Presse_Papier & Right(.Text, Len(.Text) - .SelStart), "-", "")
    Else
        .Text = Left(.Text, .SelStart) & Presse_Papier & Right(.Text, Len(.Text) - .SelStart)
    End If
    End With
    ValeurTextbox = Textbox.Text
    End Function

    .

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-10 01:10, Qingping Cheng a écrit :

    Hi Isabelle,

    I do need to check the value of entry from clipboard. Could you please provide the detail for the function ValeurTextbox? It seems that it is not a standard VBA function.

    Thanks!




    • Marked as answer by qp Cheng Sunday, April 10, 2011 1:53 PM
    Sunday, April 10, 2011 12:48 PM
  • Thanks isabelle. Excellent job. It is exactly what I want and much appreciated!

     


    Qingping Cheng
    Sunday, April 10, 2011 1:53 PM
  • thank you Qingping Cheng for this appraisal, also you should add this code to prevent any error

    Private Sub TextBox1_AfterUpdate()
    If TextBox1 = "." Then TextBox1 = 0  'or ""
    End Sub

    .
    isabelle
    ------------------------------------------------------------------

    Le 2011-04-10 09:53, Qingping Cheng a écrit :

    Thanks isabelle. Excellent job. It is exactly what I want and much appreciated!


    Sunday, April 10, 2011 3:03 PM
  • Good point isabelle . I will add that code. Thanks!
    Qingping Cheng
    Sunday, April 10, 2011 10:44 PM
  • hi,

    I hope this time, I have been thinking of all errors possibilities, otherwise, thank you for letting us know

    Private Sub TextBox1_AfterUpdate()
    If TextBox1 = "." Then TextBox1 = 0
    If Len(TextBox1) - Len(Application.Substitute(TextBox1, ".", "")) > 1
    Then TextBox1 = 0
    End Sub

    .

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-10 18:44, Qingping Cheng a écrit :

    Good point isabelle . I will add that code. Thanks!

    Monday, April 11, 2011 2:51 AM
  • Did you mean

    If Len(TextBox1) - Len(Application.Substitute(TextBox1, ".", "")) > 0 Then TextBox1 = 0?

    If user entered "." I prefer

    TextBox1=Application.Substitute(TextBox1, ".", "")

    Thanks


    Qingping Cheng
    Monday, April 11, 2011 3:41 AM

  • no, it depends if you want accept decimal numbers or not,
    if you want accept decimal numbers and if the user writes, by exemple,
    "12.1.3"  or only ".."  this values weren't numeric.
    but  if the user writes "12.1",  the next code
    TextBox1 = Application.Substitute(TextBox1, ".", "")
    will been changing value to "121"
    .

    isabelle
    ------------------------------------------------------------------

    Le 2011-04-10 23:41, Qingping Cheng a écrit :

    Did you mean

    If Len(TextBox1) - Len(Application.Substitute(TextBox1, ".", ""))>  0 Then TextBox1 = 0?

    If user entered "." I prefer

    TextBox1=Application.Substitute(TextBox1, ".", "")

    Thanks

    Monday, April 11, 2011 5:34 AM
  • You are right if accept decimal. The textbox I created is for users to enter ID number or barcode and would not accept decimal so I also excluded char 46 (".") in your InputChecking function. I will need a textbox for average sales and in that case a decimal entry is acceptable and must use your AfterUpdate event code.
    Qingping Cheng
    Monday, April 11, 2011 6:06 AM