Hi

I have successfully created functions for bitwise operators BInary AND, OR
and XOR using the following format in VBA (2010 beta)

Function Bin<operator(a, b)
Bin<operator = a <operator b
End Function

for example the XOR script is:

Function BinXor(a, b)
BinXor = a Xor b
End Function

when I try the same for NOT, VBA comes back with:

Function BinNot(a)
BinNot = Not a
End Function

which is fine as you can only NOT 1 number

The answers are not what I am expecting however, ie the corret outputs are
0=1 and 1=0 whereas the functions output is: 1= -2 and 0 = -1

What do I need to do to make this work or is there a built in "binary flip"
function in Excel?

Regards

Mark

The numerical equivalents to True and False are different in VBA than in the
worksheet:

http://groups.google.com/group/micro...746093e7133?q=
--
Gary''s Student - gsnu200909


"Mark Scott" wrote:

Hi

I have successfully created functions for bitwise operators BInary AND, OR
and XOR using the following format in VBA (2010 beta)

Function Bin<operator(a, b)
Bin<operator = a <operator b
End Function

for example the XOR script is:

Function BinXor(a, b)
BinXor = a Xor b
End Function

when I try the same for NOT, VBA comes back with:

Function BinNot(a)
BinNot = Not a
End Function

which is fine as you can only NOT 1 number

The answers are not what I am expecting however, ie the corret outputs are
0=1 and 1=0 whereas the functions output is: 1= -2 and 0 = -1

What do I need to do to make this work or is there a built in "binary flip"
function in Excel?

Regards

Mark

If, after reading the link Gary''s Student posted, you still want 1 to
become 0 and 0 to become 1 (I'm assuming you won't feed the function any
other values besides these), then give this function a try...

Function BinNot(a)
BinNot = Abs(Not a - 2)
End Function

--
Rick (MVP - Excel)


"Mark Scott" <Mark wrote in message
...
Hi

I have successfully created functions for bitwise operators BInary AND, OR
and XOR using the following format in VBA (2010 beta)

Function Bin<operator(a, b)
Bin<operator = a <operator b
End Function

for example the XOR script is:

Function BinXor(a, b)
BinXor = a Xor b
End Function

when I try the same for NOT, VBA comes back with:

Function BinNot(a)
BinNot = Not a
End Function

which is fine as you can only NOT 1 number

The answers are not what I am expecting however, ie the corret outputs are
0=1 and 1=0 whereas the functions output is: 1= -2 and 0 = -1

What do I need to do to make this work or is there a built in "binary
flip"
function in Excel?

Regards

Mark

"Mark Scott" wrote:
Function BinNot(a)
BinNot = Not a
End Function
[....]
The answers are not what I am expecting however,
ie the corret outputs are 0=1 and 1=0 whereas the
functions output is: 1= -2 and 0 = -1

They may not be what you expect, but they __are__ the correct results for
the __bitwise__ operation, which you said you want.

Consider binAND(12,10), binOR(12,10) and binXOR(12,10). The results are 8,
14 and 6 respectively, since 12 is 1100 and 10 is 1010 in binary.

I presume that meets your expectations. If not, what do you expect?(!)

binNOT(12) is -13 because the complement of 1100 is 1...10011, and
binNOT(10) is -11 because the complement of 1010 is 1...10101.

Likewise, binNOT(0) is -1 because the complement of 0 is 1...11, and
binNOT(1) is -2 because the complement of 1 is 1...10.

Hopefully that explains your results, and you acquiesce to accept them.

Otherwise (sigh)....

If you want binNOT(0) to be 1 and binNOT(1) to be 0, perhaps you want the
__boolean__ complement, not the bitwise complement.

In that case, you could write --NOT(0) and --NOT(1) in Excel. Or in VBA,
you could write: binNOT = -1 * (a = 0). I would call that boolNOT, not
binNOT. But why bother?

But what would you want for binNOT(12) and binNOT(10)?

boolNOT would return 0 in both cases. Is that what you expect?(!) If so,
--NOT() or boolNOT is indeed the answer you want. But it is __not__ the
bitwise operation.

Or would you expect 3 (0011) and 5 (0101)?

Off-hand, I do not know a fast way to do that. The best I have come up with
so far is:

Function binNOT(a As Long) As Long
Dim mask As Long
If a < 0 Then
Mask = -1
Else
Mask = 1
While (a And Mask) < a: Mask = 2 * Mask + 1: Wend
End If
binNOT = Mask And (Not a)
End Function


----- original message -----

"Mark Scott" wrote:
Hi

I have successfully created functions for bitwise operators BInary AND, OR
and XOR using the following format in VBA (2010 beta)

Function Bin<operator(a, b)
Bin<operator = a <operator b
End Function

for example the XOR script is:

Function BinXor(a, b)
BinXor = a Xor b
End Function

when I try the same for NOT, VBA comes back with:

Function BinNot(a)
BinNot = Not a
End Function

which is fine as you can only NOT 1 number

The answers are not what I am expecting however, ie the corret outputs are
0=1 and 1=0 whereas the functions output is: 1= -2 and 0 = -1

What do I need to do to make this work or is there a built in "binary flip"
function in Excel?

Regards

Mark

I wrote:
If you want binNOT(0) to be 1 and binNOT(1)
to be 0, perhaps you want the __boolean__
complement, not the bitwise complement.
[....]
Or in VBA, you could write: binNOT = -1 * (a = 0).

Or simply binNOT = - (a = 0). (Doh!)


----- original message -----

"Joe User" wrote:
"Mark Scott" wrote:
Function BinNot(a)
BinNot = Not a
End Function
[....]
The answers are not what I am expecting however,
ie the corret outputs are 0=1 and 1=0 whereas the
functions output is: 1= -2 and 0 = -1

They may not be what you expect, but they __are__ the correct results for
the __bitwise__ operation, which you said you want.

Consider binAND(12,10), binOR(12,10) and binXOR(12,10). The results are 8,
14 and 6 respectively, since 12 is 1100 and 10 is 1010 in binary.

I presume that meets your expectations. If not, what do you expect?(!)

binNOT(12) is -13 because the complement of 1100 is 1...10011, and
binNOT(10) is -11 because the complement of 1010 is 1...10101.

Likewise, binNOT(0) is -1 because the complement of 0 is 1...11, and
binNOT(1) is -2 because the complement of 1 is 1...10.

Hopefully that explains your results, and you acquiesce to accept them.

Otherwise (sigh)....

If you want binNOT(0) to be 1 and binNOT(1) to be 0, perhaps you want the
__boolean__ complement, not the bitwise complement.

In that case, you could write --NOT(0) and --NOT(1) in Excel. Or in VBA,
you could write: binNOT = -1 * (a = 0). I would call that boolNOT, not
binNOT. But why bother?

But what would you want for binNOT(12) and binNOT(10)?

boolNOT would return 0 in both cases. Is that what you expect?(!) If so,
--NOT() or boolNOT is indeed the answer you want. But it is __not__ the
bitwise operation.

Or would you expect 3 (0011) and 5 (0101)?

Off-hand, I do not know a fast way to do that. The best I have come up with
so far is:

Function binNOT(a As Long) As Long
Dim mask As Long
If a < 0 Then
Mask = -1
Else
Mask = 1
While (a And Mask) < a: Mask = 2 * Mask + 1: Wend
End If
binNOT = Mask And (Not a)
End Function


----- original message -----

"Mark Scott" wrote:
Hi

I have successfully created functions for bitwise operators BInary AND, OR
and XOR using the following format in VBA (2010 beta)

Function Bin<operator(a, b)
Bin<operator = a <operator b
End Function

for example the XOR script is:

Function BinXor(a, b)
BinXor = a Xor b
End Function

when I try the same for NOT, VBA comes back with:

Function BinNot(a)
BinNot = Not a
End Function

which is fine as you can only NOT 1 number

The answers are not what I am expecting however, ie the corret outputs are
0=1 and 1=0 whereas the functions output is: 1= -2 and 0 = -1

What do I need to do to make this work or is there a built in "binary flip"
function in Excel?

Regards

Mark