Operators

The Assignment Operator
We have already seen the first of VBA's operators: the assignment operator, which is just the equals sign (=). It is used to assign the result of an expression to a variable.
Always keep in mind that VBA always derives the result of the right side of the equation (that is, the expression) before it modifies the value of the left side of the equation.

For example, consider the following code fragment:

currentYear = 2004
currentYear = currentYear + 1

The first statement assigns the value 2004 to the currentYear variable. The second statement also changes the value stored in the currentYear, but it uses the expression currentYear + 1 to do it. This looks weird until you remember that VBA always evaluates the expression first. In other words, it takes the current value of currentYear, which is 2004, and adds 1 to it. The result is 2005 and that is what's stored in currentYear when all is said and done.

Arithmetic Operators
VBA's arithmetic operators are similar to those you use to build Excel formulas.

Operator
Name
Example
Result
-
Subtraction
13-2
11
-
Negation
-5
-5
*
Multiplication
3*2
6
/
Division
8/2
4
\
Integer Division
9\2
4
^
Exponential
2^3
8
Mod
Modulus
9 Mod 2
1

The Mod operator works like Excel's MOD() worksheet function. In other words, it divides one number by another and returns the remainder. Here's the general form to use:

result = dividend Mod divisor

Here, dividend is the number being divided; divisor is the number being divided into dividend; and result is the remainder of the division. For example, 16 Mod 5 returns 1 because 5 goes into 16 three times with a remainder of 1.

The Concatenation Operator
You use the concatenation operator (&) to combine text strings within an expression. One way to use the concatenation operator is to combine string literals. For example, consider the following expression:

"soft" & "ware"

The result of this expression is the following string:

software

You can also use & to combine not just String operands, but numeric and Date operands, too. Just remember that the result will always be of the String data type.

Comparison Operators
You use the comparison operators in an expression that compares two or more numbers, text strings, variables, or function results. If the statement is true, the result of the formula is given the logical value True (which is equivalent to any nonzero value). If the statement is false, the formula returns the logical value False (which is equivalent to 0).

Operator
Name
Example
Result
=
Equal to
3=2
False
>
Greater than
3>2
True
<
Less than
3<2
False
>=
Greater than or equal to
"a">="b"
False
<=
Less than or equal to
"a"<="b"
True
<>
Not equal to
"a"<>"b"
True

Logical Operators
You use the logical operators to combine or modify True/False expressions.

Operator
General Form
What It Returns
And
Expr1 And Expr2
True if both Expr1 and Expr2 are True; False otherwise.
Or
Expr1 Or Expr2
True if at least one of Expr1 and Expr2 are True; False
otherwise.
Xor
Expr1 Xor Expr2
False if both Expr1 and Expr2 are True or if both Expr1 and Expr2
are False; True otherwise.
Not
Not Expr
True if Expr is False; False if Expr is True.