How to Use VBA Input Boxes, With Examples - VBA and VB.Net Tutorials, Education and Programming Services

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:

InputBox "Enter Your Age"

Syntax of InputBox

<string variable name>=InputBox ( < Prompt > , [ < Title > ] , [ < default value > ] , [ < X position > ] , [ < Y position > ] , [ < help file > , < Help Context ID> ] )

InputBox Syntax

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
The parts of an InputBox: Prompt, Title, and Default string value
Message: The total surface area of a cylinder whose radius is 6 and height is 22 is 1055.04 sq cm

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.

Inputbox that says "Your sentence goes here"

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.”

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.

InputBox asking "What is your name?"

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.

Leave a Reply

Your email address will not be published. Required fields are marked *