How to Use VBA Assignment Statements in Excel 2016
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.”
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 = 2016
Expressions can be as complex as you need them to be; use the line continuation character (a space followed by an underscore) to make lengthy expressions 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
In what crazy universe is z equal to itself plus 1? Answer: No known universe. In this case, the assignment statement (when executed) increases the value of z by 1. So if z is 12, executing the statement makes z equal to 13. Just remember that an assignment uses the equal sign as an operator, not a symbol of equality.
Smooth operators
Operators play major roles in VBA. Besides the equal-sign operator, VBA provides several operators. These should be familiar to you because they are the same operators used in worksheet formulas (except for the Mod operator).
| 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 |
When you’re writing an Excel formula, you do modulo arithmetic by using the MOD function. For example, the following formula returns 2 (the remainder when you divide 12 by 5):
=MOD(12,5)
In VBA, the Mod operator is used like this (and z has a value of 2):
z = 12 Mod 5
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.
VBA also provides a full set of logical operators. Of these, Not, And, and Or are most frequently used.
| 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 division come next, followed by addition and subtraction. You can use parentheses 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 the preceding 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 precedence. If you answered 16, read this: The multiplication operation (5 * y) is performed first, and that result is added to x.
Many programmers tend to use parentheses even when they aren’t required. For example, in real life that last assignment statement could be written like this:
z = x + (5 * y)
Don’t be shy about using parentheses even if they aren’t required — especially if doing so makes your code easier to understand. VBA doesn’t care if you use extra parentheses.
