CODEWITHSUNDEEP
excelexcel-2013vba
Leigh
Leigh

Reputation: 133

Conversion from hexadecimal string to Double yields wrong results

I am trying to convert 14 bit hex numbers to decimal.

I have this VBA code.

Option Explicit

Public Function HexadecimalToDecimal(HexValue As String) As Double

Dim ModifiedHexValue As String
ModifiedHexValue = Replace(HexValue, "0x", "&H")

HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function

With numbers like this to convert to decimal

I keep getting random results across large amounts of data. Sometimes spot on other times the numbers are off by 6 or 2.

Upvotes: 1

Views: 2035

Answers (2)

guitarthrower
guitarthrower

Reputation: 5834

The problem isn't with VBA. Excel cells can only hold 15 digits in number format. So the "number" 1234567891234567 will always display 1234567891234560. This can be avoided by converting items to text AND/OR changing the cell format to text.

But this doesn't always work.

The only surefire way to make sure it will retain all digits is to append something onto the string that isn't a number.

This code will append an apostrophe before the number, but return the entire string. 

Public Function HexadecimalToDecimal(HexValue As String) As String

    Dim ModifiedHexValue As String
    ModifiedHexValue = Replace(HexValue, "0x", "&H")

    HexadecimalToDecimal = "'" & CDec(ModifiedHexValue)
End Function

Unfortunately, not a perfect solution.

Upvotes: 1

Brian Camire
Brian Camire

Reputation: 4825

Try changing the return type of the function from Double to Variant. Double has only about 15 decimal digits of precision, so can't, for example, capture the value 1261213964639872 (which has 16 digits) exactly. The closest it can get is 1261213964639870. By changing the return type to Variant, the full precision returned by CDec will be preserved. You can't use a Decimal return type, because VBA for some reason does not support this.

Upvotes: 2

Related Questions