Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise shift in VBA
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise shift in VBA
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise shift in VBA
Function BitShift(StartValue, BitsToShift)
BitShift = StartValue * (2 ^ BitsToShift) End Function -- __________________________________ HTH Bob "TheWizEd" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise shift in VBA
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise shift in VBA
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) On Mon, 7 Sep 2009 07:16:01 -0700, 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise shift in VBA
My overflow was caused by saving the results to the wrong data type.
Multiplying and dividing by 2s works thanks. "Chip Pearson" wrote: 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) On Mon, 7 Sep 2009 07:16:01 -0700, 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bitwise shift in VBA
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bitwise functions | Excel Worksheet Functions | |||
bitwise operations as in xor | Excel Discussion (Misc queries) | |||
Bitwise comparison question | Excel Programming | |||
Where are Bitwise Operators | Excel Worksheet Functions | |||
Bitwise And | Excel Programming |