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)
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
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
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
‘return the array
SplitBit = aElems
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)
Next week I’ll use this looping bitwise comparison to show how to make your enumerations additive.