Home>Blogs>VBA>VBA.InputBox vs Application.InputBox in Excel VBA (7 Types)
VBA.InputBox vs Application.InputBox in Excel VBA (7 Types)
VBA

VBA.InputBox vs Application.InputBox in Excel VBA (7 Types)

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.

VBA.InputBox vs Application.InputBox: the VBA Input Box dialog asking to input the value here
The plain VBA.InputBox: a prompt, a title and one text box. Whatever you type comes back as text.

⬇️  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 Type argument 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.

Application.InputBox Type argument values 0, 1, 2, 4, 8, 16 and 64 with their meanings
The seven values you can pass to the Type argument of Application.InputBox.

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 Sub

The 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.

Application.InputBox dialog in Excel VBA with prompt, text box, OK and Cancel buttons
Application.InputBox looks like an Excel dialog, and its Type argument controls what it accepts.

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 Sub

Type 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 Sub

This 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 Sub

Type 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 Sub

Enter 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 Sub

Two things change compared with the other macros:

  • The variable is declared As Range instead 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 Sub

How 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, so Select Case can compare the input with each error type in turn.
  • When a case matches, myinput is replaced with a friendly label and shown in the message box.
  • Case Else catches 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 Sub

Type 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 Sub

The 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

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.

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com