14 digit Hex to decimal in Excel

I have 14 digit Hexadecimal numbers in Excel which I want to convert to a decimal number.

For example in C2 the number is 0438E96A095180 and that has to be 1188475064373632 in decimal.

I have tried a module in VBA but that is not working:

' Force explicit declaration of variables
Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Double
Public Function HexadecimalToDecimal(HexValue As String) As Double
' If hex starts with 0x, replace it with &H to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = Replace(HexValue, "0x", "&H")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function

With that I get the decimal number 1188475064373630 instead of 1188475064373632.

What am I doing wrong?

1 Answer

You will need to return the value as a string, at least where the result has more than 15 digit precision.

eg:

Option Explicit
' Convert hex to decimal
' In: Hex in string format
' Out: Decimal in string format
Public Function HexadecimalToDecimal(HexValue As String) As String
' If hex starts with 0x, remove it to represent Hex that VBA will understand
Dim ModifiedHexValue As String
ModifiedHexValue = "&H" & Replace(HexValue, "0x", "")
HexadecimalToDecimal = CDec(ModifiedHexValue)
End Function

I will leave it to you to test the length and decide if you want to return a string or a number; and you will note that I modified your &H adding routine a bit.