Logical Operators

These perform a logical bit-by-bit conjunction on two expressions. They use pure binary math to decide the result.

And Operator

This works on the basis that both values have to be True (nonzero). The value of True in VBA is actually ‚ 1. The following will give the result False because both values have to be True for an overall True value when the And operator is used:

 Msgbox True And False 

Numbers can also be And ed together. This is done on a binary basis. The top row of the following table represents the value of each binary bit going from bit 7 to bit 0. The two rows below it represent the binary equivalents of the numeric numbers on the right of the table (column n ). The final row shows both the binary and numeric equivalents when the two numbers are And ed together. Each bit pair uses an And operator to achieve the final result on the bottom row.

128	64	32	16	8	4	2	1	n
	1		1		1			84
1			1				1	145
			1					16

Each column of this table shows a binary bit based on an 8-bit number. The bit values are shown in bold across the top. The right-hand column ( n ) contains the actual decimal values.

Bits are counted from the right to left, starting at bit 0 and ending at bit 7 for a single byte number. Notice that the values of each bit increase in powers of 2. Bit 0 is represented by the value of 1, and bit 7 is represented by the value of 128. The first number is 84, so bit 6, bit 4, and bit 2 are all set. If you add 64 + 16 + 4, this comes to 84. The second number is 145, so bit 7, bit 4, and bit 0 are set. If you add 128 + 16 + 1, this comes to 145.

When a logical And is done on the two numbers, the result is 16. This is because the only bit where both numbers have a value is bit 4. This can be shown with the following example:

 MsgBox 84 And 145 

This will give the result of 16.

This strange binary arithmetic is generally used for testing whether bits are set within a number or for masking purposes. Masking sets the values of certain bits to True or False within a number. To do this, a ‚“mask ‚½ number is And ed with the target number and the bit in the mask will be set to the mask value. For example, if you want bit 7 to be set to 1, then you And your target number with 128 (bit 7 value) and bit 7 in the target number is then set to True, regardless of what values the other bits have.

Also, for example, you could have a variable that uses 8 bits to hold various information on something, almost like properties. Each bit may represent a certain setting. If bit 4 represents a certain value and you want to see if it is set, all you do is And it with 16, which is the binary number for bit 4. If the bit is set, it will give a value of 16; otherwise it will give a value of 0. This acts totally independently of values that the other bits are set to.

Not Operator

The Not operator performs a logical Not on two numbers or expressions. It basically inverts the bits within a number. If a bit is set to 0, then it becomes 1; and if it is set to 1, it becomes 0

 MsgBox Not (2 = 3) 

This will give the result True because 2 does not equal 3 (which is False), but the Not statement then inverts the bits and makes it True.

Or Operator

This works on the basis that two values can either be True (nonzero) or one can be True and the other False (zero). The following returns True because one of the values is True:

 MsgBox True Or False 

The following returns False because there is no True value:

 MsgBox False Or False 

It works on binary arithmetic on the basis that 1 and 1 make 1, 1 and 0 make 1, 0 and 1 make 1, and 0 and 0 make 0.

The top row of the following table represents the value of each binary bit going from bit 7 to bit 0. The two rows below it represent the binary equivalents of the numeric numbers on the right of the table (column n ). The final row shows both the binary and numeric equivalents when the two numbers are Or ed together. Each bit pair uses an Or operator to achieve the final result on the bottom line.

128	64	32	16	8	4	2	1	n
	1		1		1			84
1			1				1	145
1	1		1		1		1	213

Each column of the preceding table shows a binary bit based on an 8-bit number. The bit values are shown in bold across the top. The right-hand column contains the actual decimal numbers.

Bits are counted from right to left, starting at bit 0 and ending at bit 7 for a single byte number. Notice that the values of each bit increase in powers of 2. The first number is 84, so bit 6, bit 4, and bit 2 are all set. If you add 64 + 16 + 4, this comes to 84. The second number is 145, so bit 7, bit 4, and bit 0 are set. If you add 128 + 16 + 1, this comes to 145.

When a logical Or is done on the two numbers, the result is 213 (128 + 64 + 16 + 4 +1). This can be shown using the following VBA example:

 MsgBox 84 Or 145 

This will give the result of 213.

The Or operator is often used for masking purposes in graphics and also for combining two parameters. In Chapter 5, I discussed the message box you had to combine with vbExclamation and vbYesNo in order to get the correct icon and the correct buttons on the message box. Using a simple + operator to add together vbExclamation and vbYesNo together will result in the wrong value being set for the required flag. It is only by using Or that the correct result is achieved.

You also see use of Or in If statements, as in Chapter 4:

 If  x = 1  Or  y = 1 Then 

Xor Operator

Xor is very similar to Or except that True and True make False. Only True and False make True, but there must be one True and one False. Xor stands for E xclusive Or ‚ both values cannot both be True or False. The following gives the value True:

 MsgBox True Xor False 

The following gives the value False:

 MsgBox True Xor True 

The Xor operator works on binary arithmetic on the basis that 1 and 1 make 0, 1 and 0 make 1, 0 and 1 make 1, and 0 and 0 make 0.

The top row of the following table represents the value of each binary bit going from bit 7 to bit 0. The two rows below it represent the binary equivalents of the numeric numbers on the right of the table (column n ). The final row shows both the binary and numeric equivalents when the two numbers are Xor ed together. Each bit pair uses an Xor operator to achieve the final result on the bottom line.

128	64	32	16	8	4	2	1	n
	1		1		1			84
1			1				1	145
1	1				1		1	197

Each column of the preceding table shows a binary bit based on an 8-bit number. The bit values are shown in bold across the top. The right-hand column contains the actual numbers.

Bits are counted from the right to left, starting at bit 0 and ending at bit 7 for a single byte number. Notice that the values of each bit increase in powers of 2. The first number is 84, so bit 6, bit 4, and bit 2 are all set. If you add 64 + 16 + 4, this comes to 84. The second number is 145, so bit 7, bit 4, and bit 0 are set. If you add 128 + 16 + 1, this comes to 145.

When a logical Xor is done on the two numbers, the result is 197 (128 + 64 + 4 +1). This can be shown with the following VBA example:

 MsgBox 84 Xor 145 

This will give the result of 197. This result has an interesting property. If you Xor the result with one of the numbers used, you will get the other number that was used to create the result. The following will return the value 145:

 MsgBox 84 Xor 197 

The following will return the value 84:

 MsgBox 145 Xor 197 

This operator is often used in simple encryption routines. You use a string of random characters . The string you want to encrypt is then Xor ed character by character against your random string. This produces another apparently random string with no discernible pattern in it (and patterns are what code breakers look for).

To decrypt the string, all you have to do is Xor character by character against the original random string.