Converting Hexadecimal number to floating point decimal number

[Fifi]

I tried the hex2dec function it shows the following results:
Hex Dec
0 0
0f 15
7b 123
a1 161
39 57
But my problem is that these numbers when put together should mea
something like 3.4e-5. I guess I dont remember how to put the
together. Much of these is still manual (stripping off the 0X). The wa
I want to use this is that when the original prog (not Excel) write
these hex # in the form 0x00 0x0f 0x7b 0xa1 0x39, i want excel t
automatically translate that into 3.4e-5. May be that means stackin
each segment into different rows, stripping off the 0x prefixes
convering the resulting 2 digit numbers to decimal for each segment an
combinng all the segments tomake a single number in the form 3.4e-5.
want to do this in VB so its automatic

 

[Ron Rosenfeld]

I tried the hex2dec function it shows the following results:
Hex Dec
0 0
0f 15
7b 123
a1 161
39 57
But my problem is that these numbers when put together should mean
something like 3.4e-5. I guess I dont remember how to put them
together. Much of these is still manual (stripping off the 0X). The way
I want to use this is that when the original prog (not Excel) writes
these hex # in the form 0x00 0x0f 0x7b 0xa1 0x39, i want excel to
automatically translate that into 3.4e-5. May be that means stacking
each segment into different rows, stripping off the 0x prefixes,
convering the resulting 2 digit numbers to decimal for each segment and
combinng all the segments tomake a single number in the form 3.4e-5. I
want to do this in VB so its automatic.

I'm not sure how you are converting your values to get 3.4e-5.

I do have a base conversion routine which will handle "decimals", but if I
concatenate your values to make a number like 0.000F7BA139, the result in base
10 is 2.4e-4 (actually: 0.000236250744819699)

Perhaps some more information would help.




--ron

 

[Myrna Larson]

Hi, Ron:

I'd be interested in seeing your code. Can you post it?

Myrna Larson

 

[Ron Rosenfeld]

Hi, Ron:

I'd be interested in seeing your code. Can you post it?

Myrna Larson

And I'd be interested in any critiques/bugs. Today I realized it gives an
error if the input is a zero.

============================\
Function BaseConvert(num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String

'by Ron Rosenfeld

Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r

On Error GoTo HANDLER

If FromBase > 62 Or ToBase > 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If

If InStr(1, num, "E") And FromBase = 10 Then
num = CDec(num)
End If

'Convert to Base 10
LDI = InStr(1, num, ".") - 2
If LDI = -2 Then LDI = Len(num) - 1

j = LDI

Temp = Replace(num, ".", "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 >= FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i

If r <> 0 Then LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0

ReDim Digits(LDI)

For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i

Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)

If r <> 0 Then
Digits(0) = "."
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If

BaseConvert = Temp & Join(Digits, "")

Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & num)

End Function
=============================


--ron

 

[Myrna Larson]

Looks interesting. Thanks for posting it. I'll see if I can find the bug.

 

[JE McGimpsey]

Mine's a lot less versatile than Ron's, since it only does Hex to
Decimal:

Public Function HexToDec(sInput As String) As Variant
Dim dSum As Double
Dim i As Long
Dim nPower As Long
Dim sChar As String
Dim sDecSep As String

sDecSep = Application.International(xlDecimalSeparator)
sInput = Trim(sInput)
nPower = InStr(sInput, sDecSep) - 2
If nPower = -2 Then nPower = Len(sInput) - 1
For i = 1 To Len(sInput)
sChar = Mid(sInput, i, 1)
If sChar Like "[0-9A-Fa-f]" Then
dSum = dSum + Val("&h" & sChar) * 16 ^ nPower
nPower = nPower - 1
ElseIf sChar = sDecSep Then
'do nothing
Else
HexToDec = CVErr(xlErrNum)
Exit Function
End If
Next i
HexToDec = dSum
End Function

 

[Ron Rosenfeld]

Looks interesting. Thanks for posting it. I'll see if I can find the bug.

Oh, I already found and corrected that one. But I'd be interested in others.


--ron

 

[Ron Rosenfeld]

Mine's a lot less versatile than Ron's, since it only does Hex to
Decimal:

but you use Application.International(xlDecimalSeparator). I think I will add
that to mine.


--ron