excel vba left shift code example

Example 1: excel vba bitwise left shift

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

Function ShiftLeft1&(ByVal n&)
    ShiftLeft1 = 2& * (n And 1073741823): If n And 1073741824 Then ShiftLeft1 = ShiftLeft1 Or &H80000000
End Function
  
MsgBox ShiftLeft1(170)    '<--displays 340      1 left-shift (170 << 1) = 340
  
'-------------------------------------------------------------------------------------------------------  

Function ShiftLeft2&(ByVal n&)
    ShiftLeft2 = 4& * (n And 536870911): If n And 536870912 Then ShiftLeft2 = ShiftLeft2 Or &H80000000
End Function
  
MsgBox ShiftLeft2(170)    '<--displays 680      2 left-shift3 (170 << 2) = 680
  
'-------------------------------------------------------------------------------------------------------  

Function ShiftLeft3&(ByVal n&)
    ShiftLeft3 = 8& * (n And 268435455): If n And 268435456 Then ShiftLeft3 = ShiftLeft3 Or &H80000000
End Function
  
MsgBox ShiftLeft3(170)    '<--displays 1360     3 left-shifts (170 << 3) = 1360
  
'-------------------------------------------------------------------------------------------------------  

Function ShiftLeft4&(ByVal n&)
    ShiftLeft4 = 16& * (n And 134217727): If n And 134217728 Then ShiftLeft4 = ShiftLeft4 Or &H80000000
End Function
  
MsgBox ShiftLeft4(170)    '<--displays 2720     4 left-shifts (170 << 4) = 2720
  
'NB: Thses function are extremely fast. But they are hard-coded for predefined number of place shifts.
'    Here is a generic function that handles all 31 possible place shifts:
        
Function ShiftLeft&(ByVal n&, Optional ByVal shifts& = 1)
    Dim d&
    Select Case shifts
        Case 1:  d = 2& * (n And 1073741823): If n And 1073741824 Then d = d Or &H80000000
        Case 2:  d = 4& * (n And 536870911):  If n And 536870912 Then d = d Or &H80000000
        Case 3:  d = 8& * (n And 268435455):  If n And 268435456 Then d = d Or &H80000000
        Case 4:  d = 16& * (n And 134217727): If n And 134217728 Then d = d Or &H80000000
        Case 5:  d = 32& * (n And 67108863):  If n And 67108864 Then d = d Or &H80000000
        Case 6:  d = 64& * (n And 33554431):  If n And 33554432 Then d = d Or &H80000000
        Case 7:  d = 128& * (n And 16777215): If n And 16777216 Then d = d Or &H80000000
        Case 8:  d = 256& * (n And 8388607):  If n And 8388608 Then d = d Or &H80000000
        Case 9:  d = 512& * (n And 4194303):  If n And 4194304 Then d = d Or &H80000000
        Case 10: d = 1024& * (n And 2097151): If n And 2097152 Then d = d Or &H80000000
        Case 11: d = 2048& * (n And 1048575): If n And 1048576 Then d = d Or &H80000000
        Case 12: d = 4096& * (n And 524287):  If n And 524288 Then d = d Or &H80000000
        Case 13: d = 8192& * (n And 262143):  If n And 262144 Then d = d Or &H80000000
        Case 14: d = 16384& * (n And 131071): If n And 131072 Then d = d Or &H80000000
        Case 15: d = 32768 * (n And 65535):   If n And 65536 Then d = d Or &H80000000
        Case 16: d = 65536 * (n And 32767&):  If n And 32768 Then d = d Or &H80000000
        Case 17: d = 131072 * (n And 16383&): If n And 16384& Then d = d Or &H80000000
        Case 18: d = 262144 * (n And 8191&):  If n And 8192& Then d = d Or &H80000000
        Case 19: d = 524288 * (n And 4095&):  If n And 4096& Then d = d Or &H80000000
        Case 20: d = 1048576 * (n And 2047&): If n And 2048& Then d = d Or &H80000000
        Case 21: d = 2097152 * (n And 1023&): If n And 1024& Then d = d Or &H80000000
        Case 22: d = 4194304 * (n And 511&):  If n And 512& Then d = d Or &H80000000
        Case 23: d = 8388608 * (n And 255&):  If n And 256& Then d = d Or &H80000000
        Case 24: d = 16777216 * (n And 127&): If n And 128& Then d = d Or &H80000000
        Case 25: d = 33554432 * (n And 63&):  If n And 64& Then d = d Or &H80000000
        Case 26: d = 67108864 * (n And 31&):  If n And 32& Then d = d Or &H80000000
        Case 27: d = 134217728 * (n And 15&): If n And 16& Then d = d Or &H80000000
        Case 28: d = 268435456 * (n And 7&):  If n And 8& Then d = d Or &H80000000
        Case 29: d = 536870912 * (n And 3&):  If n And 4& Then d = d Or &H80000000
        Case 30: d = 1073741824 * (n And 1&): If n And 2& Then d = d Or &H80000000
        Case 31: If n And &H1& Then d = &H80000000 Else d = &H0&
        Case 0:  d = n
    End Select
    ShiftLeft = d
End Function

MsgBox ShiftLeft(1, 24) '<--displays 16777216     24 left-shifts (1 << 24) = 16777216

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.