• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10942
  • Last Modified:

Convert large Hex to Decimal in VB.NET or Excel

I found some java code to convert Hex to Decimal -- but I'd like to do the equivilent in VB.NET -- can you do it?

        BigInteger b = new BigInteger(hexStr, 16);
        String s = b.toString();

I also found Microsoft's answer to do it in VBA, Excel, but since the number is too big it gets trucated - don't know if there's another option here to make this work for me: http://www.vbaexpress.com/kb/getarticle.php?kb_id=307

Hex Str: 76c55e6970d6843011fa94e4433304ea
Decimal Str: 157873700884132896764748183476385285354
  • 4
  • 2
  • 2
  • +1
1 Solution
Patrick MatthewsCommented:
Hello starship_wars,

Please explain what you mean by "truncated".  Excel and VBA are limited to a maximum of
15 significant digits, and cannot maintain any greater precision than that with numeric data.


I doubt Excel formula will ever do it as the formula "=HEX2DEC(A1)" is limited to a 10 character Hex string.
Try putting this into an Excel  module and trying the formula:
=HEX2DEC("76c55e6970d6843011fa94e4433304ea") in a worksheet in same workbook.

Option Explicit 
Public Function HexToDec(Hex As String) As Double 
    Dim i               As Long 
    Dim j               As Variant 
    Dim k               As Long 
    Dim n               As Long 
    Dim HexArray()      As Double 
    n = Len(Hex) 
    k = -1 
    ReDim HexArray(1 To n) 
    For i = n To 1 Step -1 
        j = Mid(Hex, i, 1) 
        k = k + 1 
        Select Case j 
        Case 0 To 9 
            HexArray(i) = j * 16 ^ (k) 
        Case Is = "A" 
            HexArray(i) = 10 * 16 ^ (k) 
        Case Is = "B" 
            HexArray(i) = 11 * 16 ^ (k) 
        Case Is = "C" 
            HexArray(i) = 12 * 16 ^ (k) 
        Case Is = "D" 
            HexArray(i) = 13 * 16 ^ (k) 
        Case Is = "E" 
            HexArray(i) = 14 * 16 ^ (k) 
        Case Is = "F" 
            HexArray(i) = 15 * 16 ^ (k) 
        End Select 
    Next i 
    HexToDec = Application.WorksheetFunction.Sum(HexArray) 
End Function 

Open in new window

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

starship_warsAuthor Commented:
Yes, I have tried Microsoft's code.  But the problem is that I want the value "157873700884132896764748183476385285354" but instead Excel only shows me: 1.57873700884133E+38

If Excel is limited to 15 digits, does anyone know how I can get the result using VB.NET?
zorvek (Kevin Jones)ConsultantCommented:
I don't get the exact result you listed above but it seems to work correctly:

Public Function GetLargeIntegerFromHex( _
      ByVal HexText As String _
   ) As String
   Dim Base2 As String
   Dim Result As String
   Dim Index As Long
   Dim Bit As Long
   Dim Char As Byte
   Base2 = "1"
   Result = "0"
   HexText = UCase(HexText)
   For Index = Len(HexText) To 1 Step -1
      Char = CLng("&H" & Mid(HexText, Index, 1))
      For Bit = 1 To 4
         If IsBitSet(Char, Bit) Then Result = AddString(Result, Base2)
         Base2 = AddString(Base2, Base2)
      Next Bit
   Next Index
   GetLargeIntegerFromHex = Result

End Function

Public Function AddString( _
      ByVal Value1 As String, _
      ByVal Value2 As String _
   ) As String

   Dim ResultIndex As Long
   Dim OperandIndex As Long
   Dim Result As String
   Dim Operand As String
   Dim Sum As Long
   Dim Carry As Long
   If Len(Value1) > Len(Value2) Then
      Result = Value1
      Operand = Value2
      Result = Value2
      Operand = Value1
   End If
   ResultIndex = Len(Result)
   OperandIndex = Len(Operand)
   Do While OperandIndex > 0
      Sum = CLng(Mid(Result, ResultIndex, 1)) + CLng(Mid(Operand, OperandIndex, 1)) + Carry
      If Sum > 9 Then
         Carry = 1
         Mid(Result, ResultIndex, 1) = Sum - 10
         Carry = 0
         Mid(Result, ResultIndex, 1) = Sum
      End If
      ResultIndex = ResultIndex - 1
      OperandIndex = OperandIndex - 1
   If Carry = 1 Then Result = "1" & Result
   AddString = Result

End Function

Public Function IsBitSet( _
      ByVal Value As Variant, _
      ByVal Position As Long _
   ) As Boolean
   Select Case VarType(Value)
      Case vbLong, vbInteger, vbByte
         IsBitSet = (CLng(Value) And (2 ^ (Position - 1))) > 0
   End Select

End Function

starship_warsAuthor Commented:
Sorry for the delayed response.

Kevin, thank you for the good code example.  Too bad the result is just off a few digits, strange.
zorvek (Kevin Jones)ConsultantCommented:
Are you sure your example is correct? I did some pretty extensive testing with my function.

starship_warsAuthor Commented:

You are right, I tried a different value and they came out the same.
The one above were the values someone else gave me, so they made an ass-u-me.

Zorvek - Kevin - you are a genius!  Thank you again!
starship_warsAuthor Commented:
I was revisting this code and couldn't get the values to compute again.  I finally figured out a fix by modifying the AddString function:
        If Len(Value1) < Len(Value2) Then
            'Make values the same length by left padding with zeros
            Value1 = Value1.PadLeft(Len(Value2), "0")
        End If
        'If Len(Value1) > Len(Value2) Then
        Result = Value1
        Operand = Value2
        '    Result = Value2
        '    Operand = Value1
        'End If
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now