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