Microsoft Office Tutorials and References
In Depth Information
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
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 topic.
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.
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:
xlFillDefault = 0
xlFillCopy = 1
xlFillSeries = 2
xlFillFormats = 3
xlFillValues = 4
xlFillDays = 5
xlFillWeekdays = 6
xlFillMonths = 7
xlFillYears = 8
xlLinearTrend = 9
xlGrowthTrend = 10