excelvbahexbase-conversion

Hexadecimal to Decimal In Excel


I am trying to convert a Hexadecimal value to a decimal one in excel with this reference.

Used parameters and formulas:

Input Hex value : 0x044F3B9AFA6C80 and expected output : 1213017328610432

  • Method1: Applying excel formula

'=HEX2DEC(RIGHT(D3164,10))+HEX2DEC(MID(D3164,3,4))*POWER(16,10)'

Actual output : 1213017328610430

  • Method2: Using VBA macro:
    ' 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

Actual output : 1213017328610430

When I try to convert this value with online conversion tool or with python script, it covert expected decimal value.

Please any hint on issue will be more helpful.


Solution

  • Thank you very much everyone. Finally I can able to covert hex to dec value with more then 16 digit. Excel only shows 16 digits in their each cells, so I have converted number into string helps me to present expected value in the cells.

    Please find the final VBA code for any further reference.

    ' 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 String
    
        ' If hex starts with 0x, replace it with &H to represent Hex that VBA will understand
        Dim ModifiedHexValue As String
        Dim dubl As Double
        Dim var As Variant
        
        ModifiedHexValue = Replace(HexValue, "0x", "&H")
        
        dubl = CDec(ModifiedHexValue)
        var = CDec(ModifiedHexValue)
        HexadecimalToDecimal = CStr(var)
    
    End Function