The Input Box in Excel VBA

By Darrin Koltow

Learn about the Input Box function in Excel VBA as a preliminary step to creating programs with this input tool. This function provides a gentle introduction to learning how to program VBA programs by hand because it only takes one argument, yet performs the vital function of receiving user input. Consider learning the MsgBox function along with Input Box, because its role, displaying output for users to see, is the counterpart of the Input Box function.

Purpose

The Input Box function prompts a user for input and receives that input. For example, an Input Box may prompt a user to enter the age of a person, which your program can store in a table called "Birthday mailing list" on an Excel spreadsheet.

Arguments

Input Box takes several arguments, but you only need to supply it with one -- the text prompting the user to enter data. Optional arguments include "Title," which appears in the Input Box's title bar. The "Default" argument is the value Input Box will return to your program if the user presses "Enter" without entering a value. You can position the Input Box at a specific screen location by specifying values for the "Xpos" and "Ypos" arguments.

Return Value

Input Box returns a string value to your program, which usually means you have to declare a variable to receive the function's return value. That declaration can take one of two forms. The first doesn't indicate a specific type of value. For example, you could write "Dim returnValue." Or you could indicate the string variable type with a declaration such as "Dim returnValue as string." In general, use the more specific typing so VBA can catch type-related errors, as when your program tries to assign a number to a string variable.

Sample Program

Try a sample program that integrates Input Box with Excel. Click the "Visual Basic" button in Excel's Developer tab to open the VBA programming environment. Copy the following code: Public Sub getEmailAdr()Dim str_email As StringDo While (InStr(str_email, "@") = 0)str_email = InputBox("Enter a valid email address.")LoopRange("A1") = str_emailEnd SubPaste the code in the environment's window, then run the program by pressing "F5." This program prompts the user for input, performs a simple error check, then installs the input value in a spreadsheet cell if the input value passes the error check. If it doesn't, the program will continue asking the user for the correct input.

Alternatives and Advantages

Input Box is only one of several ways to get user input in Excel. You can instead use a spreadsheet cell or a Textbox control to get input. Since Input Box accepts text input, it's not the best choice for responses to yes/no type questions. However, one advantage of the Input Box function is that you don't have to create a special form to use it, in contrast to VBA's other controls. A List Box control, for example, requires either a userform, which you must create, or a spreadsheet, where the control will take up screen space. Input Box takes up no space. It appears, receives user input, then disappears, making it very unobtrusive.