[SOLVED] Convert IEEE Float hex string to an Excel VBA Single (float)

Convert IEEE Float hex string to an Excel VBA Single (float)

I have an input string read from a device which is an ASCII Hex representation of a 32-bit floating point value.

e.g. "4048f5c3" (--> 3.14)

My question is how can I turn that into a Single variable (32-bit floating point) for calculations in my Excel VBA application?

I can easily convert the string to 32-bit Long (with 32-bit floating point binary representation). That's only useful if it were a step toward a Single.

Dim strIeee754 As String
Dim lLong As Long
Dim sSingle as Single

strIeee754 = "4048f5c3"
lLong = Val("&H" & "4048f5c3")        'as an integer 1,078,523,331

I can also use bitwise functions to extract the components (sign, exponent, and mantissa). Again, only useful if steps toward building a Single.

I got this idea from stackoverflow 9431346 to forceably copy the bit pattern from the Long (above) into a Single. The lines are in my Excel Sheet1 module.

Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
...
CopyMemory sSingle, lLong, 4

But the declaration caused the message Compile error: Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules

Any help toward my goal appreciated.

Solution

Put everything in a standard module:

Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _
    (Destination As Any, Source As Any, ByVal Length As Long)

Function CToSng(lLong As Long) As Single
    Dim sSingle As Single
    CopyMemory sSingle, lLong, 4
    CToSng = sSingle
End Function

Sub TestConv()
    Dim strIeee754 As String
    Dim lLong As Long
    strIeee754 = "4048f5c3"
    lLong = Val("&H" & strIeee754)
    Debug.Print CToSng(lLong)
End Sub