# 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
###### Who is Participating?

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)
Next Bit
Next Index

GetLargeIntegerFromHex = Result

End Function

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

Commented:
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

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

Commented:
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 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?
0

Author 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.
0

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

Kevin
0

Author Commented:
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 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
End If
'If Len(Value1) > Len(Value2) Then
Result = Value1
Operand = Value2
'Else
'    Result = Value2
'    Operand = Value1
'End If
0
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.