Page 175 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 161

AUTOMATING ACTIONS WITH MACROS 9

UNDERSTANDING VBA (CONTINUED)

Constants
Constants, as the name implies, represent specific Example:
values that do not change within your code. You declare Const SalesTax As String = ".075"
constants using the Const keyword. If you do not
specify the data type for the constant, Excel treats the If your state raises the sales tax, you simply have to
constant as a variant. modify the constant value — .075 in the example — and
not each calculation. Using constants helps eliminate
Using constants enables you to have only one place in potential errors that can arise from mistyping a value.
the code to modify if the value of the constant changes.
For example, suppose that you have the following
constant declaration:

Operators
VBA provides several different operators that you can comparison, and logical. You should find most of these
use in your code. You can group these operators into operators quite familiar.
four general categories: arithmetic, concatenation,

Arithmetic Operators
VBA accepts seven different arithmetic operators. When within the parentheses and for the rest of the
a statement, a single VBA expression or definition, statement. In the following statement, Val2 is added to
contains multiple arithmetic operators, VBA uses Val3, and then the sum is multiplied by Val1:
precedence order to determine how to evaluate the
statement. For example, VBA always calculates Example:
exponents first. The only exception to the precedence Value = Val1 * (Val2 + Val3)
order are parentheses; when parentheses separate
portions of a statement, VBA evaluates the contents of The following table lists arithmetic operators in their
the parentheses first, still using the precedence order precedence order:

OPERATOR PRECEDENCE PURPOSE
^ 1 Raises the number before the operator to the power of the
exponent — for example, 2^3 = 8.
- (before a number) 2 Denotes a negative value.
* 3 Multiplies two numerical values.
/ 3 Divides two numerical values and returns the entire result,
including any decimal places — for example, 5 / 2 = 2.5.
\ 3 Divides two numerical values and returns the integer portion of
the result — for example, 5 \ 2 = 2.
Mod 4 Divides two numeric values and returns the remainder — for
example, 5 MOD 2 = 1.
+ 5 Adds two numerical expressions.
- (minus sign) 5 Finds the difference between two numerical expressions by
subtracting the second expression from the first.

161