Excel 2007 VBA: Convert laaaaarge number to Hex

  • Thread starter Thread starter nj
  • Start date Start date
N

nj

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
Click to expand...

oops, sorry -- custom function of course. Altho I converted to a sub
to tshoot it.
 
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.
Click to expand...

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 <|||[email protected]>
wrote:
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
Click to expand...

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 Dec 2, 10:32 am, Martin Brown<|||[email protected]>
wrote:


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!) --
Click to expand...

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?
Click to expand...

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*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
 
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 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)
Click to expand...

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)
Click to expand...

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?
 
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?
Click to expand...

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 :
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)
Click to expand...

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!
 
Both! VBA raises the error I posted. Wks returns #VALUE!
Here's the code
Click to expand...

Okay, I think I know what is happening... the value in the cell is not a
integer value, it is a value in scientific notation (3.77198E+16) which is
what is passed into the function... the function does not know what to do
with the decimal point or the E, so it errors out. Either enter the numbers
with an apostrophe in front of them or change the cell format to Text and
type the number in so that it hold all of its digits (you might have missed
that the original number 37719831058777893 was changed to 37719831058777800
because of Excel's precision display limitations).

Rick Rothstein (MVP - Excel)
 
Rick Rothstein presented the following explanation :
Okay, I think I know what is happening... the value in the cell is not a
integer value, it is a value in scientific notation (3.77198E+16) which is
what is passed into the function... the function does not know what to do
with the decimal point or the E, so it errors out. Either enter the numbers
with an apostrophe in front of them or change the cell format to Text and
type the number in so that it hold all of its digits (you might have missed
that the original number 37719831058777893 was changed to 37719831058777800
because of Excel's precision display limitations).

Rick Rothstein (MVP - Excel)
Click to expand...

Yes, I did all this already because of the issues you mention here,
noting that you mention passing the # as a text string. Still raises
the errors (VBA/Cell)! I'll retry this today...
 
Rick Rothstein pretended :
Okay, I think I know what is happening... the value in the cell is not a
integer value, it is a value in scientific notation (3.77198E+16) which is
what is passed into the function... the function does not know what to do
with the decimal point or the E, so it errors out. Either enter the numbers
with an apostrophe in front of them or change the cell format to Text and
type the number in so that it hold all of its digits (you might have missed
that the original number 37719831058777893 was changed to 37719831058777800
because of Excel's precision display limitations).

Rick Rothstein (MVP - Excel)
Click to expand...

FWIW
I tried this again today and get same results (errors). I also tried
these number with your previously posted DecToHex function and got the
same behavior. In all tests the input (DecimalIn) was passed as a
string.
 
I tried this again today and get same results (errors). I also
tried these number with your previously posted DecToHex
function and got the same behavior. In all tests the input
(DecimalIn) was passed as a string.
Click to expand...

Garry, you have my curiosity up... email me the workbook that is doing this
and I'll see if I can figure out what is going on. My email address is
rickDOTnewsATverizonDOTnet (replace the upper case letters with the symbols
they spell out.

Rick Rothstein (MVP - Excel)
 
FWIW
I tried this again today and get same results (errors). I also
tried these number with your previously posted DecToHex
function and got the same behavior. In all tests the input
(DecimalIn) was passed as a string.
Click to expand...

Garry sent me his file and the problem was he somehow deleted an asterisk in
the BinValues constant. Garry's file had this...

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

This line of code is missing and asterisk after the 0111 at the end of the
first text substring. The code line should be this...

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

Rick Rothstein (MVP - Excel)
 
Rick Rothstein used his keyboard to write :
Garry sent me his file and the problem was he somehow deleted an asterisk in
the BinValues constant. Garry's file had this...

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

This line of code is missing and asterisk after the 0111 at the end of the
first text substring. The code line should be this...

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

Rick Rothstein (MVP - Excel)
Click to expand...

OK Rick. I copy/pasted your 2nd post code and the asterisk is missing
in that post. I'll fix this and retry the project...
 
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! ..???
 
It happens that GS formulated :
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! ..???
Click to expand...

More info...

BigDec2Hex makes it to the 3rd iteration of the final loop.
DecToHex makes it to the 5th iteration of the final loop.

Any other suggestions?
 
Back
Top