Built-in Functions

You may not have realized it, but you have already used two built-in functions: MsgBox and InputBox.

MsgBox

You have used the MsgBox function a number of times in this book already. Up to this point, it has only been with an OK button to acknowledge receipt of the message. However, other possibilities are available.

Table 11-1 shows the buttons and icons available to the MsgBox function. We will discuss the meaning of the values in a few moments.

Table 11-1: Buttons and Icons in the MsgBox Function

Constant	Value	Description
vbOKOnly	0	Displays OK button only.
vbOKCancel	1	Displays OK and Cancel buttons.
vbAbortRetryIgnore	2	Displays Abort, Retry, and Ignore buttons.
vbYesNoCancel	3	Displays Yes, No, and Cancel buttons.
vbYesNo	4	Displays Yes and No buttons.
vbRetryCancel	5	Displays Retry and Cancel buttons.
vbCritical	16	Displays Critical Message icon.
vbQuestion	32	Displays Warning Query icon.
vbExclamation	48	Displays Warning Message icon.
vbInformation	64	Displays Information Message icon.
vbDefaultButton1	0	First button is default.
vbDefaultButton2	256	Second button is default.
vbDefaultButton3	512	Third button is default.
vbDefaultButton4	768	Fourth button is default.
vbApplicationModal	0	Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal	4096	System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton	16384	Add Help button to the message box.
VbMsgBoxSetForeground	65536	Specify the message box window as the foreground window.
vbMsgBoxRight	524288	Text is right aligned.
vbMsgBoxRtlReading	1048576	Specify that text should appear as right-to-left reading on Hebrew and Arabic systems.

Up to this point, the syntax we have used for the MsgBox has been as follows:

MsgBox "Welcome to VBA"

This will give you the result shown here.

There are two other arguments you can add to the function (actually, there is one more for a compiled help file that we will not be treating here). The second argument is the button or icon you want to use, from the list in Table 11-1, and the third is the title above the box.

If you modify the preceding code as follows:

MsgBox "Welcome to VBA", vbYesNoCancel, "VBA Message Box"

you will see these results:

You could add the Information icon, if you wanted, as follows:

MsgBox "Welcome to VBA", vbYesNoCancel + vbInformation, "VBA Message Box" 

That would result in the MsgBox with the Information icon added:

Why the numeric values in Table 11-1? Those values can be substituted for the constants that indicate the name of the button. For instance, you can substitute the following syntax for the MsgBox:

MsgBox "Welcome to VBA", 3 + 64, "VBA Message Box"

This will yield the same results as shown in the previous illustration. But it also opens a lot of programming possibilities. Let’s look at the following very simple example to see what I mean.

Sub messageBoxTest()  Dim intInput As Integer  Dim intButtonStyle As Integer  Dim intIconStyle As Integer  Dim strMessage As String  Dim strTitle As String  strTitle = "MsgBox Example"  intInput = InputBox("Enter either a 1 or 2")  If intInput = 1 Then   strMessage = "Do you want 1?"   intButtonStyle = 4   intIconStyle = 32  Else   strMessage = "I guess you wanted 2"   intButtonStyle = 1   intIconStyle = 64   End If   MsgBox strMessage, intButtonStyle + intIconStyle, strTitle End Sub 

In this example, the user is asked to enter either 1 or 2 (we have not put any code in to test for the validity of the entry). The If….Then…Else structure will set the integer values of the variables according to the values in Table 11-1. We then substitute the variables for the actual values in the MsgBox syntax.

If the user selects 1, the MsgBox shown here will come up:

But if the user selects 2, this box will be shown:

By using this technique, you can make the MsgBox reflect whatever programming situation has occurred.

We have one small problem here: all the buttons do is dismiss the box. What if you want a specific action associated with a button that has been selected?

Each of the available buttons will produce an integer result. These results are shown in this table.

Constant	Value	Description
vbOK	1	OK
vbCancel	2	Cancel
vbAbort	3	Abort
vbRetry	4	Retry
vbIgnore	5	Ignore
vbYes	6	Yes
vbNo	7	No

Let’s look at the following code that determines which button was pressed.

Sub messageBoxTest()  Dim intButtonPressed As Integer  intButtonPressed = MsgBox("Welcome to VBA", vbYesNo, "Message Box Test")  If intButtonPressed = 6 Then   MsgBox "Yes was selected"   Else   MsgBox "No was selected"   End If End Sub

Notice in this case, when assigning the value of the MsgBox to a variable, you have to enclose the arguments in parenthesis. When it is serving as just a message box, you do not.

Based on the information in the previous table, you can see that vbYes returns an integer value of 6, and vbNo returns an integer value of 7. You could have used the following syntax to get the same results:

If intButtonPressed = vbYes Then

If you select “Yes,” you should see the response shown here:

Selecting “No” will result in this response:

Remember that all of these buttons are VB constants, which are easily identified because of the vb prefix.

The full syntax for MsgBox is

MsgBox prompt, buttons, title, helpfile, context

The only argument that is required is prompt.

InputBox

The InputBox will prompt the user for information. The value that the user enters into the box needs to be assigned to a variable. Once inside that variable, you can manipulate that value any way that you choose.

The syntax for an InputBox is as follows:

InputBox (prompt, title, default, xposition, yposition, helpfile, context)

Of these, only the prompt is required. If you do not list a default value, the input box is left empty. The x and y positions will determine the horizontal and vertical positions of the input box on the screen.