How to Use VBA Input Boxes, With Examples
As a program runs, you might have a parameter which changes the flow conditionally. This parameter could be an input dependent on the user. Therefore, a VBA program needs to be a way to receive input from the user during runtime. This is where InputBox comes into the picture. Other languages have their own built-in functions that work like like this too.
You can see a simple InputBox in Excel with only the message as a parameter here:
Contents
Syntax of InputBox
<string variable name>=InputBox ( < Prompt > , [ < Title > ] , [ < default value > ] , [ < X position > ] , [ < Y position > ] , [ < help file > , < Help Context ID> ] )
Where
Prompt
is the string message displayed to the user.
Title
is the string displayed in the InputBox window’s title (in the place of “ Microsoft Excel ” in the image above)
Default
value is the value displayed in the textbox of the InputBox window when it pops up. We can also provide some instructions here.
X Position
is the pixel position on x axis (on the screen) where the top left corner of the InputBox should pop up and display during run-time.
Y Position
is the pixel position on y axis (on the screen) where the top left corner of the InputBox should pop up and display during run-time.
HelpFile
is the expression (of string datatype) that checks for the Help file that provides context-sensitive help on handling the dialog box.
Help Context
ID is the numeric ID ( “Help context number” ) that the “help author” assigns to the appropriate “Help topic”.
The InputBox function always returns only a string. So, if we need data of any other type, we need to use the appropriate converter functions offered by VBA.
*All parameters that are enclosed within square brackets [] are optional. But if the < help file > parameter is provided, the < context > parameter should also be provided and vice versa. Both these parameters should either be used together, or both should be omitted.
Examples of InputBox
A simple string message received during runtime from the user
Sub goto_demo() ' declare variables Dim str_age, int_age ' one goto label to iterate this in case the age provided by user is invalid get_age: ' get the age from the user strage = InputBox("Enter your age") ' convert it to an integer intage = CInt(strage) 'validate that the input is a number If IsNumeric(intage) Then ' validate and display if the user is a senior citizen If intage >= 60 Then MsgBox "You are a senior citizen." Else MsgBox "You are not a senior citizen. " End If Else MsgBox "Invalid value. Please try again. " GoTo get_age End If End Sub
Receive inputs from user to calculate total surface area of the cylinder
Sub surf_area_cyl() ' declare variables Dim r, h, fsa, csa, tsa, pi ' assign values to known variables pi = 3.14 ' Receive inputs from user r = CInt(InputBox(" Enter the radius of the cylinder's sides in cm", "Calculate TSA of Cylinder", "Use only numbers. Do not use the units or any symbols")) h = CInt(InputBox(" Enter the height of the cylinder in cm", "Calculate TSA of Cylinder", "Use only numbers. Do not use the units or any symbols")) ' calculate area of base and top fsa = 2 * pi * (r ^ 2) ' calcuate the curved surface area csa = (2 * pi * r) * h ' calculate total surface area of the cylinder tsa = fsa + csa ' Display the total surface area of the cylinder MsgBox "The total surface area of a cylinder whose radius is " & r & " and height is " & h & " is " & tsa & " sq cm" End Sub
InputBox function using all the optional parameters
Sub len_sentence() ' declare variables Dim sent ' assign value by receiving data from the user sent = InputBox("Enter a sentence", "This is a demo", "Your sentence goes here", 100, 300, ThisWorkbook.Path & "\samplehelp.chm", 101) ' display the length of the received input MsgBox Len(sent) End Sub
The InputBox dialog is not displayed in the center of the page because the X and Y positions have been provided in the code.
We have an additional “Help” button. Clicking on it takes the user to the help page authored by the help author.
If the help context id is not provided, but the help file path is provided in the parameter’s list of the InputBox function, you’ll encounter the error below – “Run-time error ‘5’: Invalid procedure call or argument.”
InputBox function with some optional parameters
Sub name_demo() ' declare variables Dim name ' assign value by receiving data from the user name = InputBox("What is your name ?", , "Your sentence goes here", , 300) ' Simply display it with a normal sentence. MsgBox "You name '" & name & "' is a sweet name." End Sub
In this example we have provided only the Y axis, so by default the InputBox is in the middle of the X axis of the screen.
Conclusion
The InputBox function helps us receive inputs from the user during runtime. Using this function, we can avoid hard coding any values. Though dynamic inputs are possible with InputBoxes, too many of them will slow down your program as you wait for users to input data in order to continue running.
If there are options to pass data through any back-end applications like MS. Access, MS Excel or SQL , that would generally be preferable. We should use InputBoxes to receive only genuine runtime inputs that cannot be predicted. That makes your code more robust.
One thought on “How to Use VBA Input Boxes, With Examples”