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)





On Mon, 7 Sep 2009 07:33:01 -0700, TheWizEd
wrote:

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

Thanks.

"Gary''s Student" wrote:

Multiply or divide by a power of 2.
--
Gary''s Student - gsnu200902


"TheWizEd" wrote:

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?