DEC2HEX - HEX2DEC Issues - Number too Long?

I have found here those UDF functions:

Function DHex(Nb As Double) As String

  Dim Nb2 As Double
  Dim IsNeg As Boolean
  Nb = WorksheetFunction.Round(Nb, 0)
  IsNeg = Nb < 0
  If IsNeg Then _
    Nb = 16 ^ (Int(WorksheetFunction.Log(-Nb, 16)) + 2) + Nb
  Do While Nb
    Nb2 = Int(Nb / 16)
    DHex = Mid("0123456789ABCDEF", Nb - Nb2 * 16 + 1, 1) & DHex
    Nb = Nb2
  Loop
  If Not IsNeg Then DHex = "0" & DHex
  
End Function

Function HDec(Hex As String) As Variant

  Dim I As Integer
  Dim HexLen As Integer
  Dim Code As Byte
  Dim Exp As Double
  Dim Res As Double
  HexLen = Len(Hex)
  If HexLen > 13 And Left(Hex, 1) <> "0" Then Exit Function
  Exp = 1
  For I = 0 To HexLen - 1
    Code = Asc(Mid(Hex, HexLen - I, 1))
    If Code > 64 Then Res = Res + (Code - 55) * Exp _
    Else Res = Res + (Code - 48) * Exp
    Exp = Exp * 16
  Next I
  HDec = Res
  If Left(Hex, 1) = "0" Then Exit Function
  HDec = HDec - 16 ^ HexLen

End Function

To avoid starting with A (as a negative number) you need to add 0 in the formula:

Formula:

=HDec(0&A48)

Hello Zbor!

I appreciate the very quick reply! It does look like that formula works, however, it spits out a DEC value that replaces the last two number values with zeroes. It is not the 'true' DEC number.

zbor formula = 45317471814165400

Actual DEC = 45317471814165458


Do you know how to make it show the 'true' DEC number?


Also, I got this all mixed up. I need to go the other way around. I need to change the DEC - 45317471814165458 to HEX - A10000219A23D2.

Is that possible with the same formula?


Thank you.

Note that numbers that large go beyond standard double precision (~15 digits) that Excel uses to store numbers. If you need more than double precision can hold, you will need to figure out exactly how you want to do this -- which will probably depend a lot on what you are doing with these numbers.

If you need to perform calculations on these numbers, you may consider peforming all calculations and storage in VBA using a Decimal data type that can store integers up to about 29 digits (see here for limits of different data types: https://msdn.microsoft.com/en-us/lib...=vs.60%29.aspx ).

If there are not calculations needed, you may use VBA to convert from hexadecimal into a Decimal data type (not double or integer or long), then convert that Decimal data type to string and store the "numbers" in Excel as strings (though you may need to take extra measures to make sure Excel does not convert the "numbers as text" into numbers and truncate them to the limits of double precision).

I'm sure there are other strategies as well. You are dealing with very large numbers here. Large enough that you will need to pay careful attention to data type and the limits imposed on those data types by whatever programming language you choose to use.

As I indicated, some of the answer to these questions probably should be context dependent. Where are these DEC numbers coming from? How are they being used?

Am I better off just entering each into the calculator in PROGRAMMER mode and translating each individual number to HEX? Or is it possible to translate the whole list via excel?

the "best" approach might be to abandon Excel in favor of a programming language with good support for very large numbers. But, I get the impression that you are unfamiliar -- not only with Excel -- but other programming languages as well.

If it must be done in Excel, we probably need to think through how to process a very large "number stored as text" (large enough that we can never allow it to be stored as a number) and convert to a hexidecimal string. I will think on it, and see what I can think up.

I can easily translate DEC to HEX via the windows calculator - it seems odd to me that it is so easy with that program but really difficult in excel. Is there any way to replicate what the calculator is doing for a list?

As near as I can tell, MS has programmed the Windows Calculator with quad-precision (~30 digit) support. Excel, for all versions (I suppose I cannot verify the newest version) is limited to double precision. I cannot explain why MS has not added quad-precision support to Excel. Some day, when you get a chance to ask the Excel Gods, you can ask them why they did not see fit to provide support for higher precision than double precision in Excel.

As I indicated -- this is easy in a program/programming language which supports very large, very precise numbers like this.

As tedious as it might be, it might be faster to do these by hand in Windows calculator.

After further research, zbor's DHex() function will work, if we adapt it to use Decimal instead of double data type. Note this page that describes the use of Decimal data type in VBA; https://msdn.microsoft.com/en-us/lib.../gg251687.aspx

We need to input the number to the UDF as a string. Dim Nb and Nb2 as variants instead of doubles, and then the rest of the code should work normally.

Function DHex(Nbin As string) As String
dim nb as variant
  Dim Nb2 As variant
  Dim IsNeg As Boolean
  Nb = cdec(nbin) 'WorksheetFunction.Round(Nb, 0)
rest of code as given above.

Another way:

	A	B	C
1	45317471814165458	A10000219A23D2	B1:=sCur2Hex(A1)

Option Explicit

Type uCur
  c                 As Currency
End Type

Type uai8
  ai(0 To 7)        As Byte
End Type

Function sCur2Hex(sInp As String) As String
  ' shg 2016
  
  Dim uC            As uCur
  Dim uai           As uai8
  Dim sCur          As String

  sCur = Replace(sInp, ".", "")
  sCur = Left(sCur, Len(sCur) - 4) & "." & Right(sCur, 4)
  sCur = Replace(LTrim(Replace(sCur, "0", " ")), " ", "0")
  
  If Len(sCur) > 20 Then GoTo NFG
  If Len(sCur) = 20 And sCur > "922337203685477.5807" Then GoTo NFG
  
  uC.c = CCur(sCur)
  LSet uai = uC
  ArrRev uai.ai
  sCur2Hex = Byte2Hex(uai.ai)
  sCur2Hex = Replace(LTrim(Replace(sCur2Hex, "0", " ")), " ", "0")
  Exit Function

NFG:
  sCur2Hex = "Too big!"
End Function

Function ArrRev(ByRef av As Variant)
  ' shg 2008
  Dim i             As Long
  Dim j             As Long
  Dim vTmp          As Variant

  i = LBound(av)
  j = UBound(av)

  Do While i < j
    vTmp = av(i)
    av(i) = av(j)
    av(j) = vTmp
    i = i + 1
    j = j - 1
  Loop
End Function

Function Byte2Hex(ai() As Byte, Optional sSep As String = "") As String
  ' Converts ai to a hex string
  ' VBA only

  Dim i             As Long

  For i = LBound(ai) To UBound(ai)
    Byte2Hex = Byte2Hex & Right("0" & Hex(ai(i)), 2) & sSep
  Next i
  Byte2Hex = Left(Byte2Hex, Len(Byte2Hex) - Len(sSep))
End Function

C1 is showing the formula that goes in B1. i.e.,

=sCur2Hex(A1)

You need to put the code in the workbook and save as an xlsm file:

Adding a Macro or UDF to a Standard Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel

You're welcome.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Start a new thread -- we'll get you sorted.

	A	B	C
1	Hex	Decimal String
2	FFFFFFFFFFFFFF	72057594037927935	B2: =HexToDecStr(A2)
3	FFFFFFFFFFFFFFFFFFFFFFFF	79,228,162,514,264,337,593,543,950,335	B3: =HexToDecStr(A3, ",")
4	123456789ABCDEF	81 985 529 216 486 895	B4: =HexToDecStr(A4, " ")
5	FFFFFFFFFFFFFFFFFFFFFFFF	79228162514264337593543950335	B5: =HexToDecStr(A5)