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:

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

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.

0 -1


Post a comment