In earlier post we have talked about MsgBox which is exactly opposite to VBA InputBox. The job of 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’ it 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 X-axis.
‘ypos’ is also an optional argument. It refers to the positional coordinate of the input dialog window on 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 ‘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 into 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 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 user may enter something that is not expected at all. For instance if you are asking user for some integer and user enters some string in such a case your code will break. So, its 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
Here in this code we have asked user to enter a number and then we check the reminder after dividing the number by 2. If the reminder 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 user clicks the cancel button on InputBox.
As I have foretold that when user clicks the ‘Cancel’ button present on the input dialog window then it returns an empty string. This empty string can result into 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 user must have clicked the cancel button and hence proceed accordingly.
In the below code I have asked 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 datatypes is another problem that you may have to deal while using VBA InputBox function. This problem can come into picture when user enters some value that you are not expecting. This problem has no fool proof solution but at-least you can stop the code from terminating unexpectedly by using 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 “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.