VBA.InputBox vs Application.InputBox is one of the first choices you make when a macro needs to ask the user for something at run time. Both show a small dialog with a prompt, a title and a text box, but they behave very differently. VBA.InputBox simply returns whatever the user types as text. Application.InputBox adds a Type argument that validates the entry for you and can even let the user pick a range with the mouse. In this tutorial you will run both side by side and test all seven Application.InputBox types with ready-made macros.

⬇️ Download the InputBox Practice Workbook
.zip containing a macro-enabled .xlsm · 8 ready-to-run macros · enable macros after opening
Watch the Video Tutorial
In the video PK runs every macro from this workbook live: the plain VBA.InputBox with and without a default value, then Application.InputBox with types 0, 1, 2, 4, 8, 16 and 64, including what happens when you type the wrong kind of value.
What You Will Learn
- How VBA.InputBox works – prompt, title and an optional default value.
- What Application.InputBox adds – the
Typeargument and built-in validation. - All seven types – formula (0), number (1), text (2), True/False (4), range (8), error value (16) and array (64).
- How to let the user select a range with the mouse and use it in your code.
- How to handle Cancel safely so your macro does not crash.
The Practice Workbook
The download has one sheet and one module. On Sheet1 the table in B8:C15 lists every type number with its meaning, and cells E11:E13 hold three deliberate errors for testing type 16: =ds gives #NAME?, =0/0 gives #DIV/0! and =NA() gives #N/A. Module1 contains eight macros: VBA_Inputbox and Inputbox_Type_0 to Inputbox_Type_64.

To open the code, go to Developer → Visual Basic or press Alt+F11. To run a macro, go to Developer → Macros, pick it and click Run.
Part 1 – VBA.InputBox
Step 1: The basic macro
This is the first macro in the workbook:
Sub VBA_Inputbox()
Dim myinput As Variant
myinput = VBA.InputBox("Input the value here", "VBA Input Box")
MsgBox myinput
End SubThe first argument is the prompt (the message inside the box) and the second is the title of the dialog. Run it, type PK, click OK and the message box shows PK. Type test and it shows test.
VBA.InputBox and plain InputBox are the same function. Writing the VBA. prefix just makes it clear you mean the VBA function and not the Excel one.
Step 2: Add a default value
The optional third argument pre-fills the text box. In the video PK adds Test as the default:
myinput = VBA.InputBox("Input the value here", "VBA Input Box", "Test")Now the box opens with Test already typed. The user can accept it or overwrite it.
What VBA.InputBox cannot do
VBA.InputBox has no idea what kind of value you want. It always returns a String, so a number comes back as text, and there is no way to let the user click on a range. If you need a number, you must check it yourself, for example with IsNumeric. That is exactly the gap Application.InputBox fills.
Part 2 – Application.InputBox and Its 7 Types
Application.InputBox is Excel’s own input box. You call it the same way, but you can add Type:= to say what kind of input you accept. If the user enters something else, Excel shows its own warning and keeps the box open, so you get validation without writing any extra code.

Type 0: a formula
Sub Inputbox_Type_0()
Dim myinput As Variant
myinput = Application.InputBox("Input the formula", "Input box type 0", Type:=0)
MsgBox myinput
End SubType 0 returns the entry as a formula string. In the video PK types A1+B1 and the message box shows ="A1+B1": Excel wraps the entry as a formula.
Type 1: a number
Sub Inputbox_Type_1()
Dim myinput As Variant
myinput = Application.InputBox("Input the number", "Application Inputbox", Type:=1)
MsgBox myinput
End SubThis is the type you will use most. Type abc and Excel replies Number is not valid. and leaves the box open. Type 23 and the macro receives 23. Because the check is built in, you do not need to write your own validation.
Type 2: text
Sub Inputbox_Type_2()
Dim myinput As Variant
myinput = Application.InputBox("Input the string", "Input box type 2", Type:=2)
MsgBox myinput
End SubType 2 accepts any text, and numbers too, so it behaves much like VBA.InputBox.
Type 4: True or False
Sub Inputbox_Type_4()
Dim myinput As Variant
myinput = Application.InputBox("Input the True or False", "Input box type 4", Type:=4)
MsgBox myinput
End SubEnter abc and Excel says the logical value is not valid. TRUE and FALSE are accepted, and in the video 1 and 0 are accepted too.
Type 8: a cell reference (Range)
This is the type that makes Application.InputBox special. The user can click and drag on the sheet while the box is open, and you get a real Range object back:
Sub Inputbox_Type_8()
Dim myinput As Range
Set myinput = Application.InputBox("Select the range", "Input box type 8", Type:=8)
MsgBox myinput.Address
End SubTwo things change compared with the other macros:
- The variable is declared
As Rangeinstead of Variant. - You must use
Set, because you are assigning an object, not a value.
Run it and select C9:C15 and the message box shows $C$9:$C$15. Typing a plain value is rejected because it is not a valid reference. Once you have the range you can do anything with it. In the video PK counts the selected cells with one extra line:
MsgBox Application.WorksheetFunction.CountA(myinput)Selecting the seven cells C9:C15 returns 7. VBA.InputBox simply cannot do this.
Type 16: an error value
Sub Inputbox_Type_16()
Dim myinput As Variant
myinput = Application.InputBox("Please input the Error", "Input box type 16", Type:=16)
Select Case myinput
Case CVErr(xlErrNA)
myinput = "#N/A error"
Case CVErr(xlErrDiv0)
myinput = "#DIV/0! error"
Case CVErr(xlErrNull)
myinput = "#Null!"
Case CVErr(xlErrName)
myinput = "#Name? error"
Case CVErr(xlErrNum)
myinput = "#Num! error"
Case CVErr(xlErrRef)
myinput = "#Ref! error"
Case Else
myinput = "This is not an error"
End Select
MsgBox myinput
End SubHow the error code works
- Type 16 accepts an error value such as #N/A. You can type it or click a cell that shows an error, like E11:E13 in the workbook.
CVErr(xlErrNA)builds the #N/A error value in VBA, soSelect Casecan compare the input with each error type in turn.- When a case matches,
myinputis replaced with a friendly label and shown in the message box. Case Elsecatches everything else. In the video, selecting a normal cell returns This is not an error.
Type 64: an array of values
Sub Inputbox_Type_64()
Dim myinput As Variant
myinput = Application.InputBox("Please input the array like - {1,2,3}", "Input box type 64", Type:=64)
Dim str As String
Dim i As Integer
For i = LBound(myinput) To UBound(myinput)
str = str & myinput(i) & vbLf
Next i
MsgBox str
End SubType the values inside curly brackets, for example {1,2,3,4,5}. The loop runs from the first to the last item of the array (LBound to UBound) and joins each value with vbLf, a line break, so the message box shows one value per line. For text items, put each one in double quotes, as PK does in the video with PK, An, Excel and Expert inside the brackets.
VBA.InputBox vs Application.InputBox at a Glance
- Returns: VBA.InputBox always returns a String. Application.InputBox returns the type you asked for: number, text, logical, Range, error or array.
- Validation: none in VBA.InputBox; built into Application.InputBox through
Type. - Range selection: only Application.InputBox with
Type:=8. - Works outside Excel: VBA.InputBox also works in Word, Access and other Office apps. Application.InputBox belongs to Excel.
As PK says at the end of the video, the right one depends on your requirement, but Application.InputBox is the more powerful choice because it can take a range selection straight from the sheet.
Common Errors and Fixes
This section and the Tips below are additions from general VBA practice; they are not shown in the video.
Run-time error 424 when the user clicks Cancel on Type 8
On Cancel, Application.InputBox returns False, and Set myinput = False fails with Object required. Wrap the line with error handling and check for Nothing:
On Error Resume Next
Set myinput = Application.InputBox("Select the range", "Input box type 8", Type:=8)
On Error GoTo 0
If myinput Is Nothing Then Exit SubThe message box shows False
The user clicked Cancel on an Application.InputBox. Test for it before using the value, for example If VarType(myinput) = vbBoolean Then Exit Sub for a type 1 or 2 box.
Type mismatch when the result goes into a number variable
VBA.InputBox returns text, and an empty string on Cancel. Either switch to Application.InputBox(..., Type:=1) or check the text with IsNumeric before converting it.
Tips
- Types can be added together.
Type:=1 + 2(that is, 3) accepts either a number or text. - Always use named arguments such as
Type:=8. Application.InputBox has more optional arguments (Default, Left, Top, HelpFile, HelpContextID) before Type, so named arguments keep the call readable. - Give the box a clear title and prompt. Users read the prompt, not your code.
- Pre-fill a sensible default so the user can simply press Enter.
- Microsoft documents every argument in the Application.InputBox method reference.
Want It Ready-Made?
If you would rather use finished macro tools than write them, the Excel VBA Tools & Utilities Pack on NextGenTemplates.com bundles eight premium VBA templates, and you can browse every Excel VBA tool in the store.
Related Tutorials
- Resize and Relocate a Chart Using an Excel Range (Application.InputBox Type 8 in action)
- Learn Excel VBA Step by Step: PK’s VBA Course
- Lock Excel Cell After Updating: Allow Data Entry Only Once (VBA)
- Create a Data Entry Form in Excel VBA with ChatGPT
- Make Selected Shapes, Charts and Pictures the Same Size with One Click
Frequently Asked Questions
What is the difference between VBA.InputBox and Application.InputBox?
VBA.InputBox always returns text and does no checking. Application.InputBox is the Excel version: its Type argument tells Excel what to accept, Excel rejects wrong entries for you, and Type 8 lets the user select a range with the mouse.
Is InputBox the same as VBA.InputBox?
Yes. Writing InputBox on its own calls the VBA function. Adding the VBA prefix only makes the code clearer.
How do I let the user select a range in an input box?
Use Application.InputBox with Type 8, declare the variable As Range and assign it with Set. Step Type 8 above shows the full macro, which returns the address of the selected cells.
How do I accept only numbers in an input box?
Use Application.InputBox with Type 1. If the user types text, Excel shows Number is not valid and keeps the box open until a number is entered.
What does Application.InputBox return when I click Cancel?
It returns False. With Type 8 that causes an Object required error, so trap it with On Error Resume Next and check whether the range is Nothing, as shown in Common Errors and Fixes.
Can I enter several values at once?
Yes. Type 64 accepts an array typed inside curly brackets, such as 1,2,3 in braces, and your code can loop through it with LBound and UBound.
About the Author
PK is a Microsoft Excel expert and trainer and the founder of PK: An Excel Expert and NextGenTemplates.com. He has been teaching Excel, VBA, Power Query and Power BI since 2016 on his YouTube channel.
Conclusion
The VBA.InputBox vs Application.InputBox choice comes down to control. Use VBA.InputBox for a quick free-text answer. Use Application.InputBox whenever the input must be a number, a True/False value, an error, an array or, best of all, a range the user selects on the sheet. Download the workbook, run the eight macros and try typing the wrong values to see the built-in validation at work.



