We Have Already Discussed the Fact That Comments Are Important. Any Text That Follows An

Comments

We have already discussed the fact that comments are important. Any text that follows an apostrophe is considered a comment and is ignored by Excel. For example, the first line in the following code is a comment, as is everything following the apostrophe on the third line:

' Declare a string variableDimWksName as StringWksName = Activesheet.Name ' Get name of active sheet

When debugging code, it is often useful to temporarily comment out lines of code so they will not execute. The lines can subsequently be uncommented to restore them to active duty. The CommentBlock and UncommentBlock buttons, which can be found on the Edit toolbar, will place or remove comment marks from each currently selected line of code and are very useful for commenting out several lines of code in one step. (Unfortunately, there are no keyboard shortcuts for these commands, but they can be added to a menu and given menu accelerator keys.)

5.2 Line Continuation

The very nature of Excel VBA syntax often leads to long lines of code, which can be difficult to read, especially if we need to scroll horizontally to see the entire line. For this reason, Microsoft recently introduced a line-continuation character into VBA. This character is the underscore, which must be preceded by a space and cannot be followed by any other characters (including comments). For example, the following code:

ActiveSheet.Range("A1").Font.Bold = _True

is treated as one line by Excel. It is important to note that a line continuation character cannot be inserted in the middle of a literal string constant, which is enclosed in quotation marks.

5.3 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 "DonnaSmith" 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 InvoicePathin our code with the string "d:\Invoices\". Thus, InvoicePathis 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 InvoicePathto:

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.

5.3.1 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:

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

End Enum