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)