Binary operations (left/right shift, binary and/or, etc.)

[Mike Hodgson]

How do you do binary operations like left shift, right shift, binary
and, binary or, exclusive or, etc. I'd like to essentially do this
(basic C calculations) in Excel:

x = a >> 32; // (left shift by 32 bits)
y = a & 0xFFFFFFFF; // (binary AND; i.e. bit mask)

I'm basically trying to get the high-order & low-order DWORDs from a
64-bit int.

 

[Mike Hodgson]

A small clarification - I mean in a cell formula rather than VBA.

 

[Leith Ross]

Hello Mike,

Here are some routines I use when working with DWords in API calls.
think you will find these useful. Place this code in a standard VB
Module and you can use these macros in your code.


Code
-------------------
Public Function HiByte(ByVal w As Integer) As Byte

If w And &H8000 Then
HiByte = &H80 Or ((w And &H7FFF) \ &HFF)
Else
HiByte = w \ 256
End If

End Function

Public Function HiWord(dw As Long) As Integer

If dw And &H80000000 Then
HiWord = (dw \ 65535) - 1
Else
HiWord = dw \ 65535
End If

End Function

Public Function LoByte(w As Integer) As Byte

LoByte = w And &HFF

End Function

Public Function LoWord(dw As Long) As Integer

If dw And &H8000& Then
LoWord = &H8000 Or (dw And &H7FFF&)
Else
LoWord = dw And &HFFFF&
End If

End Function

Public Function LShiftWord(ByVal w As Integer, ByVal C As Integer) As Integer

Dim dw As Long
dw = w * (2 ^ C)
If dw And &H8000& Then
LShiftWord = CInt(dw And &H7FFF&) Or &H8000
Else
LShiftWord = dw And &HFFFF&
End If

End Function

Public Function RShiftWord(ByVal w As Integer, ByVal C As Integer) As Integer

Dim dw As Long
If C = 0 Then
RShiftWord = w
Else
dw = w And &HFFFF&
dw = dw \ (2 ^ C)
RShiftWord = dw And &HFFFF&
End If

End Function

Public Function MakeWord(ByVal bHi As Byte, ByVal bLo As Byte) As Integer

If bHi And &H80 Then
MakeWord = (((bHi And &H7F) * 256) + bLo) Or &H8000
Else
MakeWord = (bHi * 256) + bLo
End If

End Function

Public Function MakeDWord(wHi As Integer, wLo As Integer) As Long

If wHi And &H8000& Then
MakeDWord = (((wHi And &H7FFF&) * 65536) _
Or (wLo And &HFFFF&)) _
Or &H80000000
Else
MakeDWord = (wHi * 65535) + wLo
End If

End Functio
-------------------

 

[Amedee Van Gasse]

Mike Hodgson shared this with us in microsoft.public.excel.programming:

A small clarification - I mean in a cell formula rather than VBA.

--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:[email protected] |* W*
http://www.mallesons.com

Not directly, but you could use the Dec2Bin and Bin2Dec functions, and
write your own formulae around that. I think you need to install the
Analysis Toolpack.


PS: add a space after your "--" sig separator. It should be "-- ", not
"--" ;-)

 

[keepITcool]

there are no excel function for binary arithmatic.
you have to create VBA user defined functions.

Instead of using VBA math and binary operators
I'd go the API way...

Note: Be carefull with creating your own derivatives
with copymemory, certainly with "As Any" syntax.



Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory"
( _
ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)

Function HiWord(ByVal dw As Long) As Integer
CopyMemory HiWord, ByVal VarPtr(dw) + 2, 2
End Function
Function LoWord(ByVal dw As Long) As Integer
CopyMemory LoWord, ByVal VarPtr(dw), 2
End Function
Function MakeWord(ByVal HiWord As Integer, _
ByVal LoWord As Integer) As Long
CopyMemory ByVal VarPtr(MakeWord) + 2, HiWord, 2
CopyMemory ByVal VarPtr(MakeWord), LoWord, 2
End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Mike Hodgson wrote :