Excel 2007 VBA: Convert laaaaarge number to Hex

[GS]

GS explained :

I'm still getting the errors on the large number but the truncated number in
B1 returns a value.

Note that I also see that asterisk missing in the previous functions you
posted, which were also done via copy/paste! ..???

I was able to get both functions to work (seemingly) by inserting a
leading asterisk in the 1st line so that the entire string is wrapped
with asterisks. Is this correct?

 

[GS]

GS pretended :

GS explained :

I was able to get both functions to work (seemingly) by inserting a leading
asterisk in the 1st line so that the entire string is wrapped with asterisks.
Is this correct?

Oops! Spoke too soon. The worksheet still persists the #VALUE! error!

 

[GS]

Ron Rosenfeld pretended :

Something is strange in your system.

I copy/pasted Rick's code; the asterisks were there, and the code worked as
designed.

Thanks for confirming. Interestly, both versions of Rick's function
contain different values in BinValues. The 1st posted version does not
contain a trailing asterisk in my news reader where the 2nd version
does contain a trailing asterisk but is missing an asterisk at the end
of the split in the string. Very weird! I tried the original posted
version on another machine and got the same results as on this machine.
When I put asterisks between every set of 4-digit values AND
prepend/append the entire string with asterisks it seems to work.
However, I don't know if it works correctly. The values I get are...

In A1: 37719831058777893
In A2: =bigdec2hex(A1)
result: 8601FC8B3F0725

In B1: 37719831
In B2: =bigdec2hex(B1)
result: 23F8F17

Can you verify these results?

 

[Rick Rothstein]

Oops! Spoke too soon. The worksheet still persists

the #VALUE! error!

I am not sure what to tell you Garry... the function works properly here on
my system (and on others who have copy/pasted it in the past)... the code
was copy/pasted into my newsgroup message before I posted it. So the
BinValues constant for both posted functions were correct when I posted them
and both show the correct number of asterisks in my news reader (Windows
Mail Live) when I look at them now. And yes, there is an asterisk between
every 4 binary digits and an asterisk on each end. Also, in case it matters,
in the last For..Next loop, this part of the included expression...

Mid$(BinaryString, X, 4)

has an asterisk concatenated on both sides as well (it is how I insure an
exact match of calculated binary values). As I said, I am unsure what to
tell you as the file you sent me works correctly here on my computer ever
since I put the missing asterisk back in. I cannot come up with any reason
for why your computer is not able to run the code, other than perhaps a
virus or faulty memory chips. Are you sure your computer is working
correctly on your end?

Rick Rothstein (MVP - Excel)

 

[Rick Rothstein]

Rick's routine works fine for your specific problem.

But if need precision for more than Excel's 15 digits,
for a number of different functions, I would suggest
the Xnumbers add-in which can allow precision as
high as 4030 digits, depending on the version of
Excel and desired speed.
See http://www.thetropicalevents.com/Xnumbers60/

I thought I wildly exceeded anyone's possible need when I provided for up to
28 decimal digits that could yield Hex numbers consisting of as many as 24
Hex-digits; but 4030 digits? MY GOD!!!

Rick Rothstein (MVP - Excel)

 

[GS]

Rick,
Thanks for persisting! Here's the code from your 2nd post as I copied
from my newsreader:

Function BigDec2Hex(ByVal DecimalIn As Variant, _
Optional BitSize As Long = 93) As String
Dim X As Integer, PowerOfTwo As Variant, BinaryString As String
Const BinValues = "0000*0001*0010*0011*0100*0101*0110*0111" & _
"1000*1001*1010*1011*1100*1101*1110*1111*"
Const HexValues = "0123456789ABCDEF"
DecimalIn = Int(CDec(DecimalIn))
If DecimalIn < 0 Then
If BitSize > 0 Then
PowerOfTwo = 1
For X = 1 To BitSize
PowerOfTwo = 2 * CDec(PowerOfTwo)
Next
End If
DecimalIn = PowerOfTwo + DecimalIn
If DecimalIn < 0 Then
BigDec2Hex = CVErr(xlErrValue)
Exit Function
End If
End If
Do While DecimalIn <> 0
BinaryString = Trim$(Str$(DecimalIn - 2 * _
Int(DecimalIn / 2))) & BinaryString
DecimalIn = Int(DecimalIn / 2)
Loop
BinaryString = String$((4 - Len(BinaryString) Mod 4) _
Mod 4, "0") & BinaryString
For X = 1 To Len(BinaryString) - 3 Step 4
BigDec2Hex = BigDec2Hex & Mid$(HexValues, (4 + InStr(BinValues, _
"*" & Mid$(BinaryString, X, 4) & "*")) \ 5, 1)
Next
End Function

Here's the code from your 1st post which I just copied from my
newsreader, but is not what I used...

Function BigDec2Hex(ByVal DecimalIn As Variant, Optional BitSize As
Long = 93) As String
Dim X As Integer, PowerOfTwo As Variant, BinaryString As String
Const BinValues =
"0000*0001*0010*0011*0100*0101*0110*0111*1000*1001*1010*1011*1100*1101*1110*1111"
Const HexValues = "0123456789ABCDEF"
DecimalIn = Int(CDec(DecimalIn))
If DecimalIn < 0 Then
If BitSize > 0 Then
PowerOfTwo = 1
For X = 1 To BitSize
PowerOfTwo = 2 * CDec(PowerOfTwo)
Next
End If
DecimalIn = PowerOfTwo + DecimalIn
If DecimalIn < 0 Then
BigDec2Hex = CVErr(xlErrValue)
Exit Function
End If
End If
Do While DecimalIn <> 0
BinaryString = Trim$(Str$(DecimalIn - 2 * Int(DecimalIn / 2))) &
BinaryString
DecimalIn = Int(DecimalIn / 2)
Loop
BinaryString = String$((4 - Len(BinaryString) Mod 4) Mod 4, "0") &
BinaryString
For X = 1 To Len(BinaryString) - 3 Step 4
BigDec2Hex = BigDec2Hex & Mid$(HexValues, (4 + InStr(BinValues, "*"
& Mid$(BinaryString, X, 4) & "*")) \ 5, 1)
Next
End Function

Note how the 1st line of BinValues (2nd post) does not have a trailing
asterisk. Note also how BinValues (1st post) does not have a trailing
asterisk.

Here's what I was able to get to work:

Const BinValues = "*0000*0001*0010*0011*0100*0101*0110*0111*" _
& "1000*1001*1010*1011*1100*1101*1110*1111*"

According to Ron, this renders the correct values. I take by your
explanation that this version of BinValues is the correct one. Strange
my reader displays 2 different versions of the same post. Mind you,
asterisks are how my reader displays boldface text and so I exect I
will not see the 1st/last asterisk and the string's 1st line will be
boldface. I'll confirm this after I post and if this is the case then I
may have to change my newsreader. (I only use mesnews because Karl
Peterson recommended it)

 

[GS]

Ok, the 1st line of my string is boldface and I do not see the
leading/trailing asterisks. I'll see if I can turn this feature off
before changing to another reader.

 

[GS]

Ron Rosenfeld formulated on Saturday :

Those are the same answers I get both from Rick's code as well as from
xNumbers.

Thanks, Ron! See my reply to Rick regarding the way my newsreader
(mesnews) displays text wrapped in asterisks to understand why I had
problems with the code 'as posted'.