Basic Input and Output

First, look at some simple communication techniques you can use to make your macros more flexible and useful. If you want to display a message, use the MsgBox function, which is useful if you want to display a warning message or ask a simple question.

In the first example, you want to make sure that the printer is switched on before a print operation. The following code generates the dialog box in Figure 1-21, giving the user a chance to check the printer. The macro pauses until the OK button is clicked:

MsgBox "Please make sure that the printer is switched on"

Microsoft Excsl


Pleasernake sure that the primer le switched on

[ OK

Figure 1-21

If you want to experiment, you can use the Immediate window to execute single lines of code. Alternatively, you can insert your code into a standard module in the VBE window. In this case, you need to include Sub and End Sub lines as follows:

Sub Test1()

MsgBox "Please make sure that the printer is switched on" End Sub

An easy way to execute a sub procedure is to click somewhere in the code to create an insertion point, then press F5.

MsgBox has many options that control the types of buttons and icons that appear in the dialog box. If you want to get help with this, or any VBA word, just click somewhere in the word and press the F1 key. The Help screen for the word will immediately appear. Among other details, you will see the input parameters accepted by the function:

MsgBox(prompt[, buttons] [, title] [, helpfile, context])

Parameters in square brackets are optional, so only the prompt message is required. If you want to have a title at the top of the dialog box, you can specify the third parameter. There are two ways to specify parameter values: by position and by name.

Parameters Specified by Position

If you specify a parameter by position, you need to make sure that the parameters are entered in the correct order. You also need to include extra commas for missing parameters. The following code provides a title for the dialog box, specifying the title by position and producing the result shown in Figure 1-22:

MsgBox "Is the printer on?", , "Caution!"


jsthe printer on?

Figure 1-22

Parameters Specified by Name

There are some advantages and some special considerations required when specifying parameters by name:

□ You can enter them in any order and do not need to include extra commas with nothing between them to allow for undefined parameters.

□ You do need to use := rather than just = between the parameter name and the value, as already pointed out.

The following code generates the same dialog box as in Figure 1-22:

MsgBox Title:="Caution!", Prompt:="Is the printer on?"

Another advantage of specifying parameters by name is that the code is better documented. Anyone reading the code is more likely to understand it.

If you want more information on the buttons parameter, you will find a table of options in the help screen as follows:






Display OK button only



Display OK and Cancel buttons



Display Abort, Retry, and Ignore buttons



Display Yes, No, and Cancel buttons



Display Yes and No buttons



Display Retry and Cancel buttons



Display Critical Message icon



Display Warning Query icon



Display Warning Message icon



Display Information Message icon



First button is default



Second button is default



Third button is default



Fourth button is default



Application modal; the user must respond to the message box before continuing work in the current application



System modal; all applications are suspended until the user responds to the message box



Adds Help button to the message box

vbMsgBoxSet Foreground


Specifies the message box window as the foreground window



Text is right-aligned



Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

Values 0 to 5 control the buttons that appear. A value of 4 gives Yes and No buttons, as shown in Figure 1-23:

MsgBox Prompt:="Delete this

record?", Buttons:=4

Microsoft Excel

Microsoft Excel

Figure 1-23

Values 16 to 64 control the icons that appear; 32 gives a question mark icon. If you want both value 4 and value 32, add them to see the dialog box in Figure 1-24:

MsgBox Prompt:="Delete this record?", Buttons:=36

Microsoft Excel

I^J Delete tlnis- record? Yes I I No

Figure 1-24


Specifying a Buttons value of 36 ensures that your code is indecipherable to all but the most battle-hardened programmer. This is why VBA provides the constants shown to the left of the button values in the help screen. Rather than specifying Buttons by numeric value, you can use the constants, which provide a better indication of the choice behind the value. The following code generates the same dialog box as the previous example:

MsgBox Prompt:="Delete this record?", Buttons:=vbYesNo + vbQuestion

The VBE helps you as you type by providing a pop-up list of the appropriate constants after you type Buttons:=. Point to the first constant and press the plus key (+), and you will be prompted for the second constant. Choose the second and press the spacebar or Tab to finish the line. If there is another parameter to be specified, enter a comma rather than a space or a Tab.

Constants are a special type of variable that do not change, if that makes sense. They are used to hold key data and, as you have seen, provide a way to write more understandable code. VBA has many built-in constants that are referred to as intrinsic constants. You can also define your own constants, as you will see later in this chapter.

Return Values

There is something missing from the previous examples of MsgBox. You are asking a question, but failing to capture the user's response to the question. That is because you have been treating MsgBox as a statement, rather than a function. This is perfectly legal, but you need to know some rules if you are to avoid syntax errors. You can capture the return value of the MsgBox function by assigning it to a variable.

However, if you try the following, you will get a syntax error:

Answer = MsgBox Prompt:="Delete this record?", Buttons:=vbYesNo + vbQuestion

The error message, Expected: End of Statement, is not really very helpful. You can click the Help button on the error message to get a more detailed description of the error, but even then you might not understand the explanation.


The problem with the previous line of code is that there are no parentheses around the function arguments. It should read as follows:

Answer = MsgBox(Prompt:="Delete this record?", Buttons:=vbYesNo + vbQuestion)

The general rule is that if you want to capture the return value of a function, you need to put any arguments in parentheses. If you don't want to use the return value, you should not use parentheses, as with the original examples of using MsgBox.

The parentheses rule also applies to methods used with objects. Many methods have return values that you can ignore or capture. See the section on object variables later in this chapter for an example.

Now that you have captured the return value of MsgBox, how do you interpret it? Once again, the help screen provides the required information in the form of the following table of return values:

























If the Yes button is clicked, MsgBox returns a value of 6. You can use the constant vbYes, instead of the numeric value, in an If test:

Answer = MsgBox(Prompt If Answer = vbYes Then

="Delete selected Row?", Buttons:=vbYesNo + vbQuestion) ActiveCell.EntireRow.Delete

Another useful VBA function is InputBox, which allows you to get input data from a user in the form of text. The following code generates the dialog box shown in Figure 1-25:

UserName = InputBox(Prompt:="Please enter your name")

Microsoft Excel


1 'lanes! |


Figure 1-25

InputBox returns a text (string) result. Even if a numeric value is entered, the result is returned as text. If you click Cancel or OK without typing anything into the text box, inputBox returns a zero-length string. It is a good idea to test the result before proceeding so this situation can be handled. In the following example, the sub procedure does nothing if Cancel is clicked. The Exit Sub statement stops the procedure at that point. Otherwise, it places the entered data into cell B2:

Sub GetData()

Sales = InputBox(Prompt:="Enter Target Sales") If Sales = "" Then Exit Sub Range("B2").Value = Sales End Sub

In this code, the If test compares Sales with a zero-length string. There is nothing between the two double quote characters. Don't be tempted to put a blank space between the quotes.

There is a more powerful version of InputBox that is a method of the Excel Application object. It has the ability to restrict the type of data that you can enter. It is covered in Chapter 2.

0 0

Post a comment