A comment is text in your macro code that is for information only—it has no effect on how the macro operates. Comments are useful—in fact they are recommended—for describing how your code operates. Then, when you or someone else needs to edit the code sometime down the road, its operation will be clear and not a matter of guesswork.

To create a comment, start a line with either an apostrophe or REM. You can put a comment at the end of a line of code using the apostrophe but not REM:

Rem This is a comment.
' This is another comment.
Dim S As String ' This too is a comment.
Dim S As String Rem But this is not permitted.

A variable is a place where you can store a single piece of information, such as a number of a segment of text. The name "variable" comes from the fact that the information can change while the macro is executing—it is not fixed. When you create a variable, you assign it a name, and then you use that name to refer to the variable in your source code. VBA variable names must follow these rules:

The case of names is irrelevant, although the VBA Editor automatically adjusts case to be the same for all instances of a given variable name. For example, if the first time you use a variable you call it Count, and the second time you type in COUNT, the second entry will be changed to Count by the editor.

You should use descriptive variable names—that is, names that describe the data the variable holds. For instance, a variable that holds an interest rate might be called IntRate or InterestRate. You could call it MickeyMouse or Q99 and VBA would not care, but descriptive names make your code a lot easier to read and debug.

It is common practice, although not required, to use a combination of upper- and lowercase letters for variable names as shown in these examples:

All variables should be declared before being used for the first time. This tells VBA about the variable, specifically its name and the kind of data it will hold. A variable declaration takes this form:

Dim varname As type

varname is the name of the variable, and type is the name of the variable's data type (as explained in the following sections). You can place multiple declarations on the same line:

Dim varname1 As type1, varname2 As type2, varname3 As type3

I say that variables should be declared, not that they must be declared. VBA gives you the option of not requiring variable declaration—you simply use new variable names as needed in your code. This may sound like a good idea, but in fact it is not. When variable declaration is required, misspelling a variable name results in an error message when you run the program, because the (misspelled) variable has not been declared, and the misspelling can be easily corrected. If variable declaration is not required, misspellings go undetected and can cause lots of problems. To make sure variable declaration is required, select Tools, Options in the VBA Editor and select the Require Variable Declaration option on the Editor tab. You can obtain the same effect by placing the Option Explicit statement at the start of every VBA module.

Numeric variables hold number data. VBA has six different numeric variable types that differ in whether they hold integer data (numbers without a decimal part such as 2, −145, and 32,190) or floating-point data (numbers with a decimal part such as −1.143, 0.0045, and 123,900.5). These six numeric variable types are summarized in Table 23.1.

Why have three integer types and three floating-point types? You can see that the types differ in terms of the range of values they can hold and, for the floating-point types, their precision or accuracy. They also differ in the amount of memory they consume. It's also true that certain operations, such as division, are faster with the integer types. To be honest, these memory and speed differences are rarely relevant with today's fast, memory-laden computers. Choose a numeric data type based on the range of values the variable will hold. When in doubt, it is always advisable to err on the side of caution, using a type Long rather than a type Integer, for example. If you try to store an out-of-range value in a variable, VBA generates an Overflow error.

Strings, which is just programmer-talk for text data, represent the second main class of data that VBA programs work with. VBA provides two types of string variables. A variable-length string can hold any amount of text up to about 2 billion characters. A variable-length string expands and contracts automatically to fit the data you put in it, so you do not need to specify the size when declaring it:

Dim City As String

A fixed-length string, on the other hand, has a fixed size you specify when the variable is declared. This can range from 1 to as many as approximately 64,000 characters. The declaration syntax is as follows:

Dim FirstName As String * 15
Dim  LastName As String * 15

If you assign text that is too long to a fixed-length string variable, the extra part of the text is cut off and lost.

VBA can figure out that you mean to use s as a number even though it was declared as a string, so the division is performed properly with the result that i is equal to 8.

Dim s As String
Dim i As Integer
s = "16"
i = s / 2

As its name implies, a constant is program data whose value does not change. VBA has two types of constants, literal and symbolic. A literal constant is a number or string typed directly into your code. String literals are typed in double quotation marks. Numeric literal constants are typed without special formatting. In this code, "Jackson" and 0.75 are literal constants:

Dim Ratio As Single
Dim Name As String
Max = 0.75
Name = "Jackson"

A symbolic constant has a name. To create a symbolic constant, use the Const keyword:

Const CONSTNAME As Type = value

The rules for constant names are the same as for variables. I like to use all uppercase names with underscore separators for constants, which makes them easily distinguishable from variables in the code (which are commonly written with a combination of upper- and lowercase). Thus, PRIME_RATE is easily identified as a constant, and PrimeRate is clearly a variable. The As type part of the declaration is optional and is required only if you want the constant to be stored as a specific data type. value is the constant value.

Here are two examples:

Const PRIME_RATE As Double = 0.042

Const FILE_SAVE_PATH = "c:my documentsdatabases"

A big advantage of using symbolic constants is that you can change the value of a constant throughout the program simply by editing its declaration statement. Another advantage is that the constant's name can help in making the program easier to read.

Boolean variables can hold only the two values True and False. They are widely used in all areas of programming because you'll find that you often need to store values that are true/false, yes/no, or on/off. You assign values to Boolean variables using the VBA keywords True and False. Here's an example:

Dim TaskDueToday As Boolean
TaskDueToday = True

Behind the scenes, VBA represents True and False by the values −1 and 0, respectively. When treated as a Boolean, any numeric expression will evaluate as True if its value is non-zero.

In VBA, the term "date" refers to the time of day as well as the calendar date. Dates are stored internally as floating-point numbers in which the part to the left of the decimal point represents the number of days since December 30, 1899, with negative numbers representing prior dates. The part to the right of the decimal point represents the time as a fraction of a day, so that .25 is 6 A.M., .5 is noon, and so on.

The Date data type is designed to hold date values. When you display a date value, the display format is determined by the system's short date format setting, and times are displayed as either 12- or 24-hour times, again in accordance with the system settings.

You write literal date values in just about any recognizable format, enclosed in # signs. Here are some examples:

Dim d1 as Date, d2 As Date, d3 As Date

d1 = #January 31, 2005#
d2 = #31 Jan 05#
d3 = #1/31/2005#

The VBA Editor will automatically convert date literals to a standard format. VBA has a wide range of tools for working with dates and times. They are beyond the scope of this chapter, but you can find detailed information on the VBA online documentation.

The Variant data type is VBA's default variable type, and also its most flexible. Default means that if you declare a variable without specifying a type, it is created as a Variant. These declarations create two type Variant variables:

Dim x

Dim y As Variant

This data type's flexibility comes from its ability to hold almost any type of data (with the single exception of fixed-length strings). Thus, a type Variant can hold numbers, text, object references, user-defined types, and arrays. One common use for this data type is when data needs to be treated either as text or as a number depending on circumstances. Another use is as an argument to procedures that can take different kinds of data. You should not, however, simply use type Variant as a convenience to avoid the necessity of thinking about what data type should be used for specific variables. The Variant data type requires more memory to store, and more processor time to manipulate, than other data types.

Another type of variable holds a reference to an object. Objects, which represent the various parts of the Outlook program (among other things), are a central part of VBA programming. You learn more about object variables when you look at Outlook's Object Model in Chapter 24.

An array is a group of two or more variables that have the same name and are distinguished by a numeric index. VBA offers two types of arrays: static and dynamic.

Dim ArrayName(n) As type

Dim NewArray(100) As Long

NewArray(1) = 25
x = 15
NewArray(x) = 99  ' Same as NewArray(15)
NewArray(MyArray(1)) = 5  ' Same as NewArray(25)

Dim NewArray(start To stop)

Dim Months(1 To 12)

Dim ChessBoard(1 To 8, 1 To 8) As Integer

Dim MyDynamicArray() As type

ReDim MyDynamicArray(indexes)

Dim DynamicArray1()
Dim DynamicArray2()
Dim DynamicArray3()
' 1 dimension, 21 elements 0-20.
Redim DynamicArray1(20)
' 1 dimension, 26 elements 5-30.
Redim DynamicArray2(5 to 20)
' 2 dimensions, 40 total elements.
ReDim DynamicArray3(1 To 4, 1 to 10)

ReDim [Preserve] arrayname(indexes)

A user-defined type, or UDT, can be used to define your own data structures. A UDT contains two or more elements and is designed to meet specific data storage needs of your macro. To define a UDT, use the Type ... End Type statement:

Type UDTName
    Element1 As type
    Element2 As type
    ....
    Elementn As type
End Type

Each element can be any of VBA's data types: Byte, Boolean, Integer, Long, Currency, Single, Double, Date, variable-length or fixed-length String, Object, Variant, another UDT, or an object type. Each element can be a single variable or an array. The rules for naming the UDT itself and it elements are the same as the rules for naming VBA variables as described earlier in this chapter.

Once you have defined a UDT, you declare it using the Dim statement. A UDT must be declared in a module but not within any procedure. Here's a UDT definition:

Type Person
    FirstName As String
    LastName As String
    EmailAddress As String
End Type

Then declare in a variable of this type:

Dim MyBestFriend As Person

Finally, access the elements of the UDT using the VariableName.ElementName syntax:

MyBestFriend.FirstName = "Alice"
MyBestFriend.LastName = "Wilson"
MyBestFriend.EmailAddress = "[email protected]"

You can create arrays of UDTs as well:

Dim AllMyFriends(100) As Person

An enumeration is a user-defined data type that consists of a defined set of symbolic constants. You use the Enum keyword to create an enumeration as shown in this example:

Enum Flavors
    Vanilla

Chocolate
    Strawberry
    Banana
End Enum

This creates an enumeration called Flavors with the constants Vanilla equal to 0, Chocolate equal to 1, and so on. If you do not want the enumeration values assigned sequentially starting at 0, you can specify the values:

Enum Flavors
    Vanilla = 2
    Chocolate = 3
    Strawberry = 5
    Banana = 10
End Enum

Enumerations can be defined only at the module level. Once an Enum is defined, it becomes available as a data type you can use to declare variables:

Dim IceCreamCone As Flavors

A variable declared as an enumerated type can only take on the set of values defined in the enumeration. A real convenience is that VBA displays autolist members for enumerated types, permitting you to select from a list of the enumeration's constants.

The assignment operator (=) tells VBA to assign a value to a variable. Specifically, the value of the expression on the right side of the operator is assigned to the variable on the left side of the operator. For example, the statement

x = 15

assigns the value 15 to x.

The mathematical operators perform the common operations such as addition and division. There are the four common arithmetic operations as follows:

+ Addition
-  Subtraction
*  Multiplication
/  Division

There are three others that are perhaps less common:

For both division operators, a Divide by Zero error occurs if the divisor is 0.

There is one string operation called concatenation, which simply joins two strings together. The operator for this is &. For example, after this code:

Dessert = "Chocolate" & "cake"

the variable Dessert contains "Chocolate cake". There are lots of other things you can do with strings, but they involve functions rather than operators. You can find information on these functions in VBA's online documentation.

You use comparison operators to perform comparisons between two expressions. The result of a comparison is either True or False depending on whether or not the comparison was true. The most commonly used comparison operators, which are used primarily with numeric expressions, are

For example, the expression

a < b

asks the question "is a less than b?" and returns either True or False depending on the values of a and b.

You use logical operators to manipulate logical (True/False) expressions. Most of the logical operators combine two logical expressions into a single logical value. The logical operators are described in Table 23.2.

The logical operators are often used in conjunction with the comparison operators. For example, the following expression evaluates as True only if x is equal to 5 and y is not equal to 0:

(x = 5) And (y <> 0)

When an expression contains more than one operator, it may not always be clear how the expression evaluates. For example, look at this expression:

20 / 4 + 6

How should you read this?

Potentially ambiguous expressions such as this one are resolved by VBA's rules of operator precedence, which determine the order in which operations are performed. The precedence of VBA's operators is given in Table 23.3. Operators with lower precedence numbers are performed first.

Operators that have the same precedence level, such as multiplication and division, are executed in left-to-right order.

With this information on the precedence rules, you can see that the previous example will evaluate to 11 because the division will be performed before the addition.

You can use parentheses in an expression to modify the order of execution. Those parts of an expression enclosed in parentheses are always evaluated first regardless of operator precedence. Therefore,

20 / (4 + 6)

evaluates to 2 because the parentheses force the addition to be performed before the division. You can use as many parentheses in an expression as you like as long as they always come in pairs with each left parenthesis having a matching right parenthesis. Nested parentheses—when one set is inside another set—execute starting with the innermost set and proceed outward.

A loop statement is used to execute a block of VBA statements a certain number of times. There are three loop statements.

For index = start To stop Step step
...
statements
...
Next index

Dim NewArray(50) As Integer
Dim j As Integer
For j = 0 To 50
    NewArray(j) = j * 4
Next j

Dim NewArray(100) As Integer
Dim j As Integer

For j = 100 To 0 Step −1
    NewArray(j) = 100 - j
Next j

For Each item in collection
...
statements
...
Next item

Dim o As Object
For Each o In SomeCollection
    o.Color = Blue
Next o

Do While condition
...
statements
...
Loop

Do Until condition
...
statements
...
Loop

Do
...
statements
...
Loop While condition

Do
...
statements
...
Loop Until condition

VBA's conditional statements are used to execute or not execute a block of statements depending on a program condition. The statements are executed once or not at all. There are two conditional statements in VBA.

If condition Then
    block1
Else

block2
End If

If condition1 Then
    block1
ElseIf condition2 Then
    block2
ElseIf condition3 Then
    block3
....
Else
    block4
End If

Select Case expression
    Case template-1
        statements-1
    Case template-2
        statements-2
    ....
    Case template-n
        statements-n
    Case Else
        statements-else
End Select

Case 0, 5 To 10, Is > 25

VBA supports two kinds of procedures, Subs and Functions. They are identical except for the fact that a Function returns data to the calling program, whereas a Sub does not.

To define a Sub procedure, the basic syntax is

Sub subname(argumentlist)
...
' Code is placed here
...
End Sub

The syntax to define a Function is similar:

Function functionname(argumentlist) As type
...
' Code is placed here
functionname = returnvalue

End Function

For both types of procedures, the name must follow VBA variable naming rules and also must be unique within the module where the procedure is located. argumentlist is an optional list of data passed to the procedure when it is called (as explained in the next section). For a function, the As type clause specifies the data type of the function's return value, and the functionname = returnvalue statement sets the value of that return value.

Many procedures have arguments that permit data to be passed to the procedure when it is called. The argument list can include as many arguments as are needed, and has the following syntax:

argname1 As type, argname2 As type, .....

Here's an example of a function procedure that is passed three numbers and returns the largest one:

Function Largest(n1 as Double, n2 As Double, n3 As Double) _
    As Double

    Dim temp as Double
    If n1 > n2 Then
        temp = n1
    Else
        temp = n2
    End If
    If n3 > temp Then
        Largest = n3
    Else
        Largest = temp
    End If

End Function

Each argument can be any of VBA's data types, including UDTs, arrays, or enumerations. To specify an array argument, use an empty set of parentheses:

Sub ProcessArray(array() As String)
...
End Sub

Calling a procedure requires a different syntax for Sub and Function procedures. For Sub procedures you have two choices: You can use the Call keyword, in which case the argument list must be in parentheses:

Call ProcedureName(ArgumentList)

Or, you can omit the Call keyword and the parentheses as well:

ProcedureName ArgumentList

Because a function returns a value, it can be treated as an expression and used—that is, called—any place an expression could go. For example:

result = Largest(x, y, z)

You learned earlier in this chapter how to declare variables in your VBA programs. Most of the variables you use will in fact be declared within procedures. These are called local variables and they have some special characteristics that relate to scope.

Scope refers to the parts of a program where a variable is visible. You can use a variable only where it is in scope. In other parts of the program, it is out of scope and might as well not exist. In your VBA programs, you control a variable's scope by where the variable is declared:

This has implications for the programmer. You can use the same variable name in two or more procedures and they will be totally independent of each other. But what if you have a variable declared within a procedure and also use the same name for a variable declared at the module level? If a procedure contains a variable with the same name as a module-level variable, the local copy takes precedence. Otherwise, the module-level variable is visible within the procedure.

Now that you have learned something about how to write procedures, how do you actually go about it? The VBA Editor makes it easy:

What is it that differentiates a macro procedure from a non-macro procedure? The main difference is whether you intend the user to execute the procedure directly from the Macros dialog box. In order to be considered a macro by Outlook and to be listed in the Macros dialog box, a procedure must meet two criteria:

Figure 23.1. Adding a Sub or Function procedure to a VBA module.

Suppose, however, that you wrote a Sub procedure that takes no arguments, yet you do not want it listed in the Macros dialog box. You can "hide" it by using the Private keyword:

Private MySub()
...
End Sub

Now your other macro code will be able to call this Sub, but the user will not be able to execute it from the Macros dialog box.

The MsgBox function displays a dialog box with a message and one or more buttons. The function's return value indicates which of the buttons the user clicked. You use this function as follows (I have omitted a couple of optional and rarely used arguments):

MsgBox(prompt, buttons, title)

As mentioned, the function's return value indicates which of the buttons the user selected. The possible return values are represented by the constants shown in Table 23.5.

To use the MsgBox function, call it with the appropriate arguments and then test the return value. Here's an example, with the displayed dialog box shown in Figure 23.2:

Dim retval As Integer
retval = MsgBox("Delete file - are you sure?", vbYesNo, _
   "Confirm delete")
If retval = vbYes Then
    ' Code to delete file goes here.
End If

Figure 23.2. An example of a dialog box displayed by the MsgBox function.

You use the InputBox function to return a string entered by the user. The syntax for this function is (I have omitted some optional and rarely used arguments):

InputBox(prompt, title, default,)

This code uses the InputBox function to get the user's country of residence, with "United States" being the default response. The displayed dialog box is shown in Figure 23.3.

Dim Country As String
Country = InputBox("Your country of residence?",  "Country", _
    "United States")

Figure 23.3. Getting user input with the InputBox function.