Excel VBA InputBox Types and When to Use Each

Excel VBA has two commonly used input boxes: the VBA InputBox function, which returns text, and Application.InputBox, which can require a particular kind of response. Its Type argument accepts values such as 1 for a number, 2 for text, and 8 for a selected range; other values support formulas, logical values, errors, and arrays.

The Two Excel VBA InputBox Options

The similar names can be confusing because these are separate features. The built-in VBA InputBox function is a straightforward way to ask for text. It returns a String, even when the person types digits. Your code must then decide whether that text is valid as a number, date, or other value.

Application.InputBox is a method of Excel’s Application object. Its optional Type argument tells Excel what kind of input to accept and return. That makes it useful when a macro needs a number, a cell reference, a formula, or another defined kind of value rather than unrestricted text.

For example, use InputBox for a simple name or comment. Use Application.InputBox with Type:=8 when someone should select a worksheet range. Neither option is a worksheet function: both run from VBA code, such as a macro or a user form event.

Application.InputBox Type Values

The Type argument is numeric. These are the documented return types for Application.InputBox:

0 — Formula: Returns a formula entered in the box as text. This lets code inspect or use the formula string.
1 — Number: Accepts a numeric response and returns it as a number, rather than simply returning the characters as a String.
2 — Text: Returns text. This is the useful explicit choice when you want text input through Application.InputBox.
4 — Logical value: Accepts a Boolean value, such as TRUE or FALSE.
8 — Range reference: Lets the person select a cell or range on a worksheet and returns a Range object.
16 — Error value: Accepts an Excel error value, such as #N/A, and returns an error value.
64 — Array: Accepts an array value and returns an array.

When you omit Type, the method returns text. You can also add type numbers to permit more than one kind of input. For instance, Type:=1 + 2 allows either a number or text. Use combinations only when both kinds of response make sense for the task, and make sure the rest of the macro handles each possible return type.

Choosing the Right Type

Start with the value your macro actually needs, not with the most restrictive option available. A quantity, a percentage, or a row number usually calls for type 1. A label, filename, or short note is text, so use the regular InputBox function or type 2. A cell selection calls for type 8. Requiring an appropriate response can prevent some invalid entries, but it does not replace checks in your code.

Type 0 is useful when the user needs to enter a formula that your macro will later place in a cell or otherwise process as formula text. Do not mistake it for a general-purpose calculation box: the returned value is a formula string, and your code is responsible for deciding where and how to use it.

Type 4 is appropriate when a True/False choice is genuinely convenient. For a clearer interface, especially when the choice has consequences, a message box with explicit Yes and No buttons or a UserForm may be easier to understand. Type 16 and type 64 are more specialized. Most routine data-entry macros do not need to accept error values or array constants directly.

Basic Text and Number Examples

The built-in function is convenient for a text response, but Cancel and an intentionally blank answer both produce a zero-length string. The following example treats either case as no usable name. It also shows the complete function syntax with optional title and default text:

Sub AskForName()
    Dim personName As String

    ' Ask for text; Cancel and a blank response both return an empty string.
    personName = InputBox("Enter a name:", "Name", "")

    If Len(personName) = 0 Then Exit Sub

    MsgBox "Hello, " & personName
End Sub

Because this function returns a String, a response such as 12 is still text until your code converts or validates it. For numeric input, Application.InputBox with type 1 is more direct. The result is stored as a Variant here so the code can also recognize Cancel:

Sub AskForQuantity()
    Dim answer As Variant

    ' Type 1 requests a number; Cancel returns False.
    answer = Application.InputBox(Prompt:="Enter a quantity:", _
                                  Title:="Quantity", Type:=1)

    If VarType(answer) = vbBoolean Then
        If answer = False Then Exit Sub
    End If

    MsgBox "Quantity entered: " & CStr(answer)
End Sub

For business rules, add checks after the response is received. A prompt that requests a positive whole number should not silently accept a negative value or a decimal simply because Excel accepted a number. Check the range and integer requirement in VBA before using the result.

Getting a Worksheet Range

Type 8 is a distinctive case: it returns a Range object rather than ordinary text or a numeric value. Use Set when assigning the returned object to a Range variable. Since Cancel returns False, receiving the result into a Variant first makes it possible to distinguish cancellation before assigning the object:

Sub AskForRange()
    Dim answer As Variant
    Dim selectedRange As Range

    ' Type 8 asks the user to select a worksheet range.
    answer = Application.InputBox(Prompt:="Select cells:", _
                                  Title:="Choose a range", Type:=8)

    If VarType(answer) = vbBoolean Then
        If answer = False Then Exit Sub
    End If

    Set selectedRange = answer
    MsgBox "Selected " & selectedRange.Address
End Sub

A range selection is not the same as typing an address into a text box: Excel provides a selection interface, and the returned object refers to cells. Code should still check that the chosen range is suitable. For example, a macro expecting a single cell should test whether the selection contains more than one cell before changing data.

Cancel, Blank Responses, and Return Types

Cancellation is a frequent source of bugs because the two input options behave differently. The VBA InputBox function returns an empty string when the user clicks Cancel. It also returns an empty string when the user submits a blank response, so that result alone cannot tell those actions apart.

Application.InputBox returns Boolean False when the user clicks Cancel. For other types, the returned value may be a number, a String, an error value, an array, or an object. Declare the receiving variable as a Variant when the return type can vary or when you need to test for cancellation before using the value. Do not assume every result is text.

For a numeric prompt, checking VarType before using the result is a practical way to detect the Boolean Cancel result. For a range prompt, first receive the result in a Variant, check for cancellation, and only then use Set to assign it to a Range variable. Also consider what your macro should do if the user submits an empty response or chooses an unsuitable range.

Common Problems and Safer Macro Design

Using the wrong input box: The standard InputBox function does not have an argument that enforces a number or range. Use Application.InputBox when you need its Type options.
Forgetting the return type: A number received through the basic function is text. Convert it deliberately and handle invalid input instead of assuming the contents are numeric.
Treating Cancel as an empty response: The standard function makes Cancel and blank indistinguishable. The Application method returns False on Cancel, so test for that before processing.
Assigning a selected range like a value: A Range is an object. Declare a Range variable and use Set after handling the Cancel result.
Trusting the prompt as validation: A clear prompt helps, but the macro should still enforce business rules such as allowed limits, required fields, or a maximum selection size.

Before running a macro that writes to cells or changes workbook structure, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is a useful safeguard while developing and using code. Excel for the web does not run VBA macros; use desktop Excel. On Windows, open the VBA editor with Alt+F11 (some keyboards require Fn as well). On Mac, VBA is available in desktop Excel, but keyboard shortcuts and some Excel features differ; use the Visual Basic editor command available in that Excel version if the Windows shortcut does not apply.

InputBox Compared with Other Interface Choices

An input box is quick to add and works well for a short, one-time request. It is less suitable for a process that needs several fields, detailed instructions, drop-down choices, or a preview of the effect of a choice. Repeated prompts can also make a macro tedious because the user must respond to each box in sequence.

For a simple confirmation, a VBA MsgBox can provide buttons such as Yes and No and return which button was selected. For a richer, reusable data-entry screen, a VBA UserForm can combine labels, text boxes, lists, and buttons. A worksheet-based form may be more appropriate when people need to enter or review data without running a macro. These alternatives take more setup, but they can make choices clearer and give your code more control over validation.

Use the smallest interface that fits the task. A single number or range is a good InputBox use. A multi-step workflow with many required fields is usually a sign to consider another interface rather than stacking a long series of prompts.

Frequently Asked Questions

What is the difference between InputBox and Application.InputBox in VBA?
The VBA InputBox function returns text and has no type-enforcement argument. Application.InputBox has a Type argument that can request numbers, formulas, ranges, and other kinds of response.

Which Application.InputBox type selects cells?
Use Type:=8. It returns a Range object, so assign the result to a Range variable with Set, after checking whether the user clicked Cancel.

What does Application.InputBox return when I click Cancel?
It returns Boolean False. Store the result in a Variant and test for that result before treating it as the requested number, text, or range.

Can InputBox tell whether the user clicked Cancel or left the answer blank?
The standard VBA InputBox function returns an empty string for both actions, so the result alone does not distinguish them. Application.InputBox returns False on Cancel, which allows cancellation to be checked separately.

Can I accept both numbers and text?
Yes. Add the relevant type values; for example, Type:=1 + 2 allows a number or text. Store the result in a Variant and handle each allowed kind appropriately.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.