CODEWITHSUNDEEP
vbaexcelhex
Aston
Aston

Reputation: 21

7 Byte Hex to Dec Conversion

I need to convert 7 byte hexadecimal values in an Excel column to the decimal equivalents in an adjacent column.

I have over 2000 unique values to convert from hexadecimal to decimal.

I got as far as using Excel's hex2bin and then bin2dec formulas.
I found that Excel is rounding up the least significant 4 decimal places.

Example:
7Byte Hex: 0x803277323A8904
Excel Output: 36084284544158000
Correct Decimal Number: 36084284544157956

Upvotes: 2

Views: 1805

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

This is a small variation of Rick Rothstein's code

Function HexToDecs(ByVal HexString As String) As String
  Dim X As Integer
  Dim BinStr As String
  Const BinValues = "0000000100100011010001010110011110001001101010111100110111101111"
  If Left$(HexString, 2) Like "&[hH]" Then
    HexString = Mid$(HexString, 3)
  End If
  If Len(HexString) <= 23 Then
    For X = 1 To Len(HexString)
      BinStr = BinStr & Mid$(BinValues, 4 * Val("&h" & Mid$(HexString, X, 1)) + 1, 4)
    Next
    HexToDecd = CDec(0)
    For X = 0 To Len(BinStr) - 1
      HexToDecd = HexToDecd + Val(Mid(BinStr, Len(BinStr) - X, 1)) * 2 ^ X
    Next
  Else
    ' Number is too big, handle error here
  End If
  HexToDecs = CStr(HexToDecd)
End Function

enter image description here

NOTE:

  • This UDF() returns a String representation of the integer to avoid the 15 digit limitation to true numeric values.
  • I have elected not to start my input string with 0x

Upvotes: 2

Vityata
Vityata

Reputation: 43585

Excel maximum number of digits. In Excel spreadsheet, there is a limit for storing a number in a Cell, which is 15 digits (15 numbers) regardless of whether the numbers are decimal places. Excel call this “15 significant digits of precision” which adheres to “IEEE 754”.Feb 24, 2015

In order to have 36084284544157956, which has 17 digits, save the cell as a Text.

Even VBA does not like displaying such big numbers:

Public Sub TestMe()

    Dim inputString As String: inputString = "123456789012345678"
    Dim someValue As Double

    someValue = inputString
    Debug.Print someValue + 1

End Sub

gets: 1,23456789012346E+17

To present the text value in Excel cell, make sure that you format the cell before putting the text in it:

Option Explicit

Public Sub TestMe()

    With Range("D2")
        .NumberFormat = "@"
        .Value2 = "123456789012345678"
    End With

End Sub

Upvotes: 0

Related Questions