EXCELY


Free Excel Templates and Articles
  • Excel VBA
  • Bit Shifting Function in Excel VBA

Bit Shifting Function in Excel VBA



Do you need Bitwise Shift operators in Excel VBA (similar to “<<" and ">>” operators in C++)? Unfortunately, VBA doesn't have any bit shift operators. But we can replicate bitwise shift operators with multiplying or dividing by the appropriate power of 2.

Bitwise Right Shift Function:

Public Function shr(ByVal Value As Long, ByVal Shift As Byte) As Long
    Dim i As Byte
    shr = Value
    If Shift > 0 Then
        shr = Int(shr / (2 ^ Shift))
    End If
End Function

Right shifting is equal to dividing Value by 2Shift.

Bitwise Left Shift Function:

Public Function shl(ByVal Value As Long, ByVal Shift As Byte) As Long
    shl = Value
    If Shift > 0 Then
        Dim i As Byte
        Dim m As Long
        For i = 1 To Shift
            m = shl And &H40000000
            shl = (shl And &H3FFFFFFF) * 2
            If m <> 0 Then
                shl = shl Or &H80000000
            End If
        Next i
    End If
End Function

Left shifting is equal to multiplying Value by 2Shift. But to avoid an overflow error we use small trick:

m = shl And &H40000000      ' save 30th bit

shl = shl And &H3FFFFFFF    ' clear 30th and 31st bits

shl = shl * 2               ' multiply by 2

If m <> 0 Then
    shl = shl Or &H80000000 ' set 31st bit
End If

Related articles: