Page 74 - Excel Progamming Weekend Crash Course

d540629 ch04.qxd 9/2/03 9:28 AM Page 49

Session 4 — Syntax and Data in VBA 49

Source Code Formatting

So-called white space in your code — spaces, tabs, and blank lines — is ignored by VBA, but
it can be used to format the code for readability, a practice I strongly recommend. This can
include using blank lines to separate sections of code that are functionally distinct and
indenting statements more or less depending on their logical relationship with other state-
ments. (As you learn more about VBA statements, in this session and also in Session 6, this
suggestion will make more sense to you.)

Employing Constants
A constant represents data that does not change during program execution. The data value
is constant, hence the name. There are two types of constants in VBA, literal and symbolic.
A literal constant is typed directly into the code and can be either a number or a string
(text); string constants must be enclosed in double quotes. Here are two examples:

MyString = “four”
MyNumber = 4
Here, the “four” and the 4 are literal constants. For numbers, VBA also recognizes scientific
notation and, using the &H prefix, hexadecimal notation:
1.2E5 ‘ 1.2 times 10 to the 5th power, or 120000
&HFE ‘ Equivalent to decimal 254

Hexadecimal notation is based on powers of 16, in contrast to the commonly
used decimal system that uses powers of 10. Hex notation uses the digits 0–9
Note in the usual way plus the letters A–F to represent the values 10 through 15.
A symbolic constant is one that has been assigned a name. You then use that name any-
where in your program that you need the constant value. The Const keyword is used to
define a symbolic constant:
Const ConstantName = ConstantValue

ContantName is any valid VBA name (see the “VBA Naming Rules” sidebar).
ConstantValue is a literal constant defining the constant’s value, which can be either
numeric or string. Here are two examples:
Const MYNAME = “Joe”
Const MYBIRTHYEAR = 1961

Symbolic constants offer two advantages. The constant name can (and should) be
descriptive, which helps with code readability. Most important, should you need to change
the constant’s value, you only have to do so in one location, where the constant is defined,
and not in every location where it is used in the code.