Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
528 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
One of the major advantages of the Excel input box over the generic VBA version is that you can specify the type of value you want the input box to return:
This input box has been set to accept only numeric values.
You don't need any files for this section.
Click here to download a file which contains the sample code used on this page.
Create a new workbook and insert a new module in the VBE.
You can use the Type parameter of the Excel InputBox function to control the type of values that it will return. Create a new subroutine called ReturnANumber and add code to it as shown below:
The Type parameter of the Excel InputBox function allows you to specify the type of value it returns.
To determine the return type of the input box, you need to pass a number to the Type parameter. You can see a list of the possible values for this parameter in the table below:
Number passed to Type parameter | Type returned |
---|---|
0 | A formula |
1 | A number |
2 | A string (the default) |
4 | A Boolean value |
8 | A Range object |
16 | An error value |
64 | An array of values |
As an example, you can set the return type of the input box to a number using code similar to that shown below:
We've set the type parameter to 1 and captured the return value in a variable with the Variant data type.
You could declare a variable with any numeric data type (e.g. Byte, Integer, Long etc.) but you'd need to instruct the user to enter a value that is within the range of that type.
When you run the subroutine, you can enter a number into the input box:
Enter any number.
You can use the Locals window to find out the type of value that has been returned:
The Locals window shows that the subtype of the variable is Double.
The most useful thing about this feature is that you can't enter a value of the wrong type. In the example below we're attempting to enter some text into the input box:
You can attempt to enter an invalid value, but you won't be allowed to proceed!
To practise using the Type parameter:
Sub WhatsMyType()
Dim v As Variant
v = Application.InputBox( _
Prompt:="Type any value")
End Sub
Sub WhatsMyType()
Dim v As Variant
v = Application.InputBox( _
Prompt:="Type any value")
MsgBox v & " is a " & TypeName(v)
End Sub
Enter any type of value into the input box.
You could also use the Locals window to check this information.
Sub WhatsMyType()
Dim v As Variant
v = Application.InputBox( _
Prompt:="Type any value", _
Type:=1)
MsgBox v & " is a " & TypeName(v)
End Sub
Enter any number into the input box.
This is the sort of message you should see.
You'll see this type of message when you enter a non-numeric value.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2025. All Rights Reserved.