Bit Operations in Excel VBA

Each bit has value 0 or 1, all data is stored as a series of bits. Bits are grouped together to form bytes (8 bits) and larger data elements:

• Byte – 8 bits grouped into 1 byte
• Integer – 16 bits (2 bytes)
• Long – 32 bits (4 bytes)

For example, let's convert the binary number 11010001 to decimal. We need the powers of 2 from 0 (right bit) to 7 (left bit): 2⁰ = 1, 2¹ = 2, 2² = 4, … 2⁷ = 128. So the example, binary number 11010001₂ is equal to:
11010001₂ = 128*1 + 64*1 + 32*0 + 16*1 + 8*0 + 4*0 + 2*0 + 1*1 = 209

The AND, OR, XOR and NOT operators are bit operations, they work on individual bits in a number. Except for NOT (inverse) bit operators require 2 arguments also called operands.

Bit Operation OR

Bitwise operator OR check if either the right operand or the left operand is true, or if they are both true. In other words, operation OR returns 1 in all cases except where the corresponding bits of both operands are zero.

' Example
' Bit operator OR

i = 81 Or 22
 
' i = 87 = 1010111  = 1010001 Or 10110

Bit Operation AND

Bitwise operator AND check if either the right operand and the left operand is true. In other words, operation AND returns zero in all cases except where the corresponding bits of both operands are 1.

' Example
' Bit operator AND
i = 81 And 22
 
' i = 16 = 10000 = 1010001 And 10110

Bit Operation XOR

Bitwise operator XOR (or exclusive OR) sets the bit to 1 if the two bits are different, and 0 if they are the same.

' Example
' Bit operator XOR
i = 81 Xor 22
 
' i = 71 = 1000111 = 1010001 Xor 10110

Bit Operation NOT

Bitwise operator NOT only takes a single argument and simply reverses all bits, changing all the ones to zeros and zeros to ones.

' Example
' Bit operator NOT
i = Not 81
 
' i = 174 = 10101110 = Not 1010001

Related articles:

• Bit Shifting Function in Visual Basic