Page 199 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 185

AUTOMATING ACTIONS WITH MACROS 9

The MsgBox function returns an integer value between 1 and 7 If you want to display information
that indicates the button selected by the user. You can determine for the user, such as results, and
the button clicked by the user using the value returned by looking you do not care about a user
at the integer value or using the constant value that represents the response, you can use the MsgBox
button. The following table shows the integer values returned by function without capturing the
the MsgBox function and the equivalent constant value. user response. The following
example displays the contents of
MSGBOX RETURN VALUE CONSTANT DESCRIPTION the TotalSum variable in a
1 vbOK OK button clicked message box with the OK button.
The message box closes when a
2 vbCancel Cancel button clicked
user clicks OK, but Excel does not
3 vbAbort Abort button clicked return the value of the button to
4 vbRetry Retry button clicked your procedure. You can use the
MsgBox function without
5 vbIgnore Ignore button clicked
assigning it to a variable if the only
6 vbYes Yes button clicked argument is the Prompt value.
7 vbNo No button clicked
Example:
MsgBox(TotalSum)

ˇ Type a conditional Á Type additional code for ‡ Switch to Excel and run ■ The message box displays
statement to test the value the subroutine. the macro. and the macro processes the
returned. value of the clicked button.
■ You can type & to join the
Note: In this example, the If text string with a variable
Then statement looks to see if a value.
value of 6 or 7 is returned.

185