BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 17 December 2015
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
The two types of InputBox command in Excel VBA
A mea culpa confession of a VBA feature which has been missing from my life for far too long!
What I used to know
So all these years (and there have been a few), I've been teaching that you can ask your user for information like this:
Sub ThinkOfANumber()
'variable to hold a number
Dim MyNumber As Integer
'display dialog box asking user for number
MyNumber = InputBox("Enter number", _
"Number", "Type your number here")
'display the result
MsgBox "You thought of " & MyNumber
End Sub
And so you can! The above code would display an input box looking like this:
You can see that the title, prompt and default value for the input box have been set.
My new friend - Application.InputBox
So what's the point of this blog? Well, it turns out that Excel has not one, but two VBA InputBox functions:
| Function | Notes |
|---|---|
| InputBox | A generic VBA function to get information from a user. |
| Application.InputBox | An Excel-specific souped-up version of the same function. |
You can see the difference when you type them in. First the standard InputBox:
The standard input box arguments.
Compare that with the Excel version:
The biggest difference is that the Excel-specific input box has an extra Type argument.
Using Application.InputBox
Here's an example of the same code as above, but with the Type argument set to 1:
Sub ThinkOfANumber()
'variable to hold a number
Dim MyNumber As Integer
'display dialog box asking user for number
MyNumber = Application.InputBox( _
prompt:="Type in a number", _
Title:="Number box", _
Default:="Type your number here", _
Type:=1)
'display the result
MsgBox "You thought of " & MyNumber
End Sub
Suppose that you now run this macro, but don't type in a number:
This definitely isn't a number!
The Excel version of Application.InputBox will immediately detect this, and show an error:
You won't be able to proceed until you either type in a valid number or press Cancel.
Possible values for the Type argument
Here are the possible values that you can put for the Type argument:
| Type argument | What you must enter |
|---|---|
| 0 | A formula |
| 1 | A number |
| 2 | A string of text |
| 4 | A logical value (True or False) |
| 8 | A cell reference (see separate heading below) |
| 16 | An error value, such as #N/A |
| 64 | An array of values |
The most useful arguments are likely to be 1 (to force a user to input a number) or 8 (for a range).
Range input using Application.InputBox
Setting the Type argument to 8 allows a user to choose a range of cells. Here's some sample code illustrating this (note that this example assumes that you know about object variables and error trapping):
Sub GetRange()
'this will hold reference to range chosen
Dim ChosenRange As Range
'ask user for a range
On Error GoTo NothingChosen
Set ChosenRange = Application.InputBox( _
prompt:="Choose a range", _
Type:=8)
'go to range chosen by user
ChosenRange.Select
Exit Sub
NothingChosen:
MsgBox "You didn't choose anything!"
End Sub
If you run this code, you'll see a dialog box like this:
You can now select a range of cells.
You can now either type in a range or select it:
Here we've gone for 6 cells at the top left of a worksheet.
When you choose OK, Excel will check that this is a range, and if it is the rest of the procedure will select it. If you click on the Cancel button, on the other hand, the error trap will kick in and you'll see this message:
If you cancel out of the dialog box, the range variable won't be set and you'll get an error.
And there endeth my confession! For those wanting a fuller picture, there's much more about Application.InputBox in my colleague Andrew's tutorial video on the subject.