CODEWITHSUNDEEP
vbaexcel
Helmuts Vanags
Helmuts Vanags

Reputation: 3

Can't parse data from InputBox as integer (Visual Basic for Excel)

I'm trying to get a value that user inputs manually as an integer and I have to take into account the fact that user might not enter an integer. That's why I'm trying to catch a type mismatch error. However, when I enter an integer value I still get type mismatch error.

This is the piece of code that makes this error.

Dim number As Integer
On Error GoTo error
    number = InputBox("Enter an integer:")
error:
    MsgBox ("Input error. Make sure you enter an integer value.")
    Exit Sub

Upvotes: 0

Views: 6273

Answers (2)

user6432984
user6432984

Reputation:

The Application.InputBox Method allows you to specify what the Type of data returned.

MSDN Application.InputBox Method (Excel)

enter image description here

Sub Example1()
    Dim number As Integer
    number = Application.InputBox(Prompt:="Enter an integer:", Type:=1)

End Sub

Because Application.InputBox with the Type 1 parameter will return 0 if the user cancels, I would prefer to use a standard InputBox. The way to use it is to have a separate variable capture the value and test that the return value meets your criteria. In this way, you can avoid any errors. 

Sub Example2()
    Dim number As Integer
    Dim result As String
    result = InputBox("Enter an integer:")
    If result = "" Then
        MsgBox "Good Bye", vbInformation, "Action Cancelled"
        Exit Sub
    ElseIf IsNumeric(result) Then
        If CDbl(result) > CInt(result) Then
            MsgBox "You entered a Decimal" & vbCrLf & "Try Again", vbInformation, "Intergers Only"
        Else
           number = result
        End If
    Else
        MsgBox "Intergers Only" & vbCrLf & "Try Again", vbInformation, "Intergers Only"
    End If

End Sub

Upvotes: 3

Rory
Rory

Reputation: 34045

Here is one way:

Dim number                As Integer
On Error Resume Next
number = InputBox("Enter an integer:")
If Err.number <> 0 Then
    MsgBox ("Input error. Make sure you enter an integer value.")
    Exit Sub
End If
On Error GoTo 0

Note though that this will accept non-integer number entries; it's not clear if that's a concern.

Upvotes: 1

Related Questions