Microsoft Office Tutorials and References

In Depth Information

**Assignment Statements**

VBA uses the equal sign (
=
) as its assignment operator. The following are examples of

assignment statements (the expressions are to the right of the equal sign):

x = 1

x = x + 1

x = (y * 2) / (z * 2)

FileOpen = True

FileOpen = Not FileOpen

Range(“TheYear”).Value = 2010

Expressions can be very complex. You may want to use the line continuation sequence

(space followed by an underscore) to make lengthy expressions easier to read.

Often, expressions use functions. These functions can be built-in VBA functions, Excel’s

worksheet functions, or custom functions that you develop in VBA. I discuss built-in VBA functions

later in this chapter (see the upcoming section “Built-in Functions”).

Operators play a major role in VBA. Familiar operators describe mathematical operations,

including addition (
+
), multiplication (
*
), division (
/
), subtraction (
–
), exponentiation (
^
), and string

concatenation (
&
). Less familiar operators are the backslash (
\
) (used in integer division) and the

Mod
operator (used in modulo arithmetic). The
Mod
operator returns the remainder of one

number divided by another. For example, the following expression returns
2
:

17 Mod 3

VBA also supports the same comparison operators used in Excel formulas: equal to (
=
), greater than

(
>
), less than (
<
), greater than or equal to (
>=
), less than or equal to (
<=
), and not equal to (
<>
).

With one exception, the order of precedence for operators in VBA is exactly the same as in Excel

(see Table 8-3). And, of course, you can use parentheses to change the natural order of precedence.

The negation operator (a minus sign) is handled differently in VBA. In Excel, the

following formula returns 25:

=-5^2

In VBA, x equals –25 after this statement is executed:

x = -5 ^ 2

VBA performs the exponentiation operation first and then applies the negation

operator. The following statement returns 25:

x = (-5) ^ 2