Working with VBA Operators

     

You've already seen the first of VBA's operators: the assignment operator , which is just the humble equals sign (=). You use the assignment operator to assign the result of an expression to a variable (or, as you'll see in Chapter 5, to an object property).

Bear in mind that VBA always derives the result of the right side of the equation (that is, the expression) before it modifies the value of the left side of the equation. This seems like obvious behavior, but it's the source of a handy trick that you'll use quite often. In other words, you can use the current value of whatever is on the left side of the equation as part of the expression on the right side. For example, consider the following code fragment:

 currentYear = 2004 currentYear = currentYear + 1 

The first statement assigns the value 2004 to the currentYear variable. The second statement also changes the value stored in the currentYear , but it uses the expression currentYear + 1 to do it. This looks weird until you remember that VBA always evaluates the expression first. In other words, it takes the current value of currentYear , which is 2004, and adds 1 to it. The result is 2005 and that is what's stored in currentYear when all is said and done.

Consider, again, the generic assignment statement:

  variable  =  expression  

Because of VBA's evaluate-the-expression-and- then -store-the-result behavior, this assignment statement should not be read like this:

  variable  is the same as  expression  . 

Instead, it makes more sense to think of it in either of the following terms:

  variable  is set to  expression   variable  assumes the value given by  expression  . 

This helps to reinforce the important concept that the expression result is being stored in the variable.

VBA has a number of different operators that you use to combine functions, variables , and values in a VBA expression. These operators work much like the operators ”such as addition (+) and multiplication (*) ”that you use to build formulas in Excel worksheets and Word tables. VBA operators fall into five general categories: arithmetic, concatenation, comparison, logical, and miscellaneous.

Arithmetic Operators

VBA's arithmetic operators are similar to those you use to build Excel formulas. Table 4.1 lists each of the arithmetic operators you can use in your VBA statements.

Table 4.1. The VBA Arithmetic Operators

Operator

Name

Example

Result

+

Addition

10+5

15

-

Subtraction

10-5

5

-

Negation

-10

-10

*

Multiplication

10*5

50

/

Division

10/5

2

\

Integer division

11\5

2

^

Exponentiation

10^5

100000

Mod

Modulus

10 Mod 5

The Mod operator works like Excel's MOD() worksheet function. In other words, it divides one number by another and returns the remainder. Here's the general form to use:

  result  =  dividend  Mod  divisor  

Here, dividend is the number being divided; divisor is the number being divided into dividend ; and result is the remainder of the division. For example, 16 Mod 5 returns 1 because 5 goes into 16 three times with a remainder of 1.

The Concatenation Operator

You use the concatenation operator ( & ) to combine text strings within an expression. One way to use the concatenation operator is to combine string literals. For example, consider the following expression:

 "soft" & "ware" 

The result of this expression is the following string:

 software 

You can also use & to combine not just String operands, but numeric and Date operands, too. Just remember that the result will always be of the String data type. For more information on the concatenation operator, check out the section "Working with String Expressions" later in this chapter.

Comparison Operators

You use the comparison operators in an expression that compares two or more numbers , text strings, variables, or function results. If the statement is true, the result of the formula is given the logical value True (which is equivalent to any nonzero value). If the statement is false, the formula returns the logical value False (which is equivalent to 0). Table 4.2 summarizes VBA's comparison operators.

Table 4.2. The VBA Comparison Operators

Operator

Name

Example

Result

=

Equal to

10=5

False

>

Greater than

10>5

True

<

Less than

10<5

False

>=

Greater than or equal to

"a">="b"

False

<=

Less than or equal to

"a"<="b"

True

<>

Not equal to

"a"<>"b"

True

Logical Operators

You use the logical operators to combine or modify True/False expressions. Table 4.3 summarizes VBA's logical operators. I provide more detail about each operator later in this chapter (see "Working with Logical Expressions").

Table 4.3. The VBA Logical Operators

Operator

General Form

What It Returns

And

Expr1 And Expr2

True if both Expr1 and Expr2 are True; False otherwise .

Or

Expr1 Or Expr2

True if at least one of Expr1 and Expr2 are True; False otherwise.

Xor

Expr1 Xor Expr2

False if both Expr1 and Expr2 are True or if both Expr1 and Expr2 are False; True otherwise.

Not

Not Expr

True if Expr is False; False if Expr is True.



Absolute Beginner's Guide to VBA
Absolute Beginners Guide to VBA
ISBN: 0789730766
EAN: 2147483647
Year: 2003
Pages: 146

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net