Convert HEX string to Unsigned INT (VBA)

Question

In MSACCESS VBA, I convert a HEX string to decimal by prefixing the string with "&h"

?CLng("&h1234")
4660
?CLng("&h80000000")
-2147483648 

What should I do to convert it to an unsigned integer?

Using CDbl doesn't work either:

?CDbl("&h80000000")
-2147483648 

Answer 1

I found my way here looking for a Word VBA solution, but what I've discovered might also apply to other Office apps. I realise that this is a very old question and that there are some ingenious solutions to it, but I'm surprised that nobody has explained what it is that seems to be the root cause of the problem, and hence what might possibly be a one-line solution in many cases. When I was an assembly language programmer in the 1970s, working more in binary and octal than anything else, this was a very common issue, known as "2s complement".

I'll explain it in its simplest form, from first principles, by the way it works on a byte, so that it's understandable even by absolute beginners.

Normally, the most significant bit is bit-7 at the left which has a value of 128, the least significant bit is bit-0 at the right which has a value of 1. Therefore, the highest possible value if all bits are set is 255. However in 2s complement, bit-7 is the "sign bit". This only leaves the seven bits from 0 to 6 to hold the actual value, giving them a maximum value of 127. The sign bit has a value of -128. If all 8 bits are set, the byte value becomes (-128 + 127) which gives the negative decimal value of -1. The 2s complement range of values for 8 bits is from -128 (with only bit-7 set) to +127 (with only bits 0 to 6 set). If the sign bit is set, the value of the byte is -128 plus the positive value of whatever is stored in bits 0 to 6. E.g. binary 11111101 = hex FD = decimal (-128 + 125) = -3, 10110100 = hex B4 = decimal (-128 + 52) = -76.

2s complement applies the same effect at each increasing 8-bit boundary, thus for 16 bits, the sign bit is bit-15 (with a value of -32,768) and the positive value is in bits 0 to 14, giving a 16-bit range of values from -32768 to 32767. Similarly, the 24-bit range is from -8388608 to 8388607, and so on.

I recently encountered this conversion problem in some code that was converting hexadecimal RGB colour values which originated as a 6-character text string in a Word document. Having successfully processed tens of thousands of these I was suddeny presented with an "out of range" error pop-up. The string that had caused the problem was "008080". The command ... = Val("&H" + variable) had converted this to -32896, an invalid value to pass as a colour property. The Val() function had removed the leading zeros and treated 8080 as a signed 2s complement 16-bit value.

In my case the solution was simple. Because I know that I'll always be dealing with 24-bit, 6-character hex values. I just added an extra "1" text character to the front of the hex code (thus making it longer than 16 bits), then, in effect, subtracted the same value. So, with the original 6-character hex RGB code held in the variable HexCode, I get the right decimal result using the command

DecCode = Val("&H" + "1" + HexCode) - Val("&H" + "1000000")

Problem solved, by just adding a little extra code to an existing line. I hope that my explanation of the cause of the problem helps others to devise their own solutions where it's appropriate.

Answer 2

With remark of @arcadeprecinct I was able to create a function for it:

Function Hex2UInt(h As String) As Double
    Dim dbl As Double: dbl = CDbl("&h" & h)

    If dbl < 0 Then
        dbl = CDbl("&h1" & h) - 4294967296#
    End If

    Hex2UInt = dbl
End Function

Some example output:

?Hex2UInt("1234")
4660 
?Hex2UInt("80000000")
2147483648 
?Hex2UInt("FFFFFFFFFFFF")
281474976710655 

Maximum value to represent as an integer is 0x38D7EA4C67FFF

?Hex2UInt("38D7EA4C67FFF")
999999999999999 
?Hex2UInt("38D7EA4C68000")
1E+15 

Answer 3

If you want to go higher than 2^31 you could use Decimal or LongLong. LongLong and CLngLngonly work on 64bit platforms though. Since I only have 32 bit office at the moment, this is for Decimal and CDec.

There seems to be an issue when converting 8-digit Hex numbers because apparently signed 32-bit is used somewhere in the process which results in the sign mistake even though Decimal could handle the number.

'only for positive numbers
Function myHex2Dec(hexString As String) As Variant
    'cut off "&h" if present
    If Left(hexString, 2) = "&h" Or Left(hexString, 2) = "&H" Then hexString = Mid(hexString, 3)

    'cut off leading zeros
    While Left(hexString, 1) = "0"
        hexString = Mid(hexString, 2)
    Wend

    myHex2Dec = CDec("&h" & hexString)
    'correct value for 8 digits onle
    If myHex2Dec < 0 And Len(hexString) = 8 Then
        myHex2Dec = CDec("&h1" & hexString) - 4294967296#
    'cause overflow for 16 digits
    ElseIf myHex2Dec < 0 Then
        Error (6) 'overflow
    End If

End Function

Test:

Sub test()
    Dim v As Variant
    v = CDec("&H80000000")              '-2147483648
    v = myHex2Dec("&H80000000")         '2147483648
    v = CDec("&H7FFFFFFFFFFFFFFF")      '9223372036854775807
    v = myHex2Dec("&H7FFFFFFFFFFFFFFF") '9223372036854775807
    v = CDec("&H8000000000000000")      '-9223372036854775808
    v = myHex2Dec("&H8000000000000000") 'overflow

End Sub

Answer 4

Your version seems like the best answer, but can be shortened a bit:

Function Hex2Dbl(h As String) As Double
    Hex2Dbl = CDbl("&h0" & h) ' Overflow Error if more than 2 ^ 64
    If Hex2Dbl < 0 Then Hex2Dbl = Hex2Dbl + 4294967296# ' 16 ^ 8 = 4294967296
End Function

Double will have rounding precision error for most values above 2 ^ 53 - 1 (about 16 decimal digits), but Decimal can be used for values up to 16 ^ 12 - 1 (Decimal uses 16 bytes, but only 12 of them for the number)

Function Hex2Dec(h)
    Dim L As Long: L = Len(h)
    If L < 16 Then               ' CDec results in Overflow error for hex numbers above 16 ^ 8
        Hex2Dec = CDec("&h0" & h)
        If Hex2Dec < 0 Then Hex2Dec = Hex2Dec + 4294967296# ' 2 ^ 32
    ElseIf L < 25 Then
        Hex2Dec = Hex2Dec(Left$(h, L - 9)) * 68719476736# + CDec("&h" & Right$(h, 9)) ' 16 ^ 9 = 68719476736
    End If
End Function

Answer 5

a proposal, result in h

sh = "&H80000000"
h = CDbl(sh)
If h < 0 Then
 fd = Hex$(CDbl(Left(sh, 3)) - 8)
 sh = "&h" & fd & Mid(sh, 4)
 h = CDbl(sh) + 2 ^ 31
End If