CODEWITHSUNDEEP
vbaexcel
user4974730
user4974730

Reputation:

Edge cases in IsNumeric- is this overthinking it

I have code which looks like this:

Select Case IsNumeric(somevariable)
    Case True
        Resume Next
    Case False
        Call notnum
    Else
        Call MyErrorHandler
End Select

Is this overthinking it? Is there a chance IsNumeric will return something other than True or False here or is this bad programming practice?

Upvotes: 0

Views: 126

Answers (3)

Eric K.
Eric K.

Reputation: 834

Input box can have different type of input validation. Try this

something = Application.InputBox("Pls Insert the Number", Type:=1)
If something = False Then Exit Sub
'Type:=0 A formula
'Type:=1 A number
'Type:=2 Text (a string)
'Type:=4 A logical value (True or False)
'Type:=8 A cell reference, as a Range object
'Type:=16 An error value, such as #N/A
'Type:=64 An array of values

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53166

From OP's comment I'm not using my error handler. I want to do stuff with the hopefully numeric output

Sub demo()
    Dim inputs As Variant

    inputs = InputBox("Prompt", "Title", "Default")

    If Not IsNumeric(inputs) Then
        notnum
    Else
        ' Do what you want with numeric input inside the Else

    End If

    ' Maybe do more stuff irrespective of input

End Sub

Sub notnum()
    ' do not numeric stuff here
End Sub

Or if you want to keep prompting for numeric input until the users gets it right or cancels

Sub demo2()
    Dim inputs As Variant

    Do
        inputs = InputBox("Enter something Numeric", "Title", "Default")

    Loop Until IsNumeric(inputs) Or inputs = vbNullString
    If Not inputs = vbNullString Then
        ' Do wht you want with numeric input inside the Else
    End If

    ' Maybe do more stuff irrespective of input

End Sub

Upvotes: 0

Dan Donoghue
Dan Donoghue

Reputation: 6216

Don't need the else as it will be true or false however, just a note the Else should be Case Else (moot point though as you are about to delete it)

Based on this though I wouldn't use a case for only 2 options:

If IsNumeric(somevariable) then
    Resume Next
Else
    Call MyErrorHandler
End if

Edit: Here is how error checking works:

Sub SheetError()
    Dim MySheet As String
    On Error GoTo ErrorCheck
    MySheet = ActiveSheet.name
    Sheets.Add
    ActiveSheet.name = MySheet
    MsgBox "I continued the code"
    ActiveSheet.name = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
    MsgBox "I will never get to here in the code"
    End
ErrorCheck:
    If Err.Description = "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic." Then
        Resume Next
    Else
        MsgBox "Error I am not designed to deal with"
    End If
End Sub

Copy and paste this module to your personal workbook or to a new workbook and run it, step through line by line using F8 to see how it is actually dealing with the error.

Upvotes: 2

Related Questions