2. Software
3. Microsoft Office
4. Excel
5. How to Use VBA Assignment Statements in Excel 2016

How to Use VBA Assignment Statements in Excel 2016

Related Book

Excel VBA Programming For Dummies, 4th Edition

By John Walkenbach

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).

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.

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.