inputBox Excel VBA Integer problems

Question

I am new to VBA and I'm trying to create a macro that from a inputBox accepts a number between 0 and 1000 and converts it to hexadecimal. Well it works, but I am struggling to keep the program accepting that range ( 0 - 1000). This is what happens:

If I input -1 it throws a error;
If I input -1001 it throws a FFFFFFFC17;
If I input any value above 1000 it doesn't throw a MsgBox (I am not familiar with causing error on excel for now).

I've done first like this:

Sub DecToHex()
    Dim inputDec As Integer
    Dim outputHex As String

    inputDec = InputBox("Decimal?")

    If inputDec <= 1000 And inputDec >= 0 Then
        outputHex = Application.WorksheetFunction.Dec2Hex(inputDec)
        MsgBox ("Hex: " + outputHex)
    Else
        MsgBox ("Error! Please define decimal. It must be larger than zero and less than 1001")
        inputDec = InputBox("Decimal?")
        outputHex = Application.WorksheetFunction.Dec2Hex(inputDec)
        MsgBox ("Hex: " + outputHex)
    End If

End Sub

But then I thought well inputBox gives me input as string, so maybe I should accept values as string, so I changed:

Dim inputDec As Integer 
'Changed to
Dim inputDec As String

Which still did a poorly control on variables ( ie. it accepts -1200, as also 1200 ). So can you point out what am I doing wrong? Maybe it's the Worksheet Function I'm not reading well. I know it's newbie mistake but it's important for me to understand how to control these input variables from inputBox.

Siddharth Rout Siddharth Rout · Accepted Answer · 2015-05-23T22:31:34

You need to declare the inputDec As Variant
You need to Handle the Cancel Button
You need to put the code in a loop so that when user enters an invalid number, the inputbox can pop up again.
You need to use Application.InputBox with Type:=1 so that only numbers can be accepted.

Try this

Sub DecToHex()
    Dim inputDec As Variant
    Dim outputHex As String

    Do
        inputDec = Application.InputBox("Decimal?", Type:=1)

        '~~> Handle Cancel
        If inputDec = "False" Then Exit Do

        If inputDec <= 1000 And inputDec >= 0 Then
            outputHex = Application.WorksheetFunction.Dec2Hex(inputDec)
            MsgBox ("Hex: " + outputHex)
            Exit Do '<~~ Exit the loop
        Else
            MsgBox ("Error! Please define decimal. It must be larger than zero and less than 1001")
        End If
    Loop
End Sub

inputBox Excel VBA Integer problems

1 Answers