logical/arithmetic functions w. Excel

  • Thread starter Thread starter Jean-Yves Simon
  • Start date Start date
J

Jean-Yves Simon

Hi All,

I have a basic knowledge of excel so far. To test some algorithms,
I need to have some functions to treat some numbers. The number
I want to crunch are all on 8-byte hexadecimal number so I am
using strings.

So far, I succeed to create function like

Function HexBitAND(N1 As String, N2 As String) As String
HexBitAND = Hex$(Val("&H" & N1) And Val("&H" & N2))
End Function

Function HexBitNOT(N1 As String) As String
HexBitNOT = Hex$(Val("&H" & N1) Xor Val("&HFFFFFFFF"))
End Function

Function HexBitOR(N1 As String, N2 As String) As String
HexBitOR = Hex$(Val("&H" & N1) Or Val("&H" & N2))
End Function

Everything works fine. I have now a problem with the add and
possibly rotate.

Here is what I did for add:

Function HexSum(N1 As String, N2 As String) As String
Dim interm As Double
Dim val1 As Double
Dim val2 As Double
val1 = Val("&H" & N1)
val2 = Val("&H" & N2)
interm = val1 + val2
HexSum = Hex$(interm)
End Function

Problem is for some numbers

A1 = 7FFF
A2 = 1

A3 = HexSum(A1,A2) ----> 8000

For A1 from 8000 to FFFF, I get for instance for A1=8000, A3=FFFF8001
for A1= AFFF, A3=FFFFB000.

The functions starts "working" as soon as A1 has more than 4 bytes.
A1 = 1FFFF A2=1 A3= 20000

I can't figure out why the error in the range 8000 to FFFF .

I have not yet tackle the ROR function, but if anyone can give me
some help on the add function, I'd appreciate.

Thanks and regards.
 
...
...
Everything works fine. I have now a problem with the add and
possibly rotate.

Here is what I did for add:

Function HexSum(N1 As String, N2 As String) As String
Dim interm As Double
Dim val1 As Double
Dim val2 As Double
val1 = Val("&H" & N1)
val2 = Val("&H" & N2)
interm = val1 + val2
HexSum = Hex$(interm)
End Function

Problem is for some numbers

A1 = 7FFF
A2 = 1

A3 = HexSum(A1,A2) ----> 8000

For A1 from 8000 to FFFF, I get for instance for A1=8000, A3=FFFF8001
for A1= AFFF, A3=FFFFB000.
Click to expand...

The problem you're having is how signed values are handled in hexadecimal.
Specifically, the Val() function does some seriously weird things with
hexadecimal conversion. DON'T USE IT! Use CInt, CLng, etc. instead. These are
all more intelligently designed than Val, which seems to need to provide
backward compatibility with 30-year-old BASIC dialects.

Try using the following instead. It addresses another potential problem: NEVER
use any floating point types in bit manipulation operations, use only integer
types, ideally Long.


Function HexSum(N1 As String, N2 As String) As String
HexSum = Hex(CLng("&H" & N1) + CLng("&H" & N2))
End Function
 
Harlan said:
The problem you're having is how signed values are handled in hexadecimal.
Specifically, the Val() function does some seriously weird things with
hexadecimal conversion. DON'T USE IT! Use CInt, CLng, etc. instead. These are
all more intelligently designed than Val, which seems to need to provide
backward compatibility with 30-year-old BASIC dialects.
Click to expand...
Try using the following instead. It addresses another potential problem: NEVER
use any floating point types in bit manipulation operations, use only integer
types, ideally Long.

Function HexSum(N1 As String, N2 As String) As String
HexSum = Hex(CLng("&H" & N1) + CLng("&H" & N2))
End Function
Click to expand...

Harlan,

THANKS! That did the trick ! It is now working fine.

Thanks and regards
 
Back
Top