Hi, folks,

We're trying to get a subroutine working for converting numbers like
37719831058777893
83881713106708998
37156879353577176
37719831058778503

to their HEX equivalents.

All the options I have found seem to work only on smaller numbers. If
I read the MS documentation correctly, the built-in VBA HEX function
works up to 16 digits. These are 17 -- good ol' Murphey.

Another person and I have been working on the code below

Public Function DecToHex(Dec As Double) As String
Dim i As Long
Dim n As Long
Dim PlaceValHex As Long
Dim Hex(1 To 256) As String
Dim HexTemp As String
Dim Divisor As Long

'Dec = Int(Dec)

Dec = CVar(Application.Clean(Application.Trim(Dec)))

For i = 256 To 2 Step -1
If Dec = 20 ^ (i - 1) And Dec 15 Then
PlaceValHex = Int(Dec / (20 ^ (i - 1)))
Dec = Dec - (20 ^ (i - 1)) * PlaceValHex
Select Case PlaceValHex
Case 0 To 9
Hex(i) = CDec(PlaceValHex)
Case Is = 10
Hex(i) = "A"
Case Is = 11
Hex(i) = "B"
Case Is = 12
Hex(i) = "C"
Case Is = 13
Hex(i) = "D"
Case Is = 14
Hex(i) = "E"
Case Is = 15
Hex(i) = "F"
End Select
Else
Hex(i) = "0"
End If
Next i
PlaceValHex = Dec
Select Case PlaceValHex
Case 0 To 9
Hex(1) = CDec(PlaceValHex)
Case Is = 10
Hex(1) = "A"
Case Is = 11
Hex(1) = "B"
Case Is = 12
Hex(1) = "C"
Case Is = 13
Hex(1) = "D"
Case Is = 14
Hex(1) = "E"
Case Is = 15
Hex(1) = "F"
End Select
For i = 256 To 1 Step -1
If Hex(i) = "0" Then
Else
n = i
Exit For
End If
Next i
For i = n To 1 Step -1
HexTemp = HexTemp & Hex(i)
Next i
DecToHex = HexTemp

End Function

but while i = 256, in the line
If Dec = 20 ^ (i - 1) And Dec 15 Then
the condition "Dec = 20 ^ (i - 1)" throws an overflow error.

Any suggestions?

Thanks so much,
njw

We're trying to get a subroutine working for converting numbers like
37719831058777893
83881713106708998
37156879353577176
37719831058778503

oops, sorry -- custom function of course. Altho I converted to a sub
to tshoot it.

oops, sorry, a custom function. Altho I converted to a sub in order to
tshoot it.

On 02/12/2011 15:58, nj wrote:
Hi, folks,

We're trying to get a subroutine working for converting numbers like
37719831058777893
83881713106708998
37156879353577176
37719831058778503

to their HEX equivalents.

All the options I have found seem to work only on smaller numbers. If
I read the MS documentation correctly, the built-in VBA HEX function
works up to 16 digits. These are 17 -- good ol' Murphey.

There is a good reason for this. The mantissa of a double precision real
is only good to 15-16 decimal digits. Put those into a cell as real
double precision numbers and they will get rounded to the nearest value
that the machine can represent. You will have to declare Dec as Decimal
which should allow you up to 28 decimal places. If you want more then
you will have to use strings and/or find a multiple precision arithmetic
code in VBA to avoid reinventing too many wheels.

BTW Indexing into a string containing "0123456789ABCDEF" will save a
fair amount of coding.

Regards,
Martin Brown

On Dec 2, 10:32*am, Martin Brown
wrote:
<snip

There is a good reason for this. The mantissa of a double precision real
is only good to 15-16 decimal digits. Put those into a cell as real
double precision numbers and they will get rounded to the nearest value
that the machine can represent. You will have to declare Dec as Decimal
which should allow you up to 28 decimal places. If you want more then
you will have to use strings and/or find a multiple precision arithmetic
code in VBA to avoid reinventing too many wheels.

BTW Indexing into a string containing "0123456789ABCDEF" will save a
fair amount of coding.

Regards,
Martin Brown

Hi, Martin,

Yes, I was getting the idea that it was the parameter of the data
type, but a couple followup notes:
- I read else where that there's a bug that doesn't allow dimming as
Dec but instead to dim as Variant then use cdec, which I have already
done on the Dec variable.
- the overflow issue doesn't seem to be triggered by the Dec variable,
but rather by the value Dec is to be compared to, 20 ^ (i - 1) (where
i = 256!) --
obviously, that is a huuuuuuge number, so I'm not surprised, but
I'm not sure what the need of i being 256 is, so not sure I can change
it or what I can change it to, for that matter.

Bottomline is, I'm not really familiar with the underlying conversion
process/algorithm, so I'm rather tweaking in the dark.

Also, I'm not familar with the idea of "Indexing into a string
containing "0123456789ABCDEF" " -- it looks interesting -- any chance
you could point me towards more info on that?

Thanks again.

On 02/12/2011 17:04, nj wrote:
On Dec 2, 10:32 am, Martin
wrote:
<snip

There is a good reason for this. The mantissa of a double precision real
is only good to 15-16 decimal digits. Put those into a cell as real
double precision numbers and they will get rounded to the nearest value
that the machine can represent. You will have to declare Dec as Decimal
which should allow you up to 28 decimal places. If you want more then
you will have to use strings and/or find a multiple precision arithmetic
code in VBA to avoid reinventing too many wheels.

BTW Indexing into a string containing "0123456789ABCDEF" will save a
fair amount of coding.

Regards,
Martin Brown

Hi, Martin,

Yes, I was getting the idea that it was the parameter of the data
type, but a couple followup notes:
- I read else where that there's a bug that doesn't allow dimming as
Dec but instead to dim as Variant then use cdec, which I have already
done on the Dec variable.
- the overflow issue doesn't seem to be triggered by the Dec variable,
but rather by the value Dec is to be compared to, 20 ^ (i - 1) (where
i = 256!) --

Try putting the 20 into Decimal variable first and cross your fingers
that the ^ operator is available for that datatype.

Otherwise decrease i to something more reasonable like 20. Although you
can create Decimals in Excel VBA I am not sure what arithmetic
operations are defined on them. You may find it "promotes" them to 32
bit integers whilst trying to be helpful.

obviously, that is a huuuuuuge number, so I'm not surprised, but
I'm not sure what the need of i being 256 is, so not sure I can change
it or what I can change it to, for that matter.

Bottomline is, I'm not really familiar with the underlying conversion
process/algorithm, so I'm rather tweaking in the dark.

Also, I'm not familar with the idea of "Indexing into a string
containing "0123456789ABCDEF" " -- it looks interesting -- any chance
you could point me towards more info on that?

This will do the latter:


Sub TestIt()
For i = 0 To 15
Debug.Print i, HexDigit(i)
Next i
End Sub

Function HexDigit(x) As String
HexDigit = Mid("0123456789ABCDEF", x + 1, 1)
End Function


The ugly x+1 is because VBA indexes arrays starting from 1.

Regards,
Martin Brown

Below my signature is something I've posted in the past that might help you
out (it was an answer to a similar question). Note that if you want to
convert numbers larger than Excel can normally handle, make the values text
instead of numeric and the macro will process that.

Rick Rothstein (MVP - Excel)

Okay, I am pretty sure this function will do what you want. Note, though,
that you need to tell it the bit size when your decimal value is negative.
Well, you don't actually have to tell it the bit size, but if you don't,
then the code will assume the maximum bit size it can handle (which is
93-bits) and that will result in a lot of F's in front of the returned
value. In your case, the bit size appears to be 64 bits, so for your
negative values, you would use this formula...

=BigDec2Hex(A1,64)

Note that you can provide a bit size for positive numbers if you want, but
the macro will ignore it (otherwise you would get a bunch of leading zeroes
(which, if you want, I can make the function do that). Okay, here is the
macro...

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*100 1*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

Sorry about the bad wrapping on the longer lines of code. Here is the code
reformatted to fit better...

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

Rick Rothstein (MVP - Excel)

On Dec 2, 12:02*pm, "Rick Rothstein"
wrote:
Sorry about the bad wrapping on the longer lines of code. Here is the code
reformatted to fit better...

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

Rick Rothstein (MVP - Excel)

The wrapping wasn't really a problem but thanks!

Wow, that's cool and interesting. And it worked. Looks perfect. Thanks
a ton!

Rick Rothstein brought next idea :
Sorry about the bad wrapping on the longer lines of code. Here is the code
reformatted to fit better...

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

Rick Rothstein (MVP - Excel)

Rick, using the OP's values I get an 'Invalid procedure call or
argument' error on the final loop. OP states it works fine so what's
up?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Rick, using the OP's values I get an 'Invalid procedure call
or argument' error on the final loop. OP states it works
fine so what's up?

I just tested the OP's original numbers and they work for me also. How did
you attempt to use my function with them... on a worksheet or all in code?
If in code, post your code so I can test it. If on a worksheet, what is in
the cell and what is in the Formula Bar for any one value that failed for
you?

Rick Rothstein (MVP - Excel)

Rick Rothstein expressed precisely :
Rick, using the OP's values I get an 'Invalid procedure call
or argument' error on the final loop. OP states it works
fine so what's up?

I just tested the OP's original numbers and they work for me also. How did
you attempt to use my function with them... on a worksheet or all in code? If
in code, post your code so I can test it. If on a worksheet, what is in the
cell and what is in the Formula Bar for any one value that failed for you?

Rick Rothstein (MVP - Excel)

Both! VBA raises the error I posted. Wks returns #VALUE! Here's the
code

Function BigDec2Hex(ByVal DecimalIn As Variant, Optional BitSize As
Long = 93) As String
' by Rick Rothstein
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
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

...watch for wraps!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Friday, December 2, 2011 at 11:32:57 PM UTC+5:30, Rick Rothstein wrote:
Sorry about the bad wrapping on the longer lines of code. Here is the code
reformatted to fit better...

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

Rick Rothstein (MVP - Excel)


Sorry for being a dummy but how do we use this in excel. I am desperate to convert large values to hex. Also is there a way to convert float value to hex (ieee 754?)

sharif.s.786 wrote:

Sorry for being a dummy but how do we use this in excel. I am desperate
to convert large values to hex.

Rick posted VBA code. Copy the code to a module in the VBA editor, then in
your spreadsheet do this:

=BigDec2Hex(1234567890)

....replacing "1234567890" with your number.

Also is there a way to convert float value to hex (ieee 754?)

https://www.google.com/#q=convert+fl...lue+to+hex+vba

--
If a thing is wrong, it is wrong -- and vox populi can't change it.

On Fri, 2 Dec 2011 07:58:07 -0800 (PST), nj wrote:

Hi, folks,

We're trying to get a subroutine working for converting numbers like
37719831058777893
83881713106708998
37156879353577176
37719831058778503

to their HEX equivalents.

All the options I have found seem to work only on smaller numbers. If
I read the MS documentation correctly, the built-in VBA HEX function
works up to 16 digits. These are 17 -- good ol' Murphey.

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/

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)

On Sat, 3 Dec 2011 23:42:14 -0500, "Rick Rothstein" wrote:

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)

You never know :))

Personally, I find the 630 digit limit in their fastest version more than sufficient <bseg

Actually, I think the major feature is the plethora of functions available.