VBA’s standard InputBox returns text, not an Integer. In Excel VBA, use Application.InputBox with Type:=1 to request a number, then check that it has no fractional part and fits the range of the variable before converting it.
What an integer input means in VBA
An integer is a whole number with no fractional part: examples include -8, 0, and 42, but not 4.2. Getting one from a dialog involves two separate jobs: collecting the user’s response and deciding whether that response meets your rules. The basic VBA InputBox function collects text, so it does not itself guarantee that the user entered a whole number.
There is also a VBA naming detail worth knowing: the Integer data type is a signed 16-bit value, with a range from -32,768 through 32,767. For most counters and ordinary whole-number input, Long is a better choice; it is a signed 32-bit value from -2,147,483,648 through 2,147,483,647. Excel worksheet numbers are generally stored with floating-point precision, so a value being shown with no decimal places does not mean it is an integer or that VBA has validated it.
InputBox and Application.InputBox are different
In Excel VBA, InputBox is the VBA function that returns a String. The Excel-specific Application.InputBox method has a Type argument that can restrict the kind of response. Setting Type:=1 asks for a number and returns the accepted value as a Variant containing a numeric value. It is useful for rejecting ordinary text at the dialog, but it still permits decimals: a user can enter 3.5. You must validate the whole-number requirement yourself.
This distinction matters when copying code between programs. The VBA function is available in VBA projects across Office hosts, while Application.InputBox refers to the host application’s object model; the examples here are for Excel VBA. The two names are not interchangeable simply because both display a dialog. If an answer must be an integer, specify whether decimals are acceptable and what numeric limits apply, regardless of which dialog is used.
Request a number with Type:=1
A simple numeric prompt can look like this. It stores the answer as a Variant because Application.InputBox can return different kinds of values, including a Boolean value when the user cancels.
Sub AskForNumber()
Dim answer As Variant
' Request numeric input; this still allows decimals.
answer = Application.InputBox(Prompt:="Enter a number:", _
Title:="Number", Type:=1)
If VarType(answer) = vbBoolean Then Exit Sub
MsgBox "The numeric answer is " & CStr(answer)
End Sub
For this example, entering 12.5 is valid because it asks for any number, not an integer. The Boolean check handles Cancel before the value is used. A numeric zero is not mistaken for Cancel: zero is numeric, while Cancel returns Boolean False. Do not store the result directly in a Long if you need to distinguish cancellation, because converting or assigning too early can obscure what the dialog returned or cause a type mismatch.
Validate whole numbers and range before conversion
The following Excel macro accepts any whole number in the range of a VBA Long. It repeats the prompt after a decimal or an out-of-range value, and exits when the user cancels. Fix removes the fractional part toward zero; comparing the original numeric value with that result detects decimals without accidentally rounding them.
Sub GetWholeNumber()
Dim answer As Variant
Dim wholeNumber As Long
Do
' Type 1 requests a number, but can accept a decimal.
answer = Application.InputBox( _
Prompt:="Enter a whole number:", _
Title:="Whole number", Type:=1)
' Cancel returns Boolean False; numeric zero is not Boolean.
If VarType(answer) = vbBoolean Then Exit Sub
If answer <> Fix(answer) Then
MsgBox "Enter a whole number without a decimal part."
ElseIf answer < -2147483648# Or answer > 2147483647# Then
MsgBox "The number must fit in a VBA Long."
Else
wholeNumber = CLng(answer)
Exit Do
End If
Loop
MsgBox "You entered " & CStr(wholeNumber)
End Sub
The range checks happen before CLng, so conversion is only attempted for a value that fits. The # suffix marks the boundary constants as Double values, suitable for comparison with the numeric result. For example, entering 27 passes both checks and is stored as 27; 27.4 is rejected rather than rounded. If you need the narrower VBA Integer type, change the bounds to -32,768 and 32,767 and declare the destination as Integer. In most code, retaining Long avoids that unnecessary limit.
Before running a macro, save a copy of the workbook. Macro actions cannot be undone with Ctrl+Z, and that applies even when the dialog itself only collects a number: later code may change cells or other workbook content.
Why CLng alone does not validate an integer
A common misconception is that converting a response with CLng makes it a valid whole-number input. It does not. VBA conversion functions round fractional values; they do not serve as a decimal-rejection test. For example, a value of 3.5 can be rounded by CLng rather than rejected. VBA’s rounding behavior also uses banker’s rounding at exact halfway values, so relying on conversion can produce results a user did not intend.
Test the value before converting it. For a numeric value, checking whether it equals Fix(value) is a direct whole-number test: Fix(3) is 3, while Fix(3.2) is 3. Negative numbers work as well; Fix(-3.2) is -3, so it does not equal the original value. This is preferable to checking only how a value is displayed, because number formatting can hide fractional digits without changing the underlying number.
Using the standard InputBox with text validation
Sometimes the standard InputBox is appropriate, for example if the VBA project is not specifically in Excel or if you want full control over how the response is interpreted. Its result is text, so the code should check for a blank or cancelled response, confirm that the text represents a number, and then reject fractions and values outside the destination range. IsNumeric can help with the numeric check, but it does not prove that a value is an integer; values such as 2.75 are numeric too.
There is a cancellation limitation with the standard function: pressing Cancel returns an empty string, and submitting an empty response also returns an empty string. The function does not give you a separate Cancel flag. If the distinction matters, use Application.InputBox in Excel, whose Cancel result can be identified as Boolean False. If the distinction does not matter, treating both an empty answer and Cancel as “no value supplied” is a reasonable design.
Text parsing also has locale considerations. Decimal separators and accepted numeric formats can vary with Windows regional settings and Excel’s environment. Avoid assuming that every user types a decimal point or thousands separator in the same way. If you need a predictable input format, explain it in the prompt and test with the regional settings used by your intended users.
Choose the right variable and input rules
Before writing the dialog, decide what counts as acceptable. A quantity of items might need to be a nonnegative whole number, while a row offset may allow negatives. A worksheet row number, a count, and an identifier can all look like integers but need different limits and handling. The dialog should explain those rules rather than merely saying “Enter a number.”
- Use
Longfor typical whole-number values in VBA; reserveIntegerfor cases where its narrower range is intentional. - Check that the value is integral before converting, so decimals are rejected rather than rounded.
- Apply the application’s own limits, such as a minimum of zero or a maximum quantity, in addition to the data type’s bounds.
- Handle Cancel explicitly and decide whether an empty entry should mean the same thing.
- Use a clear message when the user enters a value outside the allowed range.
These checks are separate from worksheet formatting. Setting a cell’s number format to show zero decimal places changes its appearance, not the value users or formulas can store. If the goal is to constrain manual worksheet entries as well as macro input, Excel’s data validation settings are a separate tool; a macro’s prompt does not automatically enforce rules for edits made directly in cells.
Common problems and reliable fixes
If a prompt that is supposed to accept integers accepts 4.6, the code probably requested numeric input but did not test for a fractional part. Add the answer <> Fix(answer) check before conversion. If the macro unexpectedly turns a decimal into a nearby whole number, inspect for CLng or another conversion happening before validation.
If Cancel causes a type mismatch, the code may be treating the returned Variant as a number before checking its type. With Application.InputBox, test VarType(answer) = vbBoolean first. This is more precise than checking whether the answer equals zero, since zero is a legitimate numeric response. If an input appears valid but raises an overflow error, compare it with the target type’s limits before converting. Finally, if negative values behave unexpectedly in a test using Int, remember that Int rounds down, while Fix truncates toward zero; equality with the original value works for detecting an integer either way, but Fix makes that test straightforward.
Frequently Asked Questions
Can VBA InputBox return an Integer?
The standard VBA InputBox returns a String, not an Integer. In Excel, Application.InputBox with Type:=1 requests a numeric response, but you still need to check that it is whole and within range before assigning it to an integer variable.
How do I stop users from entering decimals?
Request a number and compare it with Fix(value). If the values differ, the response has a fractional part and should be rejected or re-entered. Do this before calling CLng, which can round instead of rejecting decimals.
What does Application.InputBox return when I click Cancel?
It returns Boolean False in a Variant. Check the result’s type before treating it as a number; checking whether it equals zero is not enough because zero can be a valid answer.
Should I use Integer or Long in VBA?
For most ordinary whole-number variables, use Long. VBA’s Integer range is only -32,768 to 32,767, whereas Long supports values from -2,147,483,648 to 2,147,483,647.
Does Type:=1 reject decimal numbers?
No. It restricts the response to numeric input, but numeric values can include fractions. Add a separate whole-number test if decimal values are not allowed.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Range.Resize: Change a Range’s Dimensions in ExcelIn Excel VBA, use the Resize property to return a range with a different number of rows or…
- Excel VBA Array of Worksheets: Create and Use OneIn Excel VBA, you can work with several worksheets as a group by passing an array of sheet…
- Clear Contents Macro Excel: VBA Code and Safe Use GuideIn Excel, a clear-contents macro removes cell values and formulas while leaving cell formatting in place. In Microsoft…
- Excel VBA Date and Time Functions: A Practical GuideExcel VBA’s main current-date and time functions are Date, Time, and Now; use DateSerial and TimeSerial to build…
- Excel VBA Filter Range: AutoFilter Examples and FixesTo filter a range with Excel VBA, use the range’s AutoFilter method and specify the field number and…
- VBA Copy and Paste a File: Practical Code and Safe StepsTo copy a file with VBA in Excel for Microsoft 365 on Windows, use the FileCopy statement or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.