Need float2hex Function

Question

Does anyone have an efficient way to convert single precision floating point (IEEE binary32) to a hexadecimal string?  Going the other way is fairly easy, but going from a floating point number is tricky.  The below script is the only way I've been able to do it but as you can see, it's terribly inefficient:

Function float2hex(inVal As Single) As String

  Dim bWd(3) As Byte

  float2hex = ""

  Open "tempfile.bin" For Random As #1 Len = 4

  Put #1, 1, inVal

  Close #1

  Open "tempfile.bin" For Binary As #1

    Get #1, , bWd(3)

    Get #1, , bWd(2)

    Get #1, , bWd(1)

    Get #1, , bWd(0)

  Close #1

  For i = 0 To 3

    If Len(Hex(bWd(i))) = 1 Then

      float2hex = float2hex & "0" & Hex(bWd(i))

    Else

      float2hex = float2hex & Hex(bWd(i))

    End If

  Next i

End Function

Going the other way is much easier:

Function hex2float(inVal As String) As Single

  Dim sign As Integer

  Dim expon As Integer

  Dim mantis As Long

  Dim result As Double

  sign = Excel.WorksheetFunction.Hex2Dec(Left(inVal, 1))

  If (sign And &H8) <> 0 Then sign = -1 Else sign = 1

  expon = Excel.WorksheetFunction.Hex2Dec(Left(inVal, 3))

  expon = (expon And &H7F8) / 2 ^ 3 - 127

  mantis = Excel.WorksheetFunction.Hex2Dec(Right(inVal, 6))

  mantis = mantis And &H7FFFFF

  result = sign * (1 + mantis * 2 ^ -23) * 2 ^ expon

  hex2float = result

End Function

Adding to the complication is making the function easily transportable across platforms since Windows stores type SINGLE variable records in Little Endian format while other platforms may use Big Endian format, while my string output of float2hex is Big Endian.  It just seems goofy to have to open in random, write, close, open in binary, read four times, then close.  It works pretty well as a function called within a cell, but it just seems like there should be a much more efficient way to do this without having to go to Java, Perl, C++ or something else to get to a memcpy type function.

Answer 1

Maybe this link will help...

http://www.mrexcel.com/forum/excel-questions/569375-single-float-ieee-754-32bit-hex-fails.html

Look near the bottom for a routine that seems to work, and an old link to other similar routines by the author of that routine.

Eric

Answer 2

Maybe this link will help...

http://www.mrexcel.com/forum/excel-questions/569375-single-float-ieee-754-32bit-hex-fails.html

Look near the bottom for a routine that seems to work, and an old link to other similar routines by the author of that routine.

Eric

Eric,

Thanks so much for the link.  I did not realize that there was an LSet statement that could be used in this manner, similar to memcpy.  This will solve a number of challenges that I've had to overcome by using byte arrays.  Take care!

• David