Function

Yp ou can use the InputBox function to prompt for specific user input during the execution of a procedure. The InputBox function displays a dialog box requesting specific input and returns the user response. You capture the user response by assigning the results of the InputBox function to a variable.

The InputBox has seven different arguments, but only the first is required: Prompt, Title, Default, xPos, yPos, Helpfile, and Context.

The Prompt argument indicates the user prompt on the dialog box. You can make this argument either a text string enclosed in quotes, or a variable. You can combine values using the concatenation operator (&), as in this example,

The optional Title argument contains the text that displays on the title bar of the dialog box. If omitted, Excel displays a default value of Microsoft Excel. The optional Default argument specifies the default value to display in the text box on the dialog box.

You specify the display position of the dialog box using the optional arguments xPos and yPos. If you omit them, the dialog box displays in the center of the screen. These arguments use units of measurement called twips. One twip equals V20 of a point or 1/i,440 of an inch. The xPos argument indicates the distance from the left side of the screen to the left side of the dialog box. The yPos indicates the position from the top of the screen to the top of the dialog box.

You use the final two optional arguments for adding help capability to the dialog box. The Helpfile argument specifies the name of the help file and the Context argument specifies the context ID of the help topic to display. If you specify one argument, you must specify both.

USING THE INPUTBOX FUNCTION

USING THE INPUTBOX FUNCTION

Macro Functionalism

□ Create a new subroutine.

< Declare and initialize any other variables for the subroutine.

_Q Type the initial VBA code.

Q Type UserInput = InputBox("Text Prompt")

replacing "TextPrompt" with the text to display on the Input Box.

□ Create a new subroutine.

B Type Dim UserInput As Variant, replacing Userlnput with the variable to receive value from the InputBox function.

< Declare and initialize any other variables for the subroutine.

_Q Type the initial VBA code.

■ The example uses a Do While loop to request values from a user until Done is typed.

Q Type UserInput = InputBox("Text Prompt")

replacing "TextPrompt" with the text to display on the Input Box.

You can use named arguments to simplify your function calls. When you work with built-in VBA functions, you see that many of the functions have optional arguments. For example, although the InputBox has seven different arguments, only the first one is required. If you want to include any additional arguments, you need to specify the argument values in order, leaving a placeholder for any you do not want to use.

Example:

UserInput = InputBox("Type a value", , "test")

Instead of specifying a placeholder for each value, VBA enables you to use named arguments with the built-in procedures. With a named argument, you specify the name of the argument along with the corresponding value. To specify a named argument, you type the name of the argument followed by a colon, an equals sign, and the value of that particular argument. You can place named arguments in any order, and you do not have to specify a value for every argument.

Example:

UserInput = InputBox(prompt:="Type a value.", default:="5" )

0 Type additional code to process the value returned from the InputBox function.

□ Switch to Excel and run the associated macro.

■ The InputBox function requests specific input from the user.

0 Type additional code to process the value returned from the InputBox function.

□ Switch to Excel and run the associated macro.

■ The InputBox function requests specific input from the user.

0 0

Post a comment