In Excel VBA, the Type argument belongs to Application.InputBox and controls what kind of response the dialog accepts: text, a number, a range, or another supported value. For example, Application.InputBox(Prompt:="Enter a number", Type:=1) requests a number, while Type:=8 lets the user select a worksheet range.
What the InputBox Type Argument Means
Application.InputBox is a dialog that asks the user for input while a macro is running. Its optional Type argument restricts the response and determines the kind of value Excel returns. That distinction matters: a range selection is an object, a numeric response is a number, and a text response is text. Choosing a suitable type can prevent invalid input and reduce the amount of validation your macro needs to do.
The argument is optional. If you leave it out, the dialog accepts text, which is often adequate for a simple question. If your macro needs to do something specific with the answer—such as calculate with it or work on a selected range—setting Type makes the expected input clearer and can catch unsuitable responses sooner.
Supported Type Values
Microsoft Excel recognizes these values for the Type argument:
0: A formula, returned as text.1: A number.2: Text, including a text string.4: A logical value, such as TRUE or FALSE.8: A range reference, returned as a Range object.16: An error value.64: An array of values.
Some types can be combined by adding their numbers. For instance, 1 + 2 is 3, which allows a number or text response. Combinations are useful when more than one response category is acceptable, but they also mean your code should be prepared to handle the possible return types. A type value of 0 is not the same as omitting the argument: it asks for a formula-like response rather than unrestricted text.
Application.InputBox Versus InputBox
VBA has two commonly confused input dialogs. Application.InputBox is an Excel application method with a Type argument. The older VBA InputBox function does not have that argument and returns a string. Use the application method when you want Excel to constrain the response or let the user point to worksheet cells.
There is also a practical difference when the user presses Cancel. The VBA InputBox function returns an empty string both when the user cancels and when the user submits a blank response, so those cases can be difficult to distinguish. Application.InputBox returns the Boolean value False when canceled. Store its result in a Variant and check for cancellation before treating it as the requested value.
Do not confuse Application.InputBox with a cell formula or with a form control. It is a modal dialog: the user must respond to or cancel it before continuing to interact with the workbook. For a longer workflow with several fields, instructions, or validation messages, a UserForm may be a better fit.
How to Choose a Type
Start with what the macro needs to do with the response, rather than with the appearance of the dialog. A numeric type is sensible for a quantity that will be used in arithmetic; a range type is appropriate when the user should identify cells on a sheet. A text type suits names, labels, and other words. Selecting the narrowest useful type makes the prompt more predictable.
- Choose
Type:=1for a number your code will calculate with. - Choose
Type:=2for a text answer such as a sheet label or category name. - Choose
Type:=4when a logical TRUE/FALSE response is appropriate. - Choose
Type:=8when the user needs to select one or more worksheet cells. - Choose
Type:=0when the requested answer is a formula expression, not merely a number or a text label. - Use combined values only when you deliberately accept more than one kind of response and your code can handle each one.
Consider the real task as well as the type. If users must enter a whole number from 1 through 12, Type:=1 accepts numbers but does not by itself enforce that range or require an integer. Your macro still needs to check conditions such as whether the value is whole and falls within the permitted limits. Type restriction is a first layer of input control, not a complete validation strategy.
Example: Requesting a Number
This example asks for a numeric value and exits cleanly if the user cancels. Because the answer is stored as a Variant, the code can check whether Excel returned the Boolean cancellation value before converting the answer to a number. The example displays the accepted value; it does not impose a business-specific minimum or maximum.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z.
Sub AskForNumber()
Dim answer As Variant
' Ask Excel to accept a number.
answer = Application.InputBox(Prompt:="Enter a number:", _
Title:="Numeric input", Type:=1)
' Cancel returns Boolean False.
If VarType(answer) = vbBoolean Then
If answer = False Then Exit Sub
End If
MsgBox "You entered " & CStr(answer)
End Sub
For production code, add checks that fit the task. A number can be valid input for the dialog but still be unacceptable to your procedure—for example, a negative quantity when only positive quantities are allowed. Also decide how your macro should handle decimal values. The dialog’s type does not decide those rules for you.
Example: Letting the User Select a Range
With Type:=8, Excel lets the user select cells in the worksheet. The returned value is a Range object, so the code must use Set when assigning that object to a range variable. The following example first stores the response in a Variant so it can distinguish a canceled dialog from a selected range.
Save a copy of the workbook before running this macro as well; changes made by VBA cannot be reversed with Ctrl+Z.
Sub AskForRange()
Dim answer As Variant
Dim selectedRange As Range
' Ask the user to select cells on a worksheet.
answer = Application.InputBox(Prompt:="Select a cell or range:", _
Title:="Choose a range", Type:=8)
' Cancel returns Boolean False, not a Range.
If VarType(answer) = vbBoolean Then
If answer = False Then Exit Sub
End If
If TypeName(answer) <> "Range" Then Exit Sub
Set selectedRange = answer
MsgBox "Selected: " & selectedRange.Address(External:=True)
End Sub
The resulting address includes the worksheet and workbook context because the example uses External:=True. You can then use selectedRange in later code—for example, to read a value or apply formatting. Selecting a range does not automatically make the macro safe to modify it; decide which operations are appropriate before acting on user-selected cells.
Prompt, Title, Default, and Other Arguments
The general call can include more than a type. Application.InputBox accepts arguments for the prompt, dialog title, default response, position, help file, help context, and type. Named arguments such as Prompt:= and Type:= make a call easier to read and reduce the chance of putting a value in the wrong position. Optional arguments can be omitted when the defaults are suitable.
A default response can suggest a starting value, but it is not a substitute for checking the answer. Similarly, a clear prompt should tell users what to enter or select and, where relevant, the expected units or boundaries. A vague prompt such as “Enter value” gives little guidance, even if the type is numeric. Keep the dialog focused on one decision and provide useful feedback if a later validation check fails.
Common Problems and Reliable Handling
A frequent mistake is declaring the result as a specific type too early. Since the dialog can return different kinds of values—and cancellation returns Boolean False—a Variant is a practical choice for receiving the result. After checking cancellation, convert or assign it to the type the rest of the procedure needs.
- Cancel is not an empty string: With
Application.InputBox, check for BooleanFalse. Do not assume a text comparison catches cancellation. - Type 8 needs object assignment: Assign a returned range with
Set. Treating a Range object as an ordinary string or number can cause errors. - A permitted type is not a full rule: Type 1 does not enforce a particular range, integer-only input, or a required unit. Validate those conditions in code.
- Blank and canceled are different cases: A user may submit an empty text answer when text is allowed. Decide whether to accept it, ask again, or exit.
- Do not hide every error: Broad error suppression can conceal a real bug. Prefer to check the returned value and handle expected cancellation explicitly.
When an input must meet several rules, use a loop to prompt again after invalid input, or show a clear message and exit. For complicated validation, a UserForm can provide separate controls and more tailored error handling than a single InputBox.
Excel Platform and Macro Considerations
This applies to Excel for Microsoft 365 on Windows. VBA and Application.InputBox are desktop Excel features; Excel for the web cannot run VBA macros. Excel for Mac also supports VBA, but dialog appearance and some surrounding interface details can differ. If a workbook will be used on both Windows and Mac, check the complete macro workflow in both desktop versions rather than assuming every interface detail is identical.
Macros may be blocked by security settings or organizational policy, and users should enable them only for workbooks they trust. Save the file in a macro-enabled format, such as .xlsm, if it must retain VBA code. Keep a backup before running code that changes workbook contents: macros do not participate in Excel’s normal undo history.
Frequently Asked Questions
What is the default type for Application.InputBox?
If the Type argument is omitted, the response is treated as text. Specify a type when the macro needs a number, range, or another supported kind of value.
What does Type:=8 do in VBA?
It asks the user to select a cell or range and returns a Range object. Store the result safely, check for cancellation, and use Set when assigning it to a Range variable.
How do I detect Cancel in Application.InputBox?
Store the result in a Variant and check whether its type is Boolean and its value is False. This is important because the returned value is not simply an empty text string.
Can I accept both text and numbers?
Yes. The type values can be combined by addition; Type:=3 combines number (1) and text (2). Your code should still inspect or otherwise handle the kind of value returned.
Why does InputBox not recognize Type:=8?
The VBA InputBox function has no Type argument. Use Excel’s Application.InputBox method for typed responses and worksheet range selection.
Related
- Excel Text to Columns: Use a Line Break Delimiter
- Excel Text to Columns: Split at the Last Space
- Excel Text to Columns: Split at Line Breaks
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- How to Put a Photo in a VBA UserFormTo put a photo in an Excel VBA UserForm, add an Image control, then either set its Picture…
- VBA InputBox With Two Fields: Practical OptionsVBA’s standard InputBox accepts one response field, not two side-by-side fields. To collect two values at once, create…
- Excel VBA Log Function: Syntax, Examples, and BaseIn Excel VBA, Log(number) returns the natural logarithm of a positive number—the logarithm with base e. For a…
- VBA Excel: Compare Two Sheets for Differences Step by StepTo compare two Excel sheets with VBA, loop through the cells at matching addresses, compare their contents, and…
- Excel VBA Pivot Table Source Types ExplainedIn Excel VBA, the main PivotTable source types are xlDatabase for a worksheet range or table, xlExternal for…
- How to Activate a SteelSeries MacroTo activate a SteelSeries macro, open SteelSeries GG, select your supported device under Engine, assign a saved macro…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.