VBA: Inputbox Function and Inputbox Method

文章背景：在编写VBA代码时，有时需要一个用户输入的对话框，以实现跟用户的交互。Inputbox函数是VBA自带的，功能相对简单；使用Application对象的inputbox方法，也可显示一个接收用户输入的对话框。接下来分别对两者进行介绍。

Inputbox Function

1.1 功能

Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box.

1.2 语法

InputBox(prompt, [ title ], [ default ], [ xpos ], [ ypos ], [ helpfile, context ])

• prompt : Required. String expression displayed as the message in the dialog box.
• title : Optional. String expression displayed in the title bar of the dialog box.
• default : Optional. 默认值。
• xpos, ypos : Optional. 对话框出现的位置。
• helpfile, context ：Optional. 帮助文件。 注意：If the user chooses OK or presses ENTER, the InputBox function returns whatever is in the text box. If the user chooses Cancel, the function returns a zero-length string ("").

1.3 代码举例

Sub test()

    Dim result As Variant

    result = InputBox("Enter a number!")

    MsgBox result

End Sub

如果选择对话框中的“取消”键，result返回的是空字符串。

Sub test()

    Dim result As Integer

    result = InputBox("Enter a number!")

    MsgBox result

End Sub

如果选择对话框中的“取消”键，程序报错。原因为：在此处，result的数据类型为“Integer”，而InputBox返回的是空字符串，两者数据类型不符。

视频演示：http://mpvideo.qpic.cn/0bf2ciab2aaa24afdbtlknpvaewddujaahia.f10002.mp4?

Application.InputBox method

2.1 功能

Displays a dialog box for user input. Returns the information entered in the dialog box.

2.2 语法

expression.InputBox (Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

• expression : A variable that represents an Application object.
• Type : Optional. Variant. Specifies the return data type. If this argument is omitted, the dialog box returns text. The following table lists the values that can be passed in the Type argument. Can be one or a sum of the values. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

注意：The dialog box has an OK button and a Cancel button. If you select the OK button, InputBox returns the value entered in the dialog box. If you select the Cancel button, InputBox returns False.

If Type is 8, InputBox returns a Range object. You must use the Set statement to assign the result to a Range object, as shown in the following example.

Set myRange = Application.InputBox(prompt := "Sample", type := 8)

If you do not use the Set statement, the variable is set to the value in the range, rather than the Range object itself.

两者的比较

The InputBox method differs from the InputBox function in that it allows selective validation of the user's input, and it can be used with Excel objects, error values, and formulas. Notice that Application.InputBox calls the InputBox method; InputBox with no object qualifier calls the InputBox function.

视频演示：http://mpvideo.qpic.cn/0bf2daab4aaa7uafe6tlkbpvaggddymaahqa.f10002.mp4?

Application.InputBox具有数据验证功能，如果数据类型设置为单元格，输入的是数字或字符串，则会出现提醒，让用户重新输入。

参考资料：

[1] Coursera课程（Excel/VBA for Creative Problem Solving, Part 2）

[2] InputBox function（https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputbox-function）

[3] Application.InputBox method (Excel)（https://docs.microsoft.com/en-us/office/vba/api/excel.application.inputbox）