Operators in VBA

Operators in VBA

The operators in VBA include symbols such as +, −, *, /, =, >, < as well as keywords such as And, Or, Not. Normally, operators do not require a lengthy explanation. This section gives a brief overview as to which operators are available and some of their special characteristics.

Arithmetic operators are used for carrying out calculations. Whereas +, −, *, and / need no further explanation, the operators \ and Mod are more interesting: \ carries out whole-number division. The two arguments are first rounded to whole numbers if they are not whole numbers already. Thus 5\2 yields the same result as 5.5\1.5, namely, 2. The operator Mod likewise carries out whole-number division in the same way that \ does. However, it returns the remainder of the division. Thus, 21 Mod 5 returns the remainder 1.

There are two operators available for the concatenation of character strings: + can deal only with character strings, joining, say, "ab"+"cd" to yield "abcd". The operator & can do business with numbers as well, turning them into strings. Thus "12" & "3" returns "123".

Comparison Operators

For comparing two values or character strings there is the operator =. For example, If a = 3 then …. Additionally, there are two special operators, Is and Like:

Is serves to compare two object variables (obj1 Is obj2). It should return True if both variables refer to the same object. Unfortunately, this operator does not always work correctly, both in Excel 2000 and Excel 2002. Only comparisons with the key word Nothing always return correct results (If x Is Nothing Then …).

Dim a As Object, b As Object

Set a = ActiveWindow

Set b = ActiveWindow

If a Is b Then ... 'does not work!

Like allows for pattern recognition in character strings. In the search pattern (to the right of Like) one can use the wild cards ? (an arbitrary character) and * (an arbitrary number of characters). Like is case sensitive! Example: "Coffepot" Like "C?f*ot" returns True.

Pointer / If you are working with large numbers of character strings, the instruction Option Compare Text at the beginning of a module is often useful. With it uppercase and lowercase letters are not distinguished in ordinary searches, while special symbols are correctly ordered. See Chapter 5.

Logical Operators

Logical operators make it possible to link several conditions. The expression aOrb returns True if at least one of the two component conditions a and b is True. The operator And is restrictive, requiring that both conditions be True simultaneously. The operator Xor (exclusive or) tests whether one or the other, but not both, of two conditions is true. Thus Xor returns the result True precisely when either a is True and b is False or b is True and a is False. More seldom used are Imp (implication) and Eqv (equivalence). Imp returns True unless a=True and b=False, while Eqv returns True if a and b have the same truth value.

Caution / VBA seems to incorporate no optimization in the evaluation of conditionals. A test of the form If x>=0 And Sqr(x)<3 leads in the case of x a negative number to an error. (In many programming languages the second part of the test will simply not be carried out if the first part has already tested false, since if on part of an And conditional is false, then the whole expressions is false.)

Many VBA and Excel properties contain bit-coded status information. A typical example is the Attributes property of the File object in the Scripting library (see also the following chapter). Possible attributes are defined in the FileAttribute constants:

NAME / VALUE
Normal / 0
ReadOnly / 1
Hidden / 2
System / 4
… / …

The values of these constants correspond to the powers of 2 (20, 21, 22, 23, etc.), that is, in binary representation, (0001, 0010, 0100, 1000). In a hidden, write-protected system file, Attributes has the value 7 (that is, 1 + 2 + 4).

The operators And and Or are ideally suited for working with such constants. For example, if you wish to set several attributes simultaneously, you simply join the constants with Or (alternatively, you could simply use the operator +):

myfile.Attributes = ReadOnly Or System

If you wish to test whether a particular attribute has been set, then use And:

If (myfile.Attributes And System) > 0 Then ' it is a system file

Operator Hierarchy

The operators do not all have the same precedence. In the expression a+b*c first b*c is computed and then the summation with a. At the highest level of the operator hierarchy are the arithmetic operators for numbers and the concatenation operators for character strings. After them come the comparison operators, and finally the logical operators. The two definition operators play no role in the evaluation of expressions. A fully ranked listing of all operators can be found in the online help under "Operators: Calculation operators in formulas."

Syntax Summary

ARITHMETIC OPERATORS
− / minus sign
+ − * / / basic operations
^ / exponentiation (3^2 yields 9)
\ / integer division
Mod / modulo operator (remainder under integer division)
OPERATORS FOR CONCATENATING CHARACTER STRINGS
+ / only for strings of characters
numbers are automatically converted into characters
COMPARISON OPERATORS
= / equal to
unequal
< <= / less than, less than or equal to
> >= / greater than, greater than or equal to
Is / refer to the same object
Like / pattern comparison
LOGICAL OPERATORS
And / logical And
Or / logical Or
Xor / exclusive Or (a or b, but not both)
Imp / implication (if a is true, then b must also be true)
Eqv / equivalence (a and b must be the same)
Not / logical negation
DEFINITION OPERATORS
= / allocation of variables and properties
:= / allocation of named parameters in procedure calls