Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

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.

Testing the Code

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:

Using the same module, create a subroutine called WhatsMyType.
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

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

Run the subroutine and enter any value into the input box:

Enter any type of value into the input box.

Check that you return a String, regardless of the value you have entered:

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

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

Run the subroutine and enter a number:

Enter any number into the input box.

Check that the data type of the return value is Double:

This is the sort of message you should see.

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:

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

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Setting the Return Type of an Input Box

Testing the Code

Head office

London

Manchester