Yes—VBA can ask for an integer with an input box, but neither common input-box option automatically guarantees a whole number. For reliable validation, use Application.InputBox to read the response, check that it is numeric and has no fractional part, then convert it to a suitable VBA integer type such as Long.
Two VBA input boxes, with different behavior
VBA has a built-in InputBox function, and Excel has the Application.InputBox method. Their names are similar, but their return behavior matters when you need an integer.
InputBoxreturns text. You must convert that text yourself and handle invalid entries. If the user clicks Cancel, it returns an empty string, which is indistinguishable from submitting a blank response.Application.InputBoxhas aTypeargument. SettingType:=1asks for a number, but that does not mean the value is an integer: a decimal such as 4.5 is still numeric. SettingType:=2asks for text, which is useful when your code needs to distinguish a blank entry from a number.
For an integer-only prompt, a practical approach is to use Application.InputBox with Type:=2, validate the returned text, and then convert it. This also lets the macro recognize Cancel separately from a blank or invalid entry.
What integer type should you use?
In VBA, the type named Integer is a 16-bit signed number with a range of -32,768 to 32,767. That range is often smaller than people expect. The Long type is a 32-bit signed number, ranging from -2,147,483,648 to 2,147,483,647, and is usually the better choice for ordinary whole-number input.
Excel worksheet row numbers, for example, can exceed the maximum value of VBA’s Integer type. Use Long when storing row numbers, counts, or other values that might exceed 32,767. Choose Integer only when you specifically need its smaller range and will check that input stays within it. A variable’s type does not validate what the person typed; your code must still check the value before converting it.
A robust example that accepts only whole numbers
This macro accepts positive or negative whole numbers that fit in a Long. It rejects blank, nonnumeric, fractional, and out-of-range entries, and it exits quietly if the user presses Cancel. Because the text is checked before conversion, a decimal is not silently rounded into a different value.
Sub AskForWholeNumber()
Dim answer As Variant
Dim numberValue As Double
Dim wholeNumber As Long
Do
' Request text so blank and Cancel can be handled separately.
answer = Application.InputBox( _
Prompt:="Enter a whole number:", _
Title:="Whole number", _
Type:=2)
' Application.InputBox returns Boolean False when Cancel is pressed.
If VarType(answer) = vbBoolean Then
Exit Sub
End If
If Len(CStr(answer)) = 0 Then
MsgBox "Enter a value, or press Cancel to stop."
ElseIf Not IsNumeric(answer) Then
MsgBox "The entry must be numeric."
Else
numberValue = CDbl(answer)
If numberValue <> Fix(numberValue) Then
MsgBox "Enter a whole number without a decimal part."
ElseIf numberValue < -2147483648# Or _
numberValue > 2147483647# Then
MsgBox "The value is outside the range of a Long."
Else
wholeNumber = CLng(numberValue)
Exit Do
End If
End If
Loop
MsgBox "You entered " & CStr(wholeNumber)
End Sub
The comparisons with Fix ensure there is no fractional part: Fix(7) is 7, but Fix(7.2) is 7. The range test occurs before CLng, preventing an overflow error during conversion. After a valid response, the example displays it; in a working macro, you would usually use wholeNumber in the next calculation or procedure.
How to add and run the macro
In Excel for Microsoft 365 on Windows, save a copy of the workbook first, then press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the code into the module, and run AskForWholeNumber from the editor or from Excel’s Macro dialog. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the VBA code.
On a Mac, VBA is available in desktop Excel, though the keyboard shortcut can depend on the keyboard and function-key settings; Option+F11, sometimes with Fn as well, opens the editor on many setups. The editor’s Insert > Module command is the same idea. Excel for the web does not run VBA macros, so use desktop Excel to run this code. Macros cannot be undone with Ctrl+Z, so make a backup before running a macro that changes workbook data.
Why not use Type:=1?
Application.InputBox with Type:=1 is convenient when the response should be numeric. It restricts the prompt to numeric input, but it does not restrict the response to whole numbers. The user can enter a decimal, and your macro still needs to decide whether to reject it, round it, or otherwise handle it.
There is another practical distinction: the numeric input mode can make it harder to distinguish an intentionally blank response from a numeric zero. If blank and zero have different meanings in your task, request text with Type:=2 and validate it as in the example. If decimals are acceptable, or if the prompt is only a quick numeric entry where blank handling is unimportant, Type:=1 may be sufficient—but convert and check the value before assigning it to a narrower type.
Conversion, rounding, and locale details
VBA conversion functions do not all behave the same way. CLng converts a number to a Long and rounds fractional values; it does not mean “accept only integers.” At exact halfway values, VBA uses banker’s rounding, which rounds to the nearest even integer. For example, conversion can change an input of 2.5 to 2. Therefore, calling CLng alone is not a sound way to enforce an integer-only rule.
The sample checks the fractional part before calling CLng, so it rejects 2.5 instead of rounding it. If your requirements call for rounding, state the rule explicitly and use an intentional rounding approach; do not let a conversion function silently choose the behavior. Also consider whether negative values, zero, leading or trailing spaces, and values expressed with scientific notation should be permitted. IsNumeric can recognize numeric forms beyond simple digits, so add stricter text rules if the prompt must accept only a particular format.
Numeric text follows the computer’s regional settings. A decimal separator may be a period in one locale and a comma in another, and IsNumeric and CDbl interpret text according to the system’s conventions. If a workbook will be used across regions, decide whether to accept local number formats or require a fixed format, then test that choice on the computers that will run the macro.
Common mistakes and safer choices
- Assuming numeric means integer: A numeric prompt can allow decimals. Check that the value equals
Fixof itself if fractions are not allowed. - Using Integer for large values: Values above 32,767 overflow an
Integer. UseLongfor a wider whole-number range and check its limits. - Converting before validating: Conversion can round or raise an overflow error. Check numeric validity, whole-number status, and range first.
- Treating Cancel as zero: With
Application.InputBox, Cancel returns Boolean False. Check the returned value’s type instead of relying on a comparison that could coerce it. - Forgetting blank input: With text input, explicitly decide whether an empty response should be rejected, treated as a default, or used to stop the task.
For a simple one-time prompt, a direct input box may be enough. For a macro that writes to cells, changes records, or processes many entries, validation and a clear Cancel path are important: they prevent bad input from reaching later code and make the prompt’s behavior predictable.
Frequently Asked Questions
Can VBA InputBox return an Integer directly?
The built-in InputBox returns a String, not an Integer. Application.InputBox can return a numeric value with Type:=1, but you still need to check that it has no fractional part and fits the type you plan to use.
How do I make an Excel VBA InputBox accept only whole numbers?
Read the response, check it with IsNumeric, convert it to a number for checking, and compare it with Fix of that number. Check the allowed range before assigning it to an Integer or Long.
What does Application.InputBox return when I click Cancel?
It returns the Boolean value False. Because that is different from the text response False, code can identify Cancel by checking VarType(answer) for vbBoolean when using text input.
Should I use Integer or Long in VBA?
Use Long for most whole-number variables, especially row numbers and counts that could exceed 32,767. Use Integer only when its smaller range is suitable and you have enforced that range.
Can I run VBA InputBox code in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the macro; the VBA editor and keyboard shortcut details can vary slightly by platform.
Related
- Excel Text to Columns: Split at Line Breaks
- Excel Text to Columns Greyed Out? Causes and Fixes
- Excel Text to Columns: How to Split Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Loop Syntax: For, Do, and Each ExplainedExcel VBA loops repeat a block of code: use For...Next for a known number of repetitions, For Each...Next…
- VBA InputBox Format: Syntax, Types, and Examples GuideIn VBA, the basic InputBox format is InputBox(prompt, title, default): it displays a dialog and returns the user’s…
- VBA MsgBox OK Button: Syntax, Examples, and ChoicesTo show an OK-only message box in Excel VBA, use MsgBox "Your message", vbOKOnly, "Your title". The OK…
- VBA: Filter a PivotTable by Cell Value AutomaticallyTo filter an Excel PivotTable from a cell, use VBA to read that cell and assign its value…
- Excel VBA AutoFilter with an Array: A Practical GuideYes. In Excel VBA, pass a one-dimensional array of the values you want to keep to the range’s…
- VBA Dictionary SumIf: How to Group and Sum Data in ExcelTo perform a SUMIF-style total in VBA, use a Scripting.Dictionary: make each category a key and add each…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.