Page 200 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 186

EXCEL DATA ANALYSIS

REQUEST USER INPUT FOR A MACRO

ou can use the InputBox function to prompt for displays a default value of Microsoft Excel. The
specific user input during the execution of a optional Default argument specifies the default value to
Y procedure. The InputBox function displays a dialog display in the text box in the dialog box.
box requesting specific input and returns the user response. You specify the display position of the dialog box using the
You capture the user response by assigning the results of optional arguments xPos and yPos. If you omit them, the
the InputBox function to a variable.
dialog box opens in the center of the screen. These
The InputBox has seven different arguments, but only the arguments use units of measurement called twips. One twip
1
1
first is required: Prompt, Title, Default, xPos, yPos, equals ⁄20 of a point or ⁄1,440 of an inch. The xPos argument
Helpfile, and Context. The Prompt argument indicates indicates the distance from the left side of the screen to the
the user prompt on the dialog box. You can make this left side of the dialog box. The yPos indicates the position
argument either a text string enclosed in quotes or a from the top of the screen to the top of the dialog box.
variable. You can combine values using the concatenation You use the final two optional arguments for adding help to
operator (&), as in this example: UR = InputBox("Sum: the dialog box. Helpfile specifies the name of the help file,
" & TSum).
and Context specifies the context ID of the help topic to
The optional Title argument contains the text that display. If you specify one argument, you must specify both.
appears on the title bar of the dialog box. If omitted, Excel
REQUEST USER INPUT FOR A MACRO

⁄ Create a new subroutine. ‹ Declare and initialize any › Type the initial VBA code. ˇ Type UserInput =
other variables for the InputBox("Text Prompt")
¤ Type Dim UserInput As subroutine. ■ This example uses a Do replacing "TextPrompt"
Variant, replacing While loop to request values with the text to display.
UserInput with the Note: See the section "Declare a from a user until the user
variable to receive a value Variable" for more information. types Done. ■ You can type & to join the
from the InputBox function. text string with a variable
value.
186