0

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?

  • You might use the CInt function. – harrymc Nov 21 '18 at 11:07
  • Thank you AFH, I have made the changes you suggested. Unfortunatly it still does not work. all the last numbers end with a 0 instead of the number it should be. – Ben Nov 21 '18 at 11:24
  • Try to use the variant datatype instead of Double. – harrymc Nov 21 '18 at 11:34
  • Excel only has 15 digit decimal precision, although VBA can have higher precision using the Decimal datatype. To return a value with greater than 15 digits, you will need to return a string. eg: HexadecimalToDecimal = CStr(CDec(ModifiedHexValue)). And you may need to ensure a starting &H in all cases. – Ron Rosenfeld Nov 21 '18 at 12:10
1

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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