Microsoft Office Tutorials and References
In Depth Information
The MsgBox function
The MsgBox function is one of the most useful VBA functions. Many of the examples in this
chapter use this function to display the value of a variable.
This function often is a good substitute for a simple custom dialog box. It’s also an excellent
debugging tool because you can insert MsgBox functions at any time to pause your code and
display the result of a calculation or assignment.
Most functions return a single value, which you assign to a variable. The MsgBox function not
only returns a value but also displays a dialog box that the user can respond to. The value
returned by the MsgBox function represents the user’s response to the dialog box. You can use
the MsgBox function even when you have no interest in the user’s response but want to take
advantage of the message display.
The official syntax of the MsgBox function has five arguments (those in square brackets are
MsgBox(prompt[, buttons][, title][, helpfile, context])
● prompt : (Required) The message displayed in the pop-up display.
● buttons : (Optional) A value that specifies which buttons and which icons, if any, appear
in the message box. Use built-in constants — for example, vbYesNo .
● title : (Optional) The text that appears in the message box’s title bar. The default is
Microsoft Excel .
● helpfile : (Optional) The name of the Help file associated with the message box.
● context : (Optional) The context ID of the Help topic. This represents a specific Help topic
to display. If you use the context argument, you must also use the helpfile argument.
You can assign the value returned to a variable, or you can use the function by itself without an
assignment statement. This example assigns the result to the variable Ans :
Ans = MsgBox(“Continue?”, vbYesNo + vbQuestion, “Tell me”)
If Ans = vbNo Then Exit Sub
Notice that I used the sum of two built-in constants ( vbYesNo + vbQuestion ) for the
buttons argument. Using vbYesNo displays two buttons in the message box: one labeled Yes and
one labeled No. Adding vbQuestion to the argument also displays a question mark icon. When
the first statement is executed, Ans contains one of two values, represented by the constant
vbYes or vbNo . In this example, if the user clicks the No button, the procedure ends.
See Chapter 12 for more information about the MsgBox function.
The following example demonstrates how to use an Excel worksheet function in a VBA
procedure. Excel’s infrequently used ROMAN function converts a decimal number into a Roman