Microsoft Office Tutorials and References
In Depth Information
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:
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