# 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("&H8000")
-32768
Val("&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) = "&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("&H" & 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?

• I'm confused as to what you want to accomplish. "Double" usually means a 64-bit floating point number. Yet, your text says you want a "positive numeric value", but then your examples seem to show signed 16-bit integers (usually called a "short"). Furthermore, it would seem that only whole numbers could possibly be represented in the hex string, so the return type should be Short, Integer, Long, UShort, UInteger, ULong, or Decimal. Sep 28 '15 at 9:25
• @200_success There's only a very limited set of data types in VBA. No unsigned types... Sep 28 '15 at 9:28
• Mat, what's wrong with Cdbl("&HFFFF")?
– user28366
Oct 5 '15 at 8:58
• @Meehow see the update to my answer for why I think CDbl is wrong for this scenario. Oct 5 '15 at 12:41
• @Meehow that is exactly what I meant with "I've done something stupidly over-complicated for something that should be pretty simple" ;-) Oct 5 '15 at 13:30

From what I can tell a string starting with &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 < 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 + &H7FFFFFFF + &H7FFFFFFF + 2
End If

ConvertHex = result
End Function


## Currency vs Double

Maximum accurately representable positive integer value :

• &H0020000000000000 (9,007,199,254,740,992) for Double (IEEE 754 binary64)
• &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("&H0020000000000000")
doubleAfter = doubleMax + 1

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

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

MsgBox "Currency before: " & Format(currencyMax, "#") & vbNewLine & "after: " & 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 &H00038D7EA4C68000 (1,000,000,000,000,000).

• Awesome! I've linked to this answer on an edit to my SO answer :-) Sep 28 '15 at 14:35
• I expanded the bit about Currency vs Double a little and discovered something interesting. Oct 5 '15 at 12:33
• how about doubleMax + doubleMax throwing an overflow error?
– user28366
Oct 5 '15 at 13:16
• @Meehow Just tested it and apparently Double in VBA does overflow. But it's still not possible to overflow if the parsed result is an integer. I'm glad I don't have to work with that language. Oct 5 '15 at 13:30
• The correct, idiomatic way to do this is to use one of the built-in conversion functions, as @Meehow commented on the OP - I don't know what brainfart caused me to even write this function... Oct 5 '15 at 13:36