excel vba bitshift right code example

Example 1: vba zero-fill right shift

'Many programming languages have a bitwise zero-fill right-shift operator: >>>
'VBA does not. However, it can be emulated in a performant function:

Public Function ShiftRightZeroFill&(ByVal n&, Optional ByVal shifts& = 1)
    Dim d&
    If shifts = 0 Then ShiftRightZeroFill = n: Exit Function
    If n And &H80000000 Then
        shifts = shifts - 1
        n = (n And &H7FFFFFFF) \ 2 Or &H40000000
    End If
    Select Case shifts
        Case 0:  d = n
        Case 1:  d = n \ 2&
        Case 2:  d = n \ 4&
        Case 3:  d = n \ 8&
        Case 4:  d = n \ 16&
        Case 5:  d = n \ 32&
        Case 6:  d = n \ 64&
        Case 7:  d = n \ 128&
        Case 8:  d = n \ 256&
        Case 9:  d = n \ 512&
        Case 10: d = n \ 1024&
        Case 11: d = n \ 2048&
        Case 12: d = n \ 4096&
        Case 13: d = n \ 8192&
        Case 14: d = n \ 16384&
        Case 15: d = n \ 32768
        Case 16: d = n \ 65536
        Case 17: d = n \ 262144
        Case 18: d = n \ 262144
        Case 19: d = n \ 524288
        Case 20: d = n \ 1048576
        Case 21: d = n \ 2097152
        Case 22: d = n \ 4194304
        Case 23: d = n \ 8388608
        Case 24: d = n \ 16777216
        Case 25: d = n \ 33554432
        Case 26: d = n \ 67108864
        Case 27: d = n \ 134217728
        Case 28: d = n \ 268435456
        Case 29: d = n \ 536870912
        Case 30: d = n \ 1073741824
        Case 31: d = &H0&
    End Select
    ShiftRightZeroFill = d
End Function
    
'----------------------------------------------------------------------------    
    
'Don't be off-put at the size of the function. This is many times faster than 
'any other VBA function that carries out bitwise zero-filled -right-shfits.
'The hard-coded values are much faster than calculating with exponentiation.

MsgBox ShiftRightZeroFill&(-9, 2)		<--displays:  1073741821
    

'NB: Remember that VBA Longs are signed.

Example 2: excel vba bitshift right

'Many languages have a bitwise signed-right-shift operator: >>
'VBA does not. However, it can be emulated in a performant manner:

Dim n as Long

n = 6
n = (n And -2) \ 2		'1 signed-right-shift (6 >> 1) = 3

n = 8
n = (n And -4) \ 4		'2 signed-right-shifts (8 >> 2) = 2

n = 170
n = (n And -8) \ 8		'3 signed-right-shifts (170 >> 3) = 21

n = -170
n = (n And -16) \ 16	'4 signed-right-shifts (-170 >> 4) = -11

'Notice the pattern:
'n = (n And -2^shfits) \ 2^shifts

'Important: Exponentiation is SLOW. It is much faster to use the hard coded
'           result of the exponentiation, as in the examples above.

n = -2023406815
n = (n And -16777216) \ 16777216 '24 signed-right-shifts (-2023406815 >> 24) = -121


'Imporatant: Since a Long Integer in VBA is signed, (n >> 31) needs special
'processing. The pattern is different:

n = 2147483647
n = n And -2147483648	'31 signed-right-shifts (-2147483647 >> 31) = 0

'Here is a VBA function that encapsulates the details:

Function ShiftRight&(ByVal n&, Optional ByVal shifts& = 1)
    Dim d&
    Select Case shifts
        Case 1:  d = 2&
        Case 2:  d = 4&
        Case 3:  d = 8&
        Case 4:  d = 16&
        Case 5:  d = 32&
        Case 6:  d = 64&
        Case 7:  d = 128&
        Case 8:  d = 256&
        Case 9:  d = 512&
        Case 10: d = 1024&
        Case 11: d = 2048&
        Case 12: d = 4096&
        Case 13: d = 8192&
        Case 14: d = 16384&
        Case 15: d = 32768
        Case 16: d = 65536
        Case 17: d = 131072
        Case 18: d = 262144
        Case 19: d = 524288
        Case 20: d = 1048576
        Case 21: d = 2097152
        Case 22: d = 4194304
        Case 23: d = 8388608
        Case 24: d = 16777216
        Case 25: d = 33554432
        Case 26: d = 67108864
        Case 27: d = 134217728
        Case 28: d = 268435456
        Case 29: d = 536870912
        Case 30: d = 1073741824
        Case 31: ShiftRight = CBool(n And &H80000000): Exit Function
        Case 0:  ShiftRight = n: Exit Function
    End Select
    ShiftRight = (n And -d) \ d
End Function
    
'Don't be off-put at the size of the function. This is many times faster than 
'any other VBA function that carries out bitwise signed-right-shfits. It is
'thousands of times faster than calling Excel's 
'WorksheetFunction.Bitrshift() method.
   
'However, remember that function calls are expensive in general. The inline 
'examples above are approximately 20 times faster than calling this highly
'optimized function. But you have to get the math right. The function
'takes care of it for you.