Hex to Double converter I stumbled on this SO question which was asking about a way to convert larger hex values to a positive numeric value:


?Val("&amp;H8000")
 -32768 
Val("&amp;HFFFF")
 -1



My answer involved iterating the string digits one by one, and computing their respective value into the result:

Function ConvertHex(ByVal value As String) As Double

    If Left(value, 2) = "&amp;H" Then
        value = Right(value, Len(value) - 2)
    End If

    Dim result As Double

    Dim i As Integer, j As Integer
    For i = Len(value) To 1 Step -1

        Dim digit As String
        digit = Mid$(value, i, 1)

        result = result + (16 ^ j) * Val("&amp;H" &amp; digit)
        j = j + 1

    Next

    ConvertHex = result

End Function


It works, but I can't help thinking I've done something stupidly over-complicated for something that should be pretty simple.

There's a better way, isn't there?
From what I can tell a string starting with &amp;H is a hex literal.

There exists a number of conversion functions that can convert an expression to the desired type.

So it should simply be, depending on desired type:

Function ConvertHex(ByVal value As String) As Currency
    Dim result As Currency
    result = CCur(value)

    If result &lt; 0 Then
        'Add two times Int32.MaxValue and another 2 for the overflow
        'Because the hex value is apparently parsed as a signed Int64/Int32
        result = result + &amp;H7FFFFFFF + &amp;H7FFFFFFF + 2
    End If

    ConvertHex = result
End Function


Currency vs Double

Maximum accurately representable positive integer value :


&amp;H0020000000000000 (9,007,199,254,740,992) for Double (IEEE 754 binary64)
&amp;H000346DC5D638865 (922,337,203,685,477) for Currency


So why use Currency over Double when the latter works for a larger range of integers?

Currency is always accurate. If we overflow a Currency value we get an error. If we overflow the maximum representable integer value of a double we get an approximate integer value:

Dim doubleMax As Double
Dim doubleAfter As Double
doubleMax = CDbl("&amp;H0020000000000000")
doubleAfter = doubleMax + 1

MsgBox "Double before: " &amp; Format(doubleMax, "#") &amp; vbNewLine &amp; "after: " &amp; Format(doubleAfter, "#")

Dim currencyMax As Currency
Dim currencyAfter As Currency
currencyMax = CCur("&amp;H000346DC5D638865")
currencyAfter = currencyMax + 1

MsgBox "Currency before: " &amp; Format(currencyMax, "#") &amp; vbNewLine &amp; "after: " &amp; Format(currencyAfter, "#")


The output of this example is:

Double before: 9007199254740990
after: 9007199254740990


And then a run-time error '6': Overflow which is great if you want to avoid rounding errors. Now MSDN claims Double is


  stored as IEEE 64-bit (8-byte) floating-point number


but if you've read anything about the IEEE 754 binary64 you should be a bit surprised about the output from the example. The actual maximum is &amp;H00038D7EA4C68000 (1,000,000,000,000,000).