[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Convert large Hex to Decimal in VB.NET or Excel

Posted on 2007-11-27
9
Medium Priority
?
10,782 Views
Last Modified: 2008-03-21
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
0
Comment
Question by:starship_wars
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20363365
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.

Regards,

Patrick
0
 

Expert Comment

by:kieranjcollins
ID: 20364996
I doubt Excel formula will ever do it as the formula "=HEX2DEC(A1)" is limited to a 10 character Hex string.
0
 

Expert Comment

by:kieranjcollins
ID: 20365059
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

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:starship_wars
ID: 20366538
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?
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 375 total points
ID: 20367738
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
   Else
      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
      Else
         Carry = 0
         Mid(Result, ResultIndex, 1) = Sum
      End If
      ResultIndex = ResultIndex - 1
      OperandIndex = OperandIndex - 1
   Loop
   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

Kevin
0
 

Author Comment

by:starship_wars
ID: 20405614
Sorry for the delayed response.

Kevin, thank you for the good code example.  Too bad the result is just off a few digits, strange.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 20406567
Are you sure your example is correct? I did some pretty extensive testing with my function.

Kevin
0
 

Author Comment

by:starship_wars
ID: 20413583
OMG - I am SO SORRY!!

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!
0
 

Author Comment

by:starship_wars
ID: 20495649
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
        'Else
        '    Result = Value2
        '    Operand = Value1
        'End If
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question