Chapter 14 Input boxes

14.1 Basic input boxes

We have seen in lectures that Input Boxes are a useful tool for collecting user inputs. Here we will consider in more detail some of their features, and how to avoid errors if the user inputs an unexpected piece of data.

First, it is worth noting that Input Boxes are very commonly used with only the prompt given as one of the arguments. This defaults to producing an Input Box with no default entry (and a generic title) which is often sufficient if we are just looking for a simple piece of data.

We saw in lectures that we need to be able to deal with the possibility that nothing is entered by the user. We used the Len command to detect this, and GoTo statements to make the Input Box repeat. We also mentioned how to detect a different kind of error, where the user does not enter the right kind of data, but did not consider this in much detail.

Recall that anything entered into an Input Box is regarded as a string when we refer to it in our VBA code. Often we will want to interpret it as a number or a date, or some other kind of data. If we try to convert it into the appropriate type immediately and it is not of the right type then this will produce an error. Instead we have a number of commands which we can use to test whether the data is of the appropriate form.

The function IsNumeric takes a variable as argument and returns True if the variable is of one of the various numerical types, or if it can be interpreted as a number. Otherwise it returns False. There are similar functions called IsArray (which checks whether the variable can be interpreted as an array) and IsDate (which checks whether the variable can be interpreted as a date). We have already used the similar IsError function earlier in the course.

Once we know our variable is of the desired type, the next step is to convert it from a string to the form we wish to work with it as. If we do not do this then VBA will complain and give an error. There are many conversion functions, corresponding to most of the different data types which we have seen:

Conversion function	Data type converted to
CBool	Boolean
CDate	Date
CDbl	Double
CInt	Integer
CLng	Long
CSng	Single
CStr	String

In lectures we saw an example of the use of CInt; the other functions are similar.

14.2 Advanced input boxes: the Application.InputBox method

For most applications, basic Input Boxes are fine (although it is slightly tiresome to have to convert the string into other data types). But they do not allow us to easily collect certain kinds of data from the user, such as a Range of cells. For this reason we have an alternative method, using the Application.InputBox method, which allows us to tell VBA what kind of input we are expecting.

The syntax for Application.InputBox is similar to that for the ordinary InputBox, but with extra options:

InputBox(Prompt[,Title][ ,Default][ ,Left][ ,Top][ ,HelpFile][ ,HelpContextId][ ,Type])

We have seen all of these before except Left and Top (which are just like xpos and ypos and will be ignored in this module) and the final option Type.

The option Type is an integer (or sum of integers) which indicates what type or types of input are allowed:

Type	Allowed input types
0	Formula
1	Number
2	String
4	Boolean
8	Range
16	Error value
64	Array

If we want to allow more than one data type we set Type to be the sum of the corresponding values, so type 3 would allow a number or a string.

There are two features which are useful to note when using Application.InputBox. First, it is rather inconvenient that the type option occurs at the end of a long list of options which we will hardly ever use. If we try to enter commands in the usual way we will end up with statements like

x=Application.InputBox("Enter a number", , , , , , , 1)

which is irritating to type, hard to read, and likely to be entered incorrectly. This is thus a good time to introduce a second way to refer to arguments in a function.

So far when we have used functions we have given the arguments by entering them in the specified order. For example, the Prompt was always the first argument in a MsgBox. This method uses what are called positional arguments. There is a second way of specifying arguments, using what are called named arguments.

You will have noticed that each argument in our functions has had a name (such as Prompt, or Title). We can enter the arguments in a function in any order we like if we use the syntax ArgumentName:=value

Thus for example we can specify an InputBox with prompt "Enter number" and allowed type number by entering

Application.InputBox(Prompt:="Enter number", Type:=1)

This works for all functions with arguments, and is well worth remembering if you only need to use a few arguments in a function with many optional arguments available.

The second feature of Application.InputBox is a more serious issue. If the user does not enter the right kind of data (or presses Cancel) then this method will generate an error straight away, so we are not able to use our earlier method of detecting and preventing the wrong data type being processed.

To avoid this we can use a couple of commands which we do not want to explain in detail. For the purposes of this module they can be regarded as fixed commands which make VBA handle errors sensibly. If you wish to know more about how these commands work, you will need to consult a more advanced reference on VBA.

Before we use our Application.InputBox command we will enter the lines

On Error Resume Next

and

Application.DisplayAlerts = False

In essence these “turn off” the error checking function in VBA. Once we have used the command Application.InputBox we enter the lines

On Error GoTo 0

and

Application.DisplayAlerts = True

which “switch on” the error checking function again.

This is all best understood with an example. Consider the following simple macro.

Sub MarkEntry()
Dim entry As Double
EnterScore:
On Error Resume Next
Application.DisplayAlerts = False
    entry = Application.InputBox(Prompt:="Enter your score", Type:=1)
On Error GoTo 0
Application.DisplayAlerts = True
If entry = 0 Then
    MsgBox("Please enter a mark")
    GoTo EnterScore
End If
output = MsgBox(Prompt:="You scored " & entry)
End Sub

This asks the user to input a mark, and if they do it displays a message box stating their score. However, if they enter something which is not a number, then the Input Box reappears with their input highlighted. If they enter nothing and press Cancel or Escape, then it displays a Message Box asking them to enter a mark and re-displays the Input Box.

Note that we have used the four special lines described above before and after the Input command, and have used a GoTo command to deal with the case of the empty entry.

Our next example is very similar, and illustrates how we can ask the user for a range of cells. Consider the following macro.

Sub RangeEntry()
Dim entry As Range
EnterRange:
On Error Resume Next
Application.DisplayAlerts = False
    Set entry = Application.InputBox(Prompt:="Enter a range with your mouse", Type:=8)
On Error GoTo 0
Application.DisplayAlerts = True
If entry Is Nothing Then
    MsgBox("Please enter a range")
    GoTo EnterRange
End If
' rest of code does something to that range
output = MsgBox("Success")
End Sub

This asks the user to enter a range, and if they do then it does something (which we have not included here) and prints Success. If they do not enter a range then it returns to the Input Box, and if they press Cancel or Escape it displays a message and returns to the Input Box. To enter a range the user can either use the mouse to highlight some cells, or enter a range directly in the Input Box.

There are some small but important differences compared to the previous example. Obviously, entry is now of type Range. Unfortunately we cannot just write entry=.... in such circumstances, but must instead write Set entry=..... Also, to test whether the entry is empty we use the line

If entry Is Nothing Then

which is a little different from our earlier examples.

We will not make much use of the ability to ask for ranges in this module, so you can if you wish ignore the use of Application.InputBox to select a range. But it is useful to know that such an operation is possible.