u.a. folgendesWhat's the difference between the InputBox Function and Method?
Keywords: Input Range Input Box User Input
Posted May 16, 1996
The InputBox function is provided by VBA for getting text from a user. It always returns text. The InputBox method is provided by Excel and can return text, numbers, arrays and/or references-in other words, the Excel-specific data types. Here's an example:
Sub TestInputBoxes()
''' Test InputBox Function
MsgBox InputBox(prompt:="Type a short sentence below.", _
Title:="What you type below will display in Message Box")
''' Test InputBox Method (restricts input to a number)
MsgBox Application.InputBox(prompt:="This will only accept a number.", _
Title:="Testing InputBox method", Type:=1)
End Sub
The InputBox Method requires the Application object (because it comes from the application Excel rather than VBA).
The Type argument can limit the input from your user to a specific type. For example, a type 8 allows you to drag a range and get a range object for your routines. See the on-line help for additional types and other optional arguments.
EXCEL SPECIFIC INPUT BOX
You're probably familiar with VBA's InputBox statement, as it is available to all Office applications. The Excel library offers its own version of this statement, Application.InputBox. Use the Excel-specific version when inputting formulas and ranges.
This statement takes the form:
Application.InputBox(prompt, [title], [default], [left], [top], [helpfile], [helpcontextID], [type])
Only the first argument, Prompt, is required. The type argument specifies whether the input box expects a formula (0), number (0), range (8), or matrix of cells (64). For example, the following statement would display an input box that accepts only a valid range address:
Application.InputBox ("Please enter the range address",Type:=8)
by Susan Sales Harkins
UserVal = Application.InputBox("Value?", , , , , , , 1)
If UserVal <> False Then Range("A1") = UserVal
VBA has an InputBox function, and there's also an InputBox method for the Application object. Are these the same?
No. Excel's InputBox method is more versatile since it allows validation of the user's entry. The preceding example uses 1 (which represents a numeric value) for the last argument of the InputBox method. This ensures that the user enters a value into the input box.
InputBox
The InputBox function creates and displays a simple dialog box containing a prompt, an edit box, and OK and Cancel buttons. If you require a more elaborate dialog box, you must create a custom dialog box using a dialog sheet (see later). The return value from the InputBox function is the string entered by the user. If the input box is empty, or if the user pressed Cancel the return value is an empty string. The following displays a simple input box:
radius=InputBox("Enter the circle radius:", "Circle Radius")
The InputBox method of the Application object is similar but allows you to specify the desired data type for the data entry (a range, or a string for example). If the user enters data with an incorrect type, Excel displays a message indicating this.
If a data type is specified, the return value from the InputBox method has that data type if the user pressed Enter or OK. If the data type is not specified the return value is a string. In either case, the return value is False if the user pressed Cancel or Esc to cancel the dialog box. The full syntax includes the facility to specify the screen location of the input box and context-sensitive help (see the Help file) but the main parameters are:
result=Application.InputBox(Prompt:="....", Type:=n)
The value of n may be one of the following:
0 Formula
1 Number
2 String
4 Logical
8 Range
16 Error
64 Array of values
The following code uses the InputBox method to ask the user for a search range and a search value. the search range must be a valid Range reference and the search value must be a number.
Sub CountValues()
cellCount=0
Set rangeToSearch = Application.InputBox(Prompt:="Enter the range to search", type:=8) type=8 - must be a range object
searchValue = Application.InputBox(Prompt:="Enter the search value", Type:=1) type=1 - must be a number
If searchValue=False then Exit Sub user clicked Cancel
For Each c in rangeToSearch
If c.Value=searchValue then cellCount=cellCount+1
Next
MsgBox cellCount
End Sub