Select Page

VBAincludes a wide variety of built-in functions. While this/section can’t cover all of them, you will learn about the most useful ones here. You may want to review the Functions section of the VBAonline help for a more comprehensive list. You can also use the Object Browser (see Chapter 99, which is on the accompanying CD) to view all of the VBAfunctions.


VBAincludes support for a variety of operators. Table 21.2 summarizes these operators. Although VBAincludes fairly complex operator precedence rules governing the order in which operators are evaluated, you can always use parentheses to force a particular order of operations.

Quick Info

With the variety of built-in functioos that VBAprovides, you might find it difficult to remember the arguments that you have to supply to make each function work. That’s why VBA provides Quick Info, a way of prompting you on screen for the arguments to a function. Figure 21.7shows Quick Info in action

In this example, the Prompt argument is required. The other arguments are optional, as shown by the square brackets surrounding each argument. The Buttols As argument must be one of the values in the built-in enumeration VbMsg- BoxStyle, which you can view using the Object Browser or by starting to enter the argument. Finally, the return value of the function will be one of the values allowed by the VbMsgBoxResult enumeration.

As you work with VBA,you’ll probably find Quick Info very helpful-If you reach the point where you have learned the arguments for every function and Quick Info is becoming intrusive, you can disable it by selecting the Tools> Options> Editor.

User Interface Functions

VBAprovides two functions that are extremely useful for creating a user interface to your procedures. The MsgBox function displays a dialog box on screen, and returns information about which button the user clicked to dismiss the dialog box. The InputBox function prompts the user for information. The MsgBox function takes five arguments:

Msgbox(Prompt[, Buttons][, Title][, HelpFile, Context]

The Prompt and Title arguments supply the text and caption for the dialog box. The Buttons argument controls the appearance-of the dialog box; it accepts a series of constants that you can see by checking the Intellibense help for the function. The last two arguments allow you to specify a help file and help topic to be used if the user presses Fl while the dialog box is displayed.

The dialog box produced by the following statement:

The return value from a call to the MsgBox function is a constant that indicates which button the user clicked. Although these constants are numeric, you should always use the built-in VBA constants to evaluate this return value. That will make your code more readable, and protects you against potential future changes to the literal values of the various constants. The MsgBoxDemo procedure shows this technique.

Sub MsgBoxDemoO
Dim intRet As Integer
intRet = MsgBox(‘Unable to Save File’, _
vbAbortRetryIgnore + vbCritical, ‘File Error’)

Again, the Prompt and Title arguments control the text and caption of the dialog box. The Default argument provides a default return..y~ue, and the next two arguments allow you to specify where the dialog box ~ar on screen. As an example, Figure 21.9 shows the dialog box generated by the @nowing call:

In putBox ‘Enter a Number’, ‘Numeric , Prompt’, 27

The return value from the InputBox function is a variant containing the value that the user typed into the edit control in the dialog box.

String Functions

VBA supports a wide variety of text-string manipulation functions. Historically, BASIC dialects have been very strong in string handling, and VBA is no exception.The most important of these functions

Share This