MS-Excel / General Formatting

---

Working with VBA Operators

You've already seen the first of VBA's operators: the assignment operator, which is just the humble equals sign (=). You use the assignment operator to assign the result of an expression to a variable.

Bear 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. This seems like obvious behavior, but it's the source of a handy trick that you'll use quite often. In other words, you can use the current value of whatever is on the left side of the equation as part of the expression on the right side. For example, consider the following code fragment:

currentYear = 2007
currentYear = currentYear + 1

The first statement assigns the value 2007 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 2007, and adds 1 to it. The result is 2008 and that is what's stored in currentYear when all is said and done.

VBA has a number of different operators that you use to combine functions, variables, and values in a VBA expression. These operators work much like the operators-such as addition (+) and multiplication (*)-that you use to build formulas in Excel worksheets and Word tables. VBA operators fall into five general categories: arithmetic, concatenation, comparison, logical, and miscellaneous.

Arithmetic Operators

VBA's arithmetic operators are similar to those you use to build Excel formulas. Table lists below each of the arithmetic operators you can use in your VBA statements.

The VBA Arithmetic Operators

Operator	Name	Example	Result
+	Addition	10+5	15
-	Subtraction	10-5	5
-	Negation	-10	-10
*	Multiplication	10*5	50
/	Division	10/5	2
\	Integer division	11\5	2
^	Exponentiation	10^5	100000
Mod	Modulus (remainder)	10 Mod 5	0

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
Here's a less trivial example:
Dim strFirst As String
Dim strLast As String
strFirst = "Jame"
strLast = "Mikey"
MsgBox strFirst & " " & strLast

This code declares two String variables names strFirst and strLast, and then assigns them the string literals "Jame" and "Mikey", respectively. A MsgBox function uses & to combine the two strings with a space in between.

You can also use & to combine not just String operands, but also 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). Table below summarizes VBA's comparison operators.

The VBA Comparison Operators

Operator	Name	Example	Result
=	Equal to	10=5	False
>	Greater than	10>5	True
<	Less than	10<5	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. Table below summarizes VBA's logical operators.

The VBA Logical Operators

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	1False 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.