Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert a file from Macintosh Number to Excel 2007 | Excel Discussion (Misc queries) | |||
Convert Excel 2007 data forms to Access 2007 ? | Excel Discussion (Misc queries) | |||
when i copied text to excel 2007, how do i convert it to number | Excel Discussion (Misc queries) | |||
how i convert "100" to "hundred"( number to text) in excel-2007 | Excel Worksheet Functions | |||
Excel: how to convert "27.11.2007 15:13" to number (cellformat fai | Excel Discussion (Misc queries) |