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* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* |* W* http://www.mallesons.com

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* |* W* http://www.mallesons.com



Mike Hodgson wrote:

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* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* |* W*
http://www.mallesons.com

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
-------------------

--
Leith Ros

-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=37999

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* |* W*
http://www.mallesons.com



Mike Hodgson wrote:

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* |/ database administrator/ | mallesons stephen jaques
T +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
E |* 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
"--" ;-)

--
Amedee Van Gasse

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 :

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* |* W*
http://www.mallesons.com



Mike Hodgson wrote:

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* |/ database administrator/ | mallesons stephen jaques
T +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
E |* W*
http://www.mallesons.com