In an earlier post, we have talked about MsgBox which is exactly opposite to VBA InputBox. The job of the InputBox function in VBA is to get some input from the user. It displays a dialog to the user that gives him a cue of which value should be entered, then it returns this value to the program.
Syntax of InputBox in VBA
Its syntax is as follows:
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context] )
‘prompt’ refers to the message that is displayed to the user.
‘title’ is an optional argument. It refers to the heading on the input dialog window. If it is omitted then a default title “Microsoft Excel” is shown.
‘default’ is an optional argument. It refers to the value that will appear in the textbox when the InputBox is initially displayed. If this argument is omitted, the textbox is left empty.
‘xpos’ is an optional argument. It refers to the positional coordinate of the input dialog window on the X-axis.
‘ypos’ is also an optional argument. It refers to the positional coordinate of the input dialog window on the Y-axis.
‘helpfile’ it is the location of help file that should be used with the InputBox. This is an optional parameter but it becomes a mandatory argument when ‘context’ argument is passed.
‘context’ represents the HelpContextId in the referenced ‘helpfile’. It is an optional parameter but it becomes a mandatory argument when the ‘helpfile’ argument is passed.
Few important things about VBA InputBox
- This function needs proper error handling. If the user clicks the cancel button without entering any value then it results in an error.
- InputBox can only accept up to 255 characters but can return only 254. So, always be cautious about the max length of a value that the user can enter.
- Unlike MsgBox, InputBox should always have a variable to which it can return the results.
- Another problem with this function is that sometimes users may enter something that is not expected at all. For instance, if you are asking the user for some integer and the user enters some string in such a case your code will break. So, its the programmer’s responsibility to handle such issues.
Examples of Input Box
Now let’s move on to some practical examples:
Example 1: Simple code illustrating the use of InputBox function.
Sub Test_Function()
Dim val As String
val = InputBox("Enter your name", "May I know your Name!")
MsgBox ("Hello, " & val & " welcome to Excel Trick!")
End Sub
Example 2: Sample code to use InputBox Function along with decision-making statements like IF Statement or Select Case Statement.
Here in this code, we have asked the user to enter a number and then we check the remainder after dividing the number by 2. If the remainder is 1 then the number is odd otherwise if the reminder is 0 then the number is even.
Sub Test_Function()
Dim number As Integer
number = InputBox("Enter any number :")
result = number Mod 2 'Get the remainder after dividing by 2
Select Case result
Case Is = 0
MsgBox ("The number you entered is even.")
Case Is = 1
MsgBox ("The number you entered is odd.")
End Select
End Sub
Example 3: Making the code immune to errors caused when the user clicks the cancel button on InputBox.
As I have foretold that when the user clicks the ‘Cancel’ button present on the input dialog window then it returns an empty string. This empty string can result in the breaking of code, so it is always a good idea to check the length of the string returned by the function. If the length is 0 then you can assume that the user must have clicked the cancel button and hence proceed accordingly.
In the below code I have asked the user to enter his name. If the user clicks the cancel button then a message box with a warning is shown otherwise he is greeted.
Sub Test_Function()
Dim name As String
name = InputBox("Enter your name :", "Your Name Please.")
If Len(name) = 0 Then 'Checking if Length of name is 0 characters
MsgBox "Please enter a valid name!", vbCritical
Else
MsgBox "Hello " & name & " welcome to Excel Trick."
End If
End Sub
Example 4: Making the code immune to mismatched datatypes.
Mismatched data types is another problem that you may have to deal with while using the VBA InputBox function. This problem can come into the picture when the user enters some value that you are not expecting. This problem has no foolproof solution but at-least you can stop the code from terminating unexpectedly by using the On Error Statement.
Below I have used the same code that we used for Example 2. But here I have made this code more error resistant by adding an “On Error GoTo NotValidInput” statement this ensures that whenever any exception is caused then code should terminate gracefully.
Sub Test_Function()
On Error GoTo NotValidInput
Dim number As Integer
number = InputBox("Enter any number :")
result = number Mod 2 'Get the remainder after dividing by 2
Select Case result
Case Is = 0
MsgBox ("The number you entered is even.")
Case Is = 1
MsgBox ("The number you entered is odd.")
End Select
Exit Sub
NotValidInput:
MsgBox ("You entered some invalid value!")
End Sub
So, this was all about VBA Input Box function. Do let us know your thoughts related to this function.
About Content Studio