Prevent Variable Overflow from InputBox user error

Question

I was writing some code that required the user to input a number, which was then stored in a variable. While testing the code I input a number larger than the variable type would allow and I got an variable overflow error. So I wrote this code to prevent that from happening. It works, but I was hoping there was a more concise way to do it, or even a function already part of VBA that I could use.

Sub PreventOverflow()

    Dim IsThisANumber As String
    Dim Number As Integer
    Dim PreventOverflow As Long
    Dim Cancel As VbMsgBoxResult

    Number = 0
    PreventOverflow = 0

    IsThisANumber = InputBox("Enter a number less than 32768")

    Do While Number = 0

        Do While PreventOverflow = 0
            If IsNumeric(IsThisANumber) = True Then
                If Len(IsThisANumber) < 6 Then
                    PreventOverflow = IsThisANumber
                Else
                    IsThisANumber = InputBox("Your number was not less than 32768" & vbNewLine & "Please enter a number less than 32768")
                    PreventOverflow = 0
                End If
            ElseIf IsThisANumber <> "" Then
                IsThisANumber = InputBox("Your did not enter a number" & vbNewLine & "Plese enter a number less than 32768")
                PreventOverflow = 0
            Else
                Cancel = MsgBox("You did not enter a number.  Do you want to continue?", vbYesNo)
                If Cancel = vbNo Then
                    Exit Sub
                Else
                    IsThisANumber = InputBox("Enter a number less than 32768")
                    PreventOverflow = 0
                End If
            End If
        Loop

        If PreventOverflow < 1 Or PreventOverflow > 32767 Then
            IsThisANumber = InputBox("Your number was not less than 32768" & vbNewLine & "Please enter a number less than 32768")
            PreventOverflow = 0
        Else
            Number = PreventOverflow
        End If
    Loop

End Sub

Answer 1

I think you over engineered this function a bit, which is easy to do. Simply store your value in a variant to check if it will fit in an integer.

 Private Function IsInteger(ByVal prompt As String) As Integer
    Dim longHolder As Variant
    longHolder = InputBox(prompt)
    If IsNumeric(longHolder) Then
        If Int(longHolder) = CDbl(longHolder) Then
            If Abs(longHolder) < 32768 Then
                IsInteger = longHolder
            Else
                MsgBox "Too Big"
                IsInteger = 0
            End If
        Else
            MsgBox "Decimals!"
            IsInteger = 0
        End If
    Else
        MsgBox "Not a number"
        IsInteger = 0
    End If
End Function

You would use it like this

Sub test()
    Dim x As Integer
    x = IsInteger("give me an integer")
End Sub

---

That being said, integers are obsolete. According to msdn VBA silently converts all integers to long. So, just use Long to begin with.