InputBox

VBA allows you to equate a variable to an object, but the variable does not automatically become an object. If you then attempt to use the variable in an expression that requires an object, youll get an "Object required" error message. The Set command lets you define a variable or property as an object. The following example makes the InputBox method return an Object (so that you can use its Address property in addition to its Value property, for example) ... 

VBA provides two built-in dialog boxes for display of messages or for input, MsgBox and InputBox. 

The InputBox allows you to pause a macro and request input from the user. There are both an InputBox Function and an InputBox method. 

InputBox(prompt text, title text, default, x position, y position, helpfile, context)... 

If the user presses the OK button or the RETURN key, the InputBox function returns as a value whatever is in the text box. If the Cancel button is pressed, the function returns a null string. The following example produces the input box shown in Figure 14-17. 

The differences between the InputBox function and the InputBox method are the following (i) default can be any data type and (ii) the additional argument type num specifies the data type of the return value. The values of type num and the corresponding data types are listed in Table 14-7. Values of type num can be added together. For example, to specify an input dialog box that would accept number or string values as input, use tiie value 1 + 2 = 3 for type num. 

To specify the range of cells to use as data labels, we ll use the InputBox method. We ll define the range as an object variable, so that we can use a For...Each...Next loop. For a code example, see "You Can Define Your Own Objects" in Chapter 14. To convert SeriesCollection(l) into the general case, remember that a specific item in a collection can be referred to by its index number or by its name. We ll use the code... 

See also inputBox Function, MsgBox insert Method... 

As our next example we use the input box to specify a single address, then verify that the address was read correctly by displaying it in a message box. When an input box is used to read either a single address or an address range of a multi-cell array (a block), preface the InputBox instruction with the command Set, and use Application, before InputBox. Then use that address to select the range, as in... 

XX = Application.InputBox(Prompt ="Interpolate for X =", Type =l) Find the index NClosest of the point that is closest to XX Delta = XValue (2, 1) - XValue(l, 1)... 

Dim Message As String, Title as String, Default As String Set myXRange = Application.InputBox ... 

Dim XiValue As Variant, newXiValue As Variant, SFiValue As Variant Set myRangel = Application.InputBox(prompt ="The input parameters of" Chr(13) "the function are located in ", ... 

Title ="Propagation InputBox 1 Input Parameters", Type =8) myRangel.Select NR = Selection.Rows.Count NC = Selection.Columns.Count If NR = 0 Then End If NR <> 1 And NC <> 1 Then... 

Set myRange2 = Application. InputBox (prompt = "The standard deviations of " "the" Chr(13) "input parameters are located in ", ... 

Title = "Propagation InputBox 2 Standard Deviations", Type =8) myRange2.Select NRS = Selection.Rows.Count NCS = Selection.Columns.Count If NRS = 0 Then End... 

The parameters determined by Solver are located in ", Title ="SolverAid InputBox 1 Solver parameters". Type =8) myRangel.Select Dim rP As Integer... 

The sum of squares of the residuals is located in ". Title = "SolverAid InputBox 2 SRR", Type =8) myRange2.Select... 

---