Prevent Variable Overflow from InputBox user error 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) &lt; 6 Then
                    PreventOverflow = IsThisANumber
                Else
                    IsThisANumber = InputBox("Your number was not less than 32768" &amp; vbNewLine &amp; "Please enter a number less than 32768")
                    PreventOverflow = 0
                End If
            ElseIf IsThisANumber &lt;&gt; "" Then
                IsThisANumber = InputBox("Your did not enter a number" &amp; vbNewLine &amp; "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 &lt; 1 Or PreventOverflow &gt; 32767 Then
            IsThisANumber = InputBox("Your number was not less than 32768" &amp; vbNewLine &amp; "Please enter a number less than 32768")
            PreventOverflow = 0
        Else
            Number = PreventOverflow
        End If
    Loop

End Sub
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) &lt; 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.