Solved

Convert large Hex to Decimal in VB.NET or Excel

Posted on 2007-11-27
10,526 Views
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
Question by:starship_wars
• 4
• 2
• 2
• +1

LVL 92

Expert Comment

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

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

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

Author Comment

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

zorvek (Kevin Jones) earned 125 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

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

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

ID: 20406567
Are you sure your example is correct? I did some pretty extensive testing with my function.

Kevin
0

Author Comment

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

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

Join & Write a Comment Already a member? Login.

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

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

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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!