In addition to being a Boolean comparison operator, the And operator also performs a bitwise comparison of two numbers. This is useful for processing enumerations that are additive. First a bit about bits.

There are 8 bits in a byte. Those bits are

128 64 32 16 8 4 2 1

Remember that computers are binary (1’s and 0’s) so numbers from 0 to 255 are represented by 1’s in the proper bit positions.

00000000 = 0

00000010 = 2

01000100 = 68

11111111 = 255

You just add up the positions where there’s a 1, and you have your decimal representation of that number.

The Long Integer data type uses 4 bytes (32 bits). From help, the limit for the Long data types is 2,147,483,647. In binary, its positions look like (1 byte per row)

1,073,741,824 | 536,870,912 | 268,435,456 | 134,217,728 | 67,108,864 | 33,554,432 | 16,777,216 | |

8,388,608 | 4,194,304 | 2,097,152 | 1,048,576 | 524,288 | 262,144 | 131,072 | 65,536 |

32,768 | 16,384 | 8,192 | 4,096 | 2,048 | 1,024 | 512 | 256 |

128 | 64 | 32 | 16 | 8 | 4 | 2 | 1 |

If you add all those numbers (meaning a 1 in every bit position), you get the max for a Long (or any other 32 bit) data type. If you are particularly nosy, you may have noticed that those are only 31 numbers, not 32. The unused bit is reserved for the sign, as the Long data type can be negative to the same amount.

If you want to see if a certain bit is used to make a decimal number, you can compare the two numbers using And and it will return the bit position that they have in common. Using the Immediate Window, we can check this out.

?33 And 5

1

The decimal 33 will be represented by the 32 bit position and the 1 bit position – 00100001. The decimal 5 will be represented by the 4 bit position and the 1 bit position – 00000101. The And operator will return the sum of all the bits that these two numbers have in common. In this example, they only have 1 bit in common and it’s the 1 bit position. Another example:

?63 And 62

62

63 = 00111111

62 = 00111110

The sum of the bits that they have in common (32, 16, 8, 4, 2) adds up to 62.

These two examples show numbers that are not single bit position numbers, that is, they are all comprised of at least two bit positions. Usually, the And operator, when used for bitwise comparison, is used against a number that has only one bit position. I say “usually” because it’s the only way I ever use it. There are probably people much smarter than me that use it other ways.

To illustrate, if I take a Long number and I want to determine if a particular bit is used to make up that number, I would compare it to the decimal number that represents that bit position. Take the number 18. To determine that the 8 bit position is used to make 18, I would code

?18 And 8

The answer is zero because they have no bits in common. Since I know that 8 only uses one bit, then 8 is not used to make 18. Now let’s check 2.

?18 and 2

The answer is two – the sum of all the bits they have in common. Since I know that 2 only uses one bit, then I know that it’s the only bit they have in common, as shown here

18 = 00010010

2 = 00000010

Here’s a function that will return an array of all the bits used to make a certain number

‘Returns an array of the bits required to make lInput

‘Loop idea courtesy of a post by Andy Pope

Dim i As Long, j As Long ‘counters

Dim lPower As Long ‘holds each single bit position number

Dim lBitTest As Long ‘holds result of bitwise And

Dim aElems() As Long ‘stores common bits

‘Longs are 32 bit with one bit for the sign

For i = 0 To 30

‘calculate the single bit numbers 1,2,4,8,16, etc.

lPower = 2 ^ i

‘bitwise And to return common bit

lBitTest = lInput And lPower

‘If there is a common bit, store it in the array

If lBitTest = lPower Then

j = j + 1

ReDim Preserve aElems(1 To j)

aElems(j) = lBitTest

End If

Next i

‘return the array

SplitBit = aElems

End Function

You can test the function using a procedure like this

Dim vaElems As Variant

Dim lInput As Long

Dim i As Long

lInput = 41

vaElems = SplitBit(lInput)

For i = LBound(vaElems) To UBound(vaElems)

Debug.Print vaElems(i)

Next i

End Sub

Next week I’ll use this looping bitwise comparison to show how to make your enumerations additive.

1. Negative numbers are actually stored differently from positive numbers. -1 is actually 11111111111111111111111111111111. This is called “Two’s Complement Notation”. To negate a binary number, flip all the bits and then add one.

2. It’s not necessary to use the ^ operator in SplitBit above. Instead, put

lPower = 1

before the For i loop, and

lPower = lPower * 2

at the end of the loop.

Jonathan: Why do longs only use 31 bits then?

Hi Dick,

Here’s an explanation lifted from www4.wittenberg.edu

Two’s complement representation is a convention used to represent signed binary integers. In binary representation (positional notation), each bit has a weight which a power of two. The weights increase from right to left. For example

011101 = 0 x 2^5 + 1 x 2^4 + 1 x 2^3 + 1 x 2^2 + 0 x 2^1 + 1 x 2^0 = 16 + 8 + 4 + 1 = 29

The same positional notation is used with decimal numbers, except using powers of 10.

With two’s complement notation, all integers are represented using a fixed number of bits with the leftmost bit given a negative weight. So 100011 would be

100011 = -1 x 2^5 + 0 x 2^4 + 0 x 2^3 + 0 x 2^2 + 1 x 2^1 + 1 x 2^ 0 = -32 + 2 + 1 = -29

There are a number of nice features to two’s complement representation. The first is that the normal rules used in the addition of (unsigned) binary integers still work (throw away any bit carried out of the left-most position). Second, it’s easy to negate any integers: simply complement each bit and add 1 to the result (011101 complemented is 100010 plus 1 is 100011; 100011 complemented is 011100 plus 1 is 011101). Finally, the left most bit tells you if the integer is positive (0) or negative (1).

The range of integers and the ordering does change. For example, four bits can represent the unsigned integers values 0 (0000) through 15 (1111). However, four bit two’s complement representation represents the signed values – 8 through +7 ordered as below

1000 1001 1010 1011 1100 1101 1110 1111 0000 0001 0010 0011 0100 0101 0110 0111

-8 -7 -6 -5 -4 -3 -2 -1 0 1 2 3 4 5 6 7

I now know why the negative value is 1 larger than the positive.

It’s not necessary to use the ^ operator in SplitBit above.Jonathon – what’s the difference?

Don’t remember where I saw this, but anyway:

“There are 10 kinds of people. Those who read binary and those who don’t.”

Oft quoated saying in the news groups that one Harald, in fact i use it as my sig.

I studied 2’s comp for my A-level computers, never thought i’d see the day when it would crop up agian. Best of all was

Reverse Polish Notation, kinda makes sense, see, http://www-stone.ch.cam.ac.uk/documentation/rrf/rpn.htmlHex and Oct arithmetic where a brigde to far for me i’m afraid.

Intresting topic, i dont think i would ever use it, but you just don’t know – Any luck with that scoreboard matrix/light thing?

“Any luck with that scoreboard matrix/light thing?”

Juan Pablo has a nice digital clock that I hope to post later this week. It’s a good stepping stone to where I want to go.

Wilf Hey wrote a similar series in british mag PcPlus on RPN and how it makes calculation programming logical and easy. Far too many calculators state that 2+3*4 is 20. Great magazine, great author, recommended.

I use AND a lot to store/hide multiple TRUE/FALSE or YES/NO settings in a single Long variable. Not complicated, extremely useful. Writing a tutorial on it these days for my soon-to-be-launched website.

A digital watch using Excel cells for display ? Wow. Only men do things like that

“Great magazine, great author, recommended.”

Clearly, you see “

britishmag PcPlus”, stands to reason!!!! lol, yak, yak.“for my soon-to-be-launched website”

Look forward to that,

and…

“A digital watch using Excel cells for display ? Wow. Only men do things like that”

…. Abso-bloodly-lutely!

oh, and things like Feng Shui Moving Stars in Excel, and transposing music from a stave to colours for your kiddies’ xylophone, I thought it was just me!

still, the question that brought me here, a bitwise AND that can retrieve flags from my integer – anyone any clues?