Microsoft Office Tutorials and References

In Depth Information

x = 1

x = x + 1

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

MultiSheets = True

Expressions often use functions. These can be VBA's built-in functions, Excel's worksheet functions, or custom

functions that you develop in VBA. I discuss VBA's built-in functions later in this chapter.

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

(+), multiplication (*), division (/), subtraction (–), exponentiation (^), and string concatenation (&). Less famil-

iar operators are the backslash (\) that's used in integer division and the Mod operator that's used in modulo

arithmetic. The Mod operator returns the remainder of one integer divided by another. For example, the follow-

ing expression returns
2:

17 Mod 3

You may be familiar with the Excel MOD function. Note that in VBA, Mod is an operator, not a function.

VBA also supports the same comparative 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 (<>). Additionally, VBA

provides a full set of logical operators, as shown in Table 25-2. Refer to the Help system for additional informa-

tion and examples of these operators.

Table 25-2: VBA Logical Operators

Operator

What It Does

Not

Performs a logical negation on an expression

And

Performs a logical conjunction on two expressions

Or

Performs a logical disjunction on two expressions

Xor

Performs a logical exclusion on two expressions

Eqv

Performs a logical equivalence on two expressions

Imp

Performs a logical implication on two expressions

The order of precedence for operators in VBA exactly matches that in Excel. Of course, you can add paren-

theses to change the natural order of precedence.

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

ing formula returns 25: