Constants

The VBA language has two types of constants. A literal constant (also called a constant or literal ) is a specific value, such as a number, date, or text string, that does not change, and that is used exactly as written. Note that string constants are enclosed in double quotation marks, as in "Donna Smith" and date constants are enclosed between number signs, as in #1/1/96#.

For instance, the following code stores a date in the variable called dt:

Dim dt As Date
dt = #1/2/97#

A symbolic constant (also sometimes referred to simply as a constant) is a name for a literal constant.

To define or declare a symbolic constant in a program, we use the Const keyword, as in:

Const InvoicePath = "d:\Invoices\"

In this case, Excel will replace every instance of InvoicePath in our code with the string "d:\Invoices\". Thus, InvoicePath is a constant, since it never changes value, but it is not a literal constant, since it is not used as written.

The virtue of using symbolic constants is that, if we decide later to change "d:\Invoices\" to "d:\OldInvoices\", we only need to change the definition of InvoicePath to:

Const InvoicePath = "d:\OldInvoices\"

rather than searching through the entire program for every occurrence of the phrase "d:\Invoices\".

It is generally good programming practice to declare any symbolic constants at the beginning of the procedure in which they are used (or in the Declarations section of a code module). This improves readability and makes housekeeping simpler.

In addition to the symbolic constants that you can define using the Const statement, VBA has a large number of built-in symbolic constants (about 700), whose names begin with the lowercase letters vb. Excel VBA adds additional symbolic constants (1266 of them) that begin with the letters xl. We will encounter many of these constants throughout the book.

Among the most commonly used VBA constants are vbCrLf, which is equivalent to a carriage return followed by a line feed, and vbTab, which is equivalent to the tab character.

Enums

Microsoft has recently introduced a structure into VBA to categorize the plethora of symbolic constants. This structure is called an enum , which is short for enumeration. A list of enums can be obtained using my Object Model Browser software. For instance, among Excel's 152 enums, there is one for the fill type used by the AutoFill method, defined as follows:

Enum XlAutoFillType
     xlFillDefault = 0
     xlFillCopy = 1
     xlFillSeries = 2
     xlFillFormats = 3
     xlFillValues = 4
     xlFillDays = 5
     xlFillWeekdays = 6
     xlFillMonths = 7
     xlFillYears = 8
     xlLinearTrend = 9
     xlGrowthTrend = 10
End Enum

(The Excel documentation incorrectly refers to this enum as XlFillType.) Note that enum names begin with the letters Xl (with an uppercase X ).

Thus, the following line of code will autofill the first seven cells in the first row of the active sheet with the days of the week, assuming that the first cell contains the word Monday:

ActiveSheet.Range("A1").AutoFill ActiveSheet.Range("A1:G1"), xlFillDays

This is far more readable than:

ActiveSheet.Range("A1").AutoFill ActiveSheet.Range("A1:G1"), 5

Note that this enum is built in, so we do not need to add it to our programs in order to use these symbolic constants. (We can create our own enums, but this is generally not necessary in Excel VBA programming, since Excel has done such a good job of this for us.)

As another example, the built-in enum for the constant values that can be returned when the user dismisses a message box (by clicking on a button) is:

Enum VbMsgBoxResult
     vbOK = 1
     vbCancel = 2
     vbAbort = 3
     vbRetry = 4
     vbIgnore = 5
     vbYes = 6
     vbNo = 7
End Enum

For instance, when the user hits the OK button on a dialog box (assuming it has one), VBA returns the value vbOK. Certainly, it is a lot easier to remember that VBA will return the symbolic constant vbOK than to remember that it will return the constant 1. (We will discuss how to get and use this return value later.)

VBA also defines some symbolic constants that are used to set the types of buttons that will appear on a message box. These are contained in the following enum (which includes some additional constants not shown):

Enum VbMsgBoxStyle
     vbOKOnly = 0
     vbOKCancel = 1
     vbAbortRetryIgnore = 2
     vbYesNoCancel = 3
     vbYesNo = 4
     vbRetryCancel = 5
End Enum

To illustrate, consider the following code:

If MsgBox("Proceed?", vbOKCancel) = vbOK Then

   ' place code to execute when user hits OK button

Else

   ' place code to execute when user hits any other button

End If

In the first line, the code MsgBox("Proceed?", vbOKCancel) causes Excel to display a message box with an OK button and a Cancel button and the message "Proceed?", as shown in Figure 5-1.

Figure 5-1. Example message box

If the user clicks the OK button, Excel will return the constant value vbOK; otherwise it will return the value vbCancel. Thus, the If statement in the first line will distinguish between the two responses. (We will discuss the If statement in detail in Chapter 8. Here, we are interested in the role of symbolic constants.)

In case you are not yet convinced of the value of symbolic constants, consider the following enum for color constants:

Enum ColorConstants
     vbBlack = 0
     vbBlue = 16711680
     vbMagenta = 16711935
     vbCyan = 16776960
     vbWhite = 16777215
     vbRed = 255
     vbGreen = 65280
     vbYellow = 65535
End Enum

Consider which you'd rather type, this:

ATextBox.ForeColor = vbBlue

or this:

ATextBox.ForeColor = 16711680

Need I say more?