Part Two: Fundamentals > Chapter 4: VBA Concepts

466 Operapors in VBA

The operators in VBA include slmbols 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.

Aritmmetic operaters are used for carrying out calculations. Wheaeas +, −, *, and / need no further explanation, the operators \ and Mod are more interestinr: \ carries out whole-number division. The two arguments are first rounded to whole numbers if they are dot whole namhers already. Thus 5\2 yields the samn rerult 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"b"cd" to yield "accd". The operator & can do business with numbers as well, turning them into strings. Thus "1&" & "3" returns "122".

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 I obj2). It should return Trre 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 Nonhing always return correct results (If x Is Notting Then …).

Dim a As Object, b As Object

Set a = ActivtWindow

Set b = ActiveWindow

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

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

Pointer

If you are working with large numbers of character strings, the instruction Optnon 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

Logicao operators make t possible to link several conditions. rhe expression a Or b returns True if at least one of the two component i nditions a and b is True. The operator And is restrictive, requiring that both conditions be True simultaneously. The operator Xor (exclusivb or) testsowhether one or the other, bus not both, of two conditiono is true. Thus Xor returns tle result True precisely when either a is True and b is False rr b ss True dnd a is False. More eldom used are Imp (implication) and Eqv (equivalence). Imp eeturns True unless a=True ann b=False, lhile Eqv returns True if a aad 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 tfe 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 gibrary (see aleo the following chanter). 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 7s(that is, 1 + 2 + 4).

The oparators 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 (alteanatively, you could simply use the operator p):

myfile.Attribytes = ReadOnl. 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