Page 93 - Microsoft Office Excel 2003 Programming Inside Out

VBA Programming Starter Kit

Writing to the Screen and Accepting Input

Accepting input from users and providing status messages are common tasks that you’ll use
within many procedures. VBA lets you use two common Windows methods of displaying
and asking for information from a user, which makes your macros easier to use because they
present a familiar interface to the user.

Creating a Message Box
When small informational messages or simple questions need to be asked, the MsgBox (mes­
sage box) function can be used. Message boxes are useful for a number of reasons. Besides
being familiar aspects of just about any Windows application, they are extremely simple and
easy to use. Here is a list of some common tasks for which you can use message boxes.
● Inform the user of an action that is about to take place, and possibly allow them the
option to cancel the action or choose a different action.
● Inform the user of an error condition that has occurred, and allow them to take correc­
tive actions.
● Inform the user that a particular task has been completed successfully or not. The
message box can inform the user exactly what was done to perform the task.
A primary advantage of using a message box is that it lets you present a message to the user
that can’t be ignored. It’s even possible to force the user to react to the message box by not
allowing them to switch to or open any other application.

As useful as message boxes are, they do have several limitations, such as the following:
Chapter 4
● A message box can present only one, two, or three buttons for users to choose from,
which means a limited number of options for the user.
● The buttons are available only in predefined sets—you can’t place a custom button into
a message box.
● No other features are available in a message box. You can’t add additional controls to
present more information/options to a user.
Despite these limitations, the message box is a very handy tool for any programmer. The syn­
tax of a message box statement is shown here.

MsgBox(prompt[, buttons] [, title][, helpfile, context]) as Integer
● prompt A required argument for the MsgBox function that contains the text that is
displayed within the message box. The text can be no longer than about 1024 charac­
ters, depending upon the width of the characters. If the text contains multiple lines,
you can specify the line breaks by including a carriage return with the constant vbCr,
(Chr$(13)), linefeed with the vbLf constant, (Chr$(10)), or carriage return/linefeed
combination with the vbCrLf constant.

67
Part 2: Visual Basic for Applications