VBA large hex to integer

Question

I'm trying to convert a large integer in hexadecimal to decimal and VBA keeps returning an incorrect value.

In VBA, the string "&H0A1000043" returns -1593835453, when I expect 2701131843. I've tried CDbl, CLng, and Val functions, and they all return the wrong, negative value.

The Excel HEX2DEC does return a correct value. Am I wrong in thinking that this is a bug in VBA functions?

Answer 1

I wrote a quick functions that allow transformation of large numbers from decimal to hex and from hex to decimal. I did not do any validation of passed parameters but i would recommend adding it with thrown error if not valid. I am also sure that there is a better and more efficient way of doing this, but for the quick and dirty my solution will work in the pinch.

To add the code, open in excel Visual Basic and create new module then paste the code below. Make sure to go to Tools, click on Reference and add "Microsoft Scripting Runtime" to enable Scripting.Dictionary variable creation.

Function Hex2Dec64(rngRef As Range) As Double
Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

dict.Add "0", 0
dict.Add "1", 1
dict.Add "2", 2
dict.Add "3", 3
dict.Add "4", 4
dict.Add "5", 5
dict.Add "6", 6
dict.Add "7", 7
dict.Add "8", 8
dict.Add "9", 9
dict.Add "A", 10
dict.Add "B", 11
dict.Add "C", 12
dict.Add "D", 13
dict.Add "E", 14
dict.Add "F", 15

Dim val As String
Dim dec As Double
val = rngRef.Value

For n = 0 To Len(val) - 1
    dec = dec + dict(Mid(val, Len(val) - n, 1)) * (16 ^ n)
Next n
    
Hex2Dec64 = dec

End Function

Function Dec2Hex64(rngRef As Range) As String
Dim hex As String
Dim n As Double
n = rngRef.Value

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

dict.Add 0, "0"
dict.Add 1, "1"
dict.Add 2, "2"
dict.Add 3, "3"
dict.Add 4, "4"
dict.Add 5, "5"
dict.Add 6, "6"
dict.Add 7, "7"
dict.Add 8, "8"
dict.Add 9, "9"
dict.Add 10, "A"
dict.Add 11, "B"
dict.Add 12, "C"
dict.Add 13, "D"
dict.Add 14, "E"
dict.Add 15, "F"

While (n <> 0)
    Dim tmp As Double
    tmp = 0
    tmp = Modulus(n, 16)
            
    hex = dict(tmp) & hex
    tmp = n / 16
    
    If InStr(1, CStr(tmp), ".") > 0 Then
        n = CDbl(Left(CStr(tmp), InStr(1, CStr(tmp), ".") - 1))
    Else
        n = tmp
    End If
Wend

Dec2Hex64 = hex

End Function

Function Modulus(int1 As Double, int2 As Double) As Double
Dim myInt As Double
myInt = int1 / int2

If InStr(1, CStr(myInt), ".") > 0 Then
    myInt = CDbl("0." & Right(CStr(myInt), Len(CStr(myInt)) - InStr(1, CStr(myInt), ".")))
Else
    myInt = 0
End If
Modulus = myInt * int2
End Function

Answer 2

Thanks for getting the info straight on the number of bits. I typed "VBA" to a search engine and it gave me a table for Visual Basic, not the same. One must read the text. The "Application.WorksheetFunction.Hex2Dec(N)" returns a string, but I made that work. I never did find anything that processed a 64-bit integer correctly, but the string eliminated the need.

Answer 3

The actual core issue here is the limited length of the VBA Long datatype and the fact that VBA numeric types are (almost) all signed. So when you try something like CLng("&H0A1000043") you are trying to interpret that number as a signed 32-bit integer. In such a case, -1593835453 is the correct value. You can check at a hex converter like this one (be sure to set the "Binary Type" to signed 32-bit.

In order to properly pull off this conversion, you'd have to use a larger numeric type, like LongLong (only available in 64-bit Office). E.g. CLngLng("&H0A1000043^")

The reason that Hex2Dec succeeds while these fail is because Hex2Dec is probably directly converting into a string, without having to go through the intermediate internal representation of the number, which is where it gets interpretted as being signed.

Answer 4

You need to start with a String:

Sub BigHexToDecimal()
    Dim N As String

    N = "A1000043"
    MsgBox Application.WorksheetFunction.Hex2Dec(N)
End Sub

Answer 5

Try adding a & to the end of your Hex-String and use the Val-function: MS Documentation