VBA Programming Series...26

Using Assignment Statements

An assignment statement is a VBA statement that assigns the result of an expression to a variable or an object. Excel’s Help system defines the term expression as

. . . a combination of keywords, operators, variables, and constants that yields a string, number, or object. An expression can be used to perform a calculation, manipulate characters, or test data.

I couldn’t have said it better myself, so I won’t even try.

Much of your work in VBA involves developing (and debugging) expressions. If you know how to create formulas in Excel, you’ll have no trouble creating expressions. With a worksheet formula, Excel displays the result in a cell. A VBA expression, on the other hand, can be assigned to a variable.

Assignment statement examples

In the assignment statement examples that follow, the expressions are to the right of the equal sign:

x = 1

x = x + 1

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

HouseCost = 375000

FileOpen = True

Range(“TheYear”).Value = 2012

Expressions can be as complex as you need them to be; use the line continu-ation character (a space followed by an underscore) to make lengthy expres-sions easier to read.

Often, expressions use functions: VBA’s built-in functions, Excel’s worksheet functions, or functions that you develop with VBA. 

About that equal sign

As you can see in the preceding example, VBA uses the equal sign as its assignment operator. You’re probably accustomed to using an equal sign as a mathematical symbol for equality. Therefore, an assignment statement like the following may cause you to raise your eyebrows:

z = z + 1

How can the variable z be equal to itself plus 1? Answer: It can’t. In this case, the assignment statement is increasing the value of z by 1. Just remember that an assignment uses the equal sign as an operator, not a symbol of equality.

Smooth operators

Operators play a major role in VBA. Besides the equal sign operator (dis-cussed in the previous section), VBA provides several other operators. Table lists these operators, with which you are familiar from your worksheet formulas experience.

Function		Operator Symbol
Addition		+
Multiplication		*
Division		/
Subtraction		-
Exponentiation		^
String concatenation		&
Integer division (the result is always an integer)		\
Modulo arithmetic (returns the remainder of a division operation)		Mod

The term concatenation is programmer speak for “join together.” Thus, if you concatenate strings, you are combining strings to make a new and improved string.

As shown in Table, VBA also provides a full set of logical operators.

Consult the Help system for complete details.

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 precedence order for operators in VBA is exactly the same as in Excel formulas. Exponentiation has the highest precedence. Multiplication and divi-sion come next, followed by addition and subtraction. You can use parenthe-ses to change the natural precedence order, making whatever’s sandwiched in parentheses come before any operator. Take a look at this code:

x = 3

y = 2

z = x + 5 * y

When this code is executed, what’s the value of z? If you answered 13, you get a gold star that proves you understand the concept of operator prece-dence. If you answered 16, read this: The multiplication operation (5 * y) is performed first, and that result is added to x. If you answered something other than 13 or 16, I have no comment.

By the way, I can never remember how operator precedence works, so I tend to use parentheses even when they aren’t required. For example, in real life I would write that last assignment statement like this:

z = x + (5 * y)

Don’t be shy about using parentheses even if they aren’t required — espe-cially if doing so makes your code easier to understand. VBA doesn’t care if you use extra parentheses.