excel vba bitshift code example
Example 1: excel vba bitwise left shift
Function ShiftLeft1&(ByVal n&)
ShiftLeft1 = 2& * (n And 1073741823): If n And 1073741824 Then ShiftLeft1 = ShiftLeft1 Or &H80000000
End Function
MsgBox ShiftLeft1(170)
Function ShiftLeft2&(ByVal n&)
ShiftLeft2 = 4& * (n And 536870911): If n And 536870912 Then ShiftLeft2 = ShiftLeft2 Or &H80000000
End Function
MsgBox ShiftLeft2(170)
Function ShiftLeft3&(ByVal n&)
ShiftLeft3 = 8& * (n And 268435455): If n And 268435456 Then ShiftLeft3 = ShiftLeft3 Or &H80000000
End Function
MsgBox ShiftLeft3(170)
Function ShiftLeft4&(ByVal n&)
ShiftLeft4 = 16& * (n And 134217727): If n And 134217728 Then ShiftLeft4 = ShiftLeft4 Or &H80000000
End Function
MsgBox ShiftLeft4(170)
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)
Example 2: excel vba bitshift left
Function ShiftLeft1&(ByVal n&)
ShiftLeft1 = 2& * (n And 1073741823): If n And 1073741824 Then ShiftLeft1 = ShiftLeft1 Or &H80000000
End Function
MsgBox ShiftLeft1(170)
Function ShiftLeft2&(ByVal n&)
ShiftLeft2 = 4& * (n And 536870911): If n And 536870912 Then ShiftLeft2 = ShiftLeft2 Or &H80000000
End Function
MsgBox ShiftLeft2(170)
Function ShiftLeft3&(ByVal n&)
ShiftLeft3 = 8& * (n And 268435455): If n And 268435456 Then ShiftLeft3 = ShiftLeft3 Or &H80000000
End Function
MsgBox ShiftLeft3(170)
Function ShiftLeft4&(ByVal n&)
ShiftLeft4 = 16& * (n And 134217727): If n And 134217728 Then ShiftLeft4 = ShiftLeft4 Or &H80000000
End Function
MsgBox ShiftLeft4(170)
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)
Example 3: excel vba bitshift right
Dim n as Long
n = 6
n = (n And -2) \ 2
n = 8
n = (n And -4) \ 4
n = 170
n = (n And -8) \ 8
n = -170
n = (n And -16) \ 16
n = -2023406815
n = (n And -16777216) \ 16777216
n = 2147483647
n = n And -2147483648
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