Bitwise shift in VBA

[TheWizEd]

How do you perform a bitwise shift in VBA (Excel)?

I have seen an operator in some VB code like this.

x = y << 8 ' I believe this mean shift y 8 bits to the right

But this is not in VBA. Any suggestions?

 

[Gary''s Student]

Multiply or divide by a power of 2.

 

[Bob Phillips]

Function BitShift(StartValue, BitsToShift)
BitShift = StartValue * (2 ^ BitsToShift)
End Function

 

[TheWizEd]

I've tried that but I get overflow. Guess I need to play around with it some
more.

Thanks.

 

[Chip Pearson]

The following code illustrates bit and byte shifts to the left or to
the right:

Sub AAA()
Dim L As Long
Dim K As Long
Dim BytesToShift As Long
Dim BitsToShift As Long

L = &H800 ' Test Value

' BytesToShift > 0 --> shift left
' BytesToShift < 0 --> shift right
BytesToShift = 2
K = L * (&H10 ^ BytesToShift)
Debug.Print Hex(L), Hex(K)

L = &H80 ' Test Value
' BitsToShift > 0 --> shift left
' BitsToShift < 0 --> shift right
BitsToShift = 2
K = L * (2 ^ BitsToShift)
Debug.Print Hex(L), Hex(K)
End Sub

You can wrap it all up in a single function to handle either bit or
byte shifts in either direction.

Function Shift(InL As Long, N As Long, _
Optional Bits As Boolean = False) As Long
Dim L As Long
If Bits = False Then
Shift = InL * (&H10 ^ N)
Else
Shift = InL * (2 ^ N)
End If
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[TheWizEd]

My overflow was caused by saving the results to the wrong data type.

Multiplying and dividing by 2s works thanks.

 

[Chip Pearson]

I've tried that but I get overflow.

This happens often due to the way the compiler works. The compiler
always uses the smallest data type that it can, unless coerced
otherwise. For example, examine the following code:

Dim X As Integer
Dim Y As Integer
X = 32000
Y = (X * 2) / 2
Debug.Print Y

On first glance, this looks fine. X and Y are integers, and neither X
nor Y exceeds the maximum value of an integer. However, this code with
throw an overflow exception because the intermediate calculation (X *
2) does overflow an integer. Unless specified otherwise, the compiler
uses an Integer for this intermediate calculation, causing the
overflow.

You can get around this by coercing one of the numbers in the equation
to a Long. E.g.,

Dim X As Integer
Dim Y As Integer
X = 32000
Y = (X * 2&) / 2
Debug.Print Y

The "&" character forces the compiler to use a Long in the calculation
rather than an integer. Since the intermediate calculation of X*2 is
done with Longs, no overflow error will occur.

Also, note that Longs are ALWAYS signed values, +/- &H7FFFFFFF or +/-
2,147,483,647. If you try to shift into the last bit, you'll overflow
the Long.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[ELF]

Try these functions:

Public Function shrLong(ByVal Value As Long, ByVal Shift As Byte) As Long
shrLong = Value
If Shift > 0 Then
If Value > 0 Then
shrLong = Int(shrLong / (2 ^ Shift))
Else
If Shift > 31 Then
shrLong = 0
Else
shrLong = shrLong And &H7FFFFFFF

shrLong = Int(shrLong / (2 ^ Shift))

shrLong = shrLong Or 2 ^ (31 - Shift)
End If
End If
End If
End Function

Public Function shlLong(ByVal Value As Long, ByVal Shift As Byte) As Long
shlLong = Value
If Shift > 0 Then
Dim i As Byte
Dim m As Long
For i = 1 To Shift
' save 30th bit
m = shlLong And &H40000000
' clear 30th and 31st bits
shlLong = (shlLong And &H3FFFFFFF) * 2
If m <> 0 Then
' set 31st bit
shlLong = shlLong Or &H80000000
End If
Next i
End If
End Function

Public Function shrInteger(ByVal Value As Integer, ByVal Shift As Byte) As Integer
shrInteger = Value
If Shift > 0 Then
If Value > 0 Then
shrInteger = Int(shrInteger / (2 ^ Shift))
Else

If Shift > 15 Then
shrInteger = 0
Else
shrInteger = shrInteger And &H7FFF

shrInteger = Int(shrInteger / (2 ^ Shift))

shrInteger = shrInteger Or 2 ^ (15 - Shift)
End If

End If
End If
End Function

Public Function shlInteger(ByVal Value As Integer, ByVal Shift As Byte) As Integer
shlInteger = Value
If Shift > 0 Then
Dim i As Byte
Dim m As Integer
For i = 1 To Shift
' save 14th bit
m = shlInteger And &H4000
' clear 14th and 15th bits
shlInteger = (shlInteger And &H3FFF) * 2
If m <> 0 Then
' set 14th bit
shlInteger = shlInteger Or &H8000
End If
Next i
End If
End Function

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


Public Function shlByte(ByVal Value As Byte, ByVal Shift As Byte) As Byte
shlByte = Value
If Shift > 0 Then
Dim i As Byte
Dim m As Byte
For i = 1 To Shift
shlByte = (shlByte And &H7F) * 2
Next i
End If
End Function