Microsoft Office Tutorials and References

In Depth Information

**Using Assignment Expressions**

Date variables display dates according to your system’s short date format, and times

appear according to your system’s time format (either 12 or 24 hours). You can modify

these system settings by using the Regional and Language Options dialog box in the

Windows Control Panel. See Chapter 6 for more information on working with dates

and times.

Using Assignment Expressions

An
assignment expression
is a VBA instruction that evaluates an expression and assigns the result

to a variable or an object. An
expression
is a combination of keywords, operators, variables, and

constants that yields a string, number, or object. An expression can perform a calculation,

manipulate characters, or test data.

If you know how to create formulas in Excel, you’ll have no trouble creating expressions in VBA.

With a worksheet formula, Excel displays the result in a cell. Similarly, you can assign a VBA

expression to a variable or use it as a property value.

VBA uses the equal sign (
=
) as its assignment operator. Note the following examples of

assignment statements. (The expressions are to the right of the equal sign.)

x = 1

x = x + 1

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

MultiSheets = True

Expressions often use functions. These can be VBA’s built-in functions, Excel’s worksheet

functions, or custom functions that you develop in VBA. I discuss VBA’s built-in functions later in this

chapter.

Operators play a major role in VBA. Familiar operators describe mathematical operations,

including addition (
+
), multiplication (
*
), division (
/
), subtraction (
–
), exponentiation (
^
), and string

concatenation (
&
). Less familiar operators are the backslash (
\
) that’s used in integer division

and the
Mod
operator that’s used in modulo arithmetic. The
Mod
operator returns the remainder

of one integer divided by another. For example, the following expression returns
2
:

17 Mod 3

You may be familiar with the Excel MOD function. Note that in VBA,
Mod
is an operator, not a

function.

VBA also supports the same comparative operators used in Excel formulas: equal to (
=
), greater

than (
>
), less than (
<
), greater than or equal to (
>=
), less than or equal to (
<=
), and not equal to

(
<>
). Additionally, VBA provides a full set of logical operators, as shown in Table 24-2. Refer to

the Help system for additional information and examples of these operators.