Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
nj nj is offline
external usenet poster
 
Posts: 7
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
nj nj is offline
external usenet poster
 
Posts: 7
Default Excel 2007 VBA: Convert laaaaarge number to Hex

oops, sorry, a custom function. Altho I converted to a sub in order to
tshoot it.
  #3   Report Post  
Posted to microsoft.public.excel.programming
nj nj is offline
external usenet poster
 
Posts: 7
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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.
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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
  #5   Report Post  
Posted to microsoft.public.excel.programming
nj nj is offline
external usenet poster
 
Posts: 7
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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.


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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

  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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)

  #9   Report Post  
Posted to microsoft.public.excel.programming
nj nj is offline
external usenet poster
 
Posts: 7
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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!


  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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




  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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)

  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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


  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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


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)

  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

Rick Rothstein presented the following explanation :
Both! VBA raises the error I posted. Wks returns #VALUE!
Here's the code


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)


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...

--
Garry

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


  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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


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)


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.

--
Garry

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




  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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.


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)

  #17   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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.


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)

  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

Rick Rothstein used his keyboard to write :
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.


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)


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...

--
Garry

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


  #19   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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! ..???

--
Garry

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


  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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! ..???


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?

--
Garry

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




  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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?

--
Garry

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


  #22   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

GS pretended :
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?


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

--
Garry

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


  #23   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,045
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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/

  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,045
Default Excel 2007 VBA: Convert laaaaarge number to Hex

On Sat, 03 Dec 2011 20:33:16 -0500, GS wrote:

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?


Something is strange in your system.

I copy/pasted Rick's code; the asterisks were there, and the code worked as designed.
  #25   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

Ron Rosenfeld pretended :
On Sat, 03 Dec 2011 20:33:16 -0500, GS wrote:

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?


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?

--
Garry

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




  #26   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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)

  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,045
Default Excel 2007 VBA: Convert laaaaarge number to Hex

On Sat, 03 Dec 2011 22:37:20 -0500, GS wrote:


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?


Those are the same answers I get both from Rick's code as well as from xNumbers.
  #28   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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)

  #29   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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)

--
Garry

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


  #30   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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.

--
Garry

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




  #31   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Excel 2007 VBA: Convert laaaaarge number to Hex

Ron Rosenfeld formulated on Saturday :
On Sat, 03 Dec 2011 22:37:20 -0500, GS wrote:


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?


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'.

--
Garry

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


  #32   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,045
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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.
  #33   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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?)
  #34   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default Excel 2007 VBA: Convert laaaaarge number to Hex

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I convert a file from Macintosh Number to Excel 2007 Bert Excel Discussion (Misc queries) 1 August 15th 09 06:15 PM
Convert Excel 2007 data forms to Access 2007 ? Cris22149 Excel Discussion (Misc queries) 0 November 19th 08 03:13 AM
when i copied text to excel 2007, how do i convert it to number Nat Excel Discussion (Misc queries) 0 August 30th 08 06:15 AM
how i convert "100" to "hundred"( number to text) in excel-2007 mohanraj Excel Worksheet Functions 1 May 11th 08 09:07 PM
Excel: how to convert "27.11.2007 15:13" to number (cellformat fai dominico23 Excel Discussion (Misc queries) 2 December 11th 07 03:43 PM


All times are GMT +1. The time now is 10:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"