Built-in VBA Excel Functions

VBA contains a large set of built-in functions and procedures, the use of which significantly simplifies programming. Note that all necessary information regarding the use of available functions can be found in the VBA Help system.

• Go to the Visual Basic for Applications editor window and choose Help | Visual Basic for Applications Help (or press F1).
• In the Excel Help window, select in the left-hand contents: Visual Basic for Applications Language Reference | Visual Basic Language Reference | Functions.

From there, you can see the entire list of functions available in VBA, along with examples of their use.

Built-in Dialog Boxes

Two types of dialog boxes are often encountered in VBA projects: message boxes and input boxes. These are built into VBA, and if their capabilities are sufficient, you can avoid designing your own dialog boxes.

• A message box (MsgBox) displays simple messages for the user.
• An input box (InputBox) allows the user to enter information.

Input Box

The InputBox() function displays a dialog box on the screen containing a message, an input field, and two buttons: OK and Cancel. It waits for the user to enter text and press a button. When the OK button is pressed, it returns a String value containing the text entered in the input field. When the Cancel button is pressed, it returns an empty string (Empty).

Syntax:

InputBox(Prompt[, Title][, Default][, Xpos][, Ypos] [, Helpfile, Context])

• Prompt — a string expression displayed as a message in the dialog box. The string expression may contain multiple lines. To separate lines, you can use the carriage return character (Chr(13)), the line feed character (Chr(10)), or a combination (Chr(13) & Chr(10)).
• Title — a string expression displayed in the dialog box title bar. If omitted, the application name is placed in the title bar.
• Default — a string expression displayed in the input field as the default if the user does not enter another string. If omitted, the input field is empty.
• Xpos — a numeric expression that sets the horizontal distance between the left edge of the dialog box and the left edge of the screen. If omitted, the box is centered horizontally on the screen.
• Ypos — a numeric expression that sets the vertical distance between the top of the dialog box and the top of the screen. If omitted, the box is placed about one-third down the screen.
• Helpfile — a string expression specifying the name of the Help file containing information about the dialog box. If this parameter is specified, the Context parameter must also be provided.
• Context — a numeric expression specifying the context ID of the relevant Help topic. If specified, the Helpfile parameter must also be provided.

Example: The following code (also in the file 2-Examples of using built-in dialog boxes.xlsm on the CD) displays an input box shown in:

Sub DemoInputBox1()
    Dim n As String
    n = InputBox("Enter your name", "Input Box Example")
    Debug.Print n
End Sub

Handling the Cancel Button

When entering data with InputBox(), it is reasonable to handle the event of pressing the Cancel button. For example, in the following case:

x = InputBox("Enter x", "Example")
y = x ^ 2

If the user presses Cancel, the code execution is interrupted with a type mismatch error. This situation can easily be avoided by adding just one line of code, which checks whether the input field is empty (which is exactly what happens when Cancel is pressed).

Sub DemoInputBox2()
    Dim x As String
    Dim y As Double
    x = InputBox("Enter x", "Example")
    If x = Empty Then Exit Sub
    y = x ^ 2
    Debug.Print y
End Sub

Message Box

The MsgBox() procedure displays a dialog box containing a message, waits for the user to press a button, and then returns an Integer value indicating which button was pressed.

Syntax:

MsgBox(Prompt[, Buttons] [, Title] [, Helpfile, Context])

• Prompt — a string expression displayed as a message in the dialog box.
• Buttons — a numeric expression representing the sum of values that specify the number and type of buttons displayed, the type of icon used, the default button, and the modality of the message box. The default value of this parameter is 0. The values of the constants defining the number, type of buttons, and type of icon used are listed in Tables 1–3
• Title — a string expression displayed in the dialog box title bar. If omitted, the application name is used.
• Helpfile — a string expression specifying the name of the Help file containing information about the dialog box. If this parameter is provided, the Context parameter must also be specified.
• Context — a numeric expression specifying the context ID of the relevant Help topic. If this parameter is provided, the Helpfile parameter must also be specified.

Table 1. Values of the Buttons parameter of the MsgBox() procedure defining which buttons are displayed in the dialog box

Constant	Value	Buttons Displayed
vbOKOnly	0
vbOKCancel	1
vbAbortRetryIgnore	2
vbYesNoCancel	3
vbYesNo	4
vbRetryCancel	5

Table 2. Values of the Buttons parameter of the MsgBox() procedure defining the information icons displayed in the dialog box

Constant	Value	Message Icon
vbCritical	16
vbQuestion	32
vbExclamation	48
vbInformation	64

Table 3. Values of the Buttons parameter of the MsgBox() procedure defining the default button in the dialog box

When writing programs that respond depending on which dialog box button is pressed, it is more convenient to use the VBA constants listed in Table 4 instead of return values. These make the program code easier to read and also easier to remember.

Table 4. Constants identifying the pressed button

An example of using message boxes is provided: the square of an entered value is calculated, and the result is then sequentially displayed in five message boxes.

• The first box is a simple message box.

• The second is a message box displaying text on two lines .

• The third is a message box with a message and an information icon.

• The fourth is a message box with a message, an information icon, and two buttons, with the Yes button set as the default.

• The fifth is a message box with a message, an information icon, and a custom title.

Determining the Pressed Button in a Message Box

The MsgBox() procedure is convenient for displaying information. However, if it is necessary to know which choice the user made by pressing one of the dialog box buttons, then MsgBox() must be used as a function. In this case, the value returned by MsgBox() should be assigned to a variable, and its parameters must be placed in parentheses.

Example of using a message box with three buttons

Sub DemoThreeButtonsMsgBox()
    Dim structure As Integer
    Dim btn As Integer   
    structure = vbYesNoCancel + vbQuestion + vbDefaultButton1
    btn = MsgBox("Choose <Yes>, <No> or <Cancel>", structure, "Another Example")  
    Select Case btn
        Case vbYes
            MsgBox "You selected <Yes>", vbInformation, "Another Example"
        Case vbNo
            MsgBox "You selected <No>", vbInformation, "Another Example"
        Case vbCancel
            MsgBox "You selected <Cancel>", vbInformation, "Another Example"
    End Select
End Sub