Module 5 - Messages and User Inputs
Lesson 5.4 - The Excel Input Box
Topic 5.4.2 - Setting the Return Type

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:

Numeric input box

This input box has been set to accept only numeric values.

 

Files Needed

You don't need any files for this section.

Completed Code

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.

Setting the Return Type of an Input Box

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:

Type parameter

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:

Set number

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.

Testing the Code

When you run the subroutine, you can enter a number into the input box:

Enter number

Enter any number.

 

You can use the Locals window to find out the type of value that has been returned:

Return type

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:

Validation

You can attempt to enter an invalid value, but you won't be allowed to proceed!

 

To practise using the Type parameter:

  1. Using the same module, create a subroutine called WhatsMyType.
  2. Declare a Variant variable and use an Excel input box to assign a value to it:

Sub WhatsMyType()

 

Dim v As Variant

 

v = Application.InputBox( _

Prompt:="Type any value")

 

End Sub

  1. Add code to display a message box which shows the value and its data type:

Sub WhatsMyType()

 

Dim v As Variant

 

v = Application.InputBox( _

Prompt:="Type any value")

 

MsgBox v & " is a " & TypeName(v)

 

End Sub

  1. Run the subroutine and enter any value into the input box:
Enter number

Enter any type of value into the input box.

 
  1. Check that you return a String, regardless of the value you have entered:
Return string

You could also use the Locals window to check this information.

 
  1. Edit the code and set the Type of the input box to 1:

Sub WhatsMyType()

 

Dim v As Variant

 

v = Application.InputBox( _

Prompt:="Type any value", _

Type:=1)

 

MsgBox v & " is a " & TypeName(v)

 

End Sub

  1. Run the subroutine and enter a number:
Enter a number

Enter any number into the input box.

 
  1. Check that the data type of the return value is Double:
Return double

This is the sort of message you should see.

 
  1. Run the subroutine again and make sure you see a sensible message if you enter a value which can't be evaluated as a number:
Invalid number

You'll see this type of message when you enter a non-numeric value.

 
  1. Save and close the workbook.
This page has 0 threads Add post