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


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


    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?

  • \$\begingroup\$ 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. \$\endgroup\$ Sep 28, 2015 at 9:25
  • \$\begingroup\$ @200_success There's only a very limited set of data types in VBA. No unsigned types... \$\endgroup\$
    – Johnbot
    Sep 28, 2015 at 9:28
  • \$\begingroup\$ Mat, what's wrong with Cdbl("&HFFFF")? \$\endgroup\$
    – user28366
    Oct 5, 2015 at 8:58
  • \$\begingroup\$ @Meehow see the update to my answer for why I think CDbl is wrong for this scenario. \$\endgroup\$
    – Johnbot
    Oct 5, 2015 at 12:41
  • 1
    \$\begingroup\$ @Meehow that is exactly what I meant with "I've done something stupidly over-complicated for something that should be pretty simple" ;-) \$\endgroup\$ Oct 5, 2015 at 13:30

1 Answer 1


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 :

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).

  • \$\begingroup\$ Awesome! I've linked to this answer on an edit to my SO answer :-) \$\endgroup\$ Sep 28, 2015 at 14:35
  • \$\begingroup\$ I expanded the bit about Currency vs Double a little and discovered something interesting. \$\endgroup\$
    – Johnbot
    Oct 5, 2015 at 12:33
  • \$\begingroup\$ how about doubleMax + doubleMax throwing an overflow error? \$\endgroup\$
    – user28366
    Oct 5, 2015 at 13:16
  • 1
    \$\begingroup\$ @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. \$\endgroup\$
    – Johnbot
    Oct 5, 2015 at 13:30
  • 1
    \$\begingroup\$ 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... \$\endgroup\$ Oct 5, 2015 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.