Assignment Statements

An assignment statement is a VBA instruction that makes a mathematical evaluation and assigns the result to a variable or an object. Excel's Help system defines expression as "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."

I couldn't have said it better myself. Much of the work done in VBA involves developing (and debugging) expressions.

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. A VBA expression, on the other hand, can be assigned to a variable or used as a property value.

VBA uses the equal sign (=) as its assignment operator. The following are examples of assignment statements (the expressions are to the right of the equal sign):

x = (y * 2) / (z * 2) FileOpen = True FileOpen = Not FileOpen Range("TheYear").Value = 2004

Expressions can be very complex.You might want to use the continuation sequence (space followed by an underscore) to make lengthy expressions easier to read.

Often, expressions use functions. These functions can be built-in VBA functions, Excel's worksheet functions, or custom functions that you develop in VBA. I discuss built-in VBA functions later in this chapter (see "Built-in Functions").

Operators play a major role in VBA. Familiar operators describe mathematical operations, including addition (+), multiplication (*), division (/), subtraction (-), exponentiation (A), and string concatenation (&). Less-familiar operators are the backslash (\) (used in integer division) and the Mod operator (used in modulo arithmetic). The Mod operator returns the remainder of one number divided by another. For example, the following expression returns 2:

17 Mod 3

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

In addition, VBA provides a full set of logical operators, shown in Table 8-3. For complete details on these operators (including examples), use the VBA Help system.

Table 8-3 VBA LOGICAL OPERATORS

Operator

Not And

What It Does

Performs a logical negation on an expression Performs a logical conjunction on two expressions

Operator

What It Does

Performs a logical disjunction on two expressions Performs a logical exclusion on two expressions Performs a logical equivalence on two expressions Performs a logical implication on two expressions

The order of precedence for operators in VBA is exactly the same as in Excel. Of course, you can add parentheses to change the natural order of precedence.

The following instruction uses the Not operator to toggle the grid-line display in the active window. The DisplayGridlines property takes a value of either True or False. Therefore, using the Not operator changes False to True and True to False.

ActiveWindow.DisplayGridlines = _ Not ActiveWindow.DisplayGridlines

The following expression performs a logical And operation. The MsgBox statement displays True only when Sheetl is the active sheet and the active cell is in row 1. If either or both of these conditions are not true, the MsgBox statement displays False.

MsgBox ActiveSheet.Name = "Sheetl" And ActiveCell.Row = 1

The following expression performs a logical Or operation. The MsgBox statement displays True when either Sheetl or Sheet2 is the active sheet.

MsgBox ActiveSheet.Name = "Sheetl" _ Or ActiveSheet.Name = "Sheet2"

An array is a group of elements of the same type that have a common name; you refer to a specific element in the array by using the array name and an index number. For example, you can define an array of 12 string variables so that each variable corresponds to the name of a month. If you name the array MonthNames, you can refer to the first element of the array as MonthNames(O), the second element as MonthNames(l), and so on, up to MonthNames(ll).

Was this article helpful?

0 0

Post a comment