VBA Makro for converting a hex number to decimal float

excel_hex2dec_float.vba

Function HEX2DECFL(strHexVal As String) As Double
'
'   Function to convert 4 byte (32 bit) Hex value
'   to a floating point decimal number
'   using IEE 754
'
'   Dave Stow
'
' Lookup array of hex digits to binary digits
Dim strBinaryDigits() As Variant

' String to put the full binary string into
Dim strBinaryString As String

' Each part of the floating point number
Dim intSign As Integer      ' 1 or -1

Dim strExponentPart As String  ' The bits making up the exponent
Dim intExponent As Integer  ' The exponent itself

Dim strFraction As String   ' The string that makes up the fractional part of the mantissa
Dim dblMantissa As Double   ' The mantissa

Dim i As Integer

' Initialise the lookup tables for making the binary string
strBinaryDigits() = Array( _
            "0000", "0001", "0010", "0011", _
            "0100", "0101", "0110", "0111", _
            "1000", "1001", "1010", "1011", _
            "1100", "1101", "1110", "1111")

' Generate binary string from hex
strBinaryString = ""
For i = 1 To Len(strHexVal)
    strBinaryString = strBinaryString + strBinaryDigits(InStr("0123456789abcdef", Mid(strHexVal, i, 1)) - 1)
Next

' Extract components of floating point number based on:
' bit 31     - sign bit
' bits 30-23 - exponent + 127 (127 is bias in 32 bit floats)
' bits 22-0  - fractional part of mantissa

' -----------------------------------
' Sign - bit 31
If Left(strBinaryString, 1) = "1" Then
    intSign = -1
Else
    intSign = 1
End If

' -----------------------------------
' Exponent - bits 30-23
strExponentPart = Mid(strBinaryString, 2, 8)
intExponent = 0
For i = 1 To 8
    intExponent = intExponent + _
        ((2 ^ (8 - i)) * Val(Mid(strExponentPart, i, 1)))
Next

' Correct for bias of 127
intExponent = intExponent - 127

' -----------------------------------
' Mantissa - bits 22-0
strFraction = Mid(strBinaryString, 10, 23)
dblMantissa = 0
For i = 1 To 23
    ' 2^i is 2, 4, 8, 16 etc
    ' 1/(2^i) is 1/2, 1/4, 1/8 etc
    dblMantissa = dblMantissa + _
        (Val(Mid(strFraction, i, 1)) * (1 / (2 ^ i)))
Next
' Add 1 as leading 1.xxxxxx is implicit
dblMantissa = dblMantissa + 1

' We've got all the bits - put it all together
HEX2DECFL = intSign * (dblMantissa * (2 ^ intExponent))

End Function