# 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. – 200_success Sep 28 '15 at 9:25
• @200_success There's only a very limited set of data types in VBA. No unsigned types... – Johnbot 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. – Johnbot 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" ;-) – Mathieu Guindon 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 :-) – Mathieu Guindon Sep 28 '15 at 14:35
• I expanded the bit about Currency vs Double a little and discovered something interesting. – Johnbot 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. – Johnbot 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... – Mathieu Guindon Oct 5 '15 at 13:36