VBA InputBox

There are two InputBox functions which are used to show a popup to get input from a user. There is an InputBox function defined in the Interaction Module and there is an InputBox function defined as a member of the Excel Application object. The VBA.Interaction.InputBox function takes a string as input and returns a String whereas the Excel.Application.InputBox function can take different types of input, validate the input type, and returns a Variant.

VBA.Interaction.InputBox

The VBA.Interaction.Inputbox function shows a popup where a user can enter text input. The function returns a String representing the user input. If the user does not enter anything or presses cancel, an empty string is returned.

Syntax:

InputBox(Prompt, [Title], [Default], [XPos], [YPos], [Helpfile], [Context]) As String

Public Sub Example()

    Dim UserInput As String

    UserInput = VBA.Interaction.InputBox("Please enter input:", "Sample Inputbox")

    Debug.Print UserInput, Len(UserInput)

End Sub

Excel.Application.Inputbox

Excel.Application.InputBox shows a popup where a user can enter a value. The function returns a Variant. If the user cancels or does not enter anything the function returns False. The function allows an input type to be specified. Types can be combined by adding together type arguments. For example, to accept numbers and text, the type argument should be 1 + 2. If an invalid type is entered, a warning will be displayed.

Syntax:

Application.InputBox(Prompt As String, [Title], [Default], [Left], [Top], [HelpFile], [HelpContextID], [Type])

Public Sub Example()

    Dim UserInput As Variant

    UserInput = Excel.Application.InputBox("Please enter input:", "Sample Inputbox", Type:=1)

    Debug.Print UserInput, Len(UserInput), TypeName(UserInput)

End Sub