excelvbafloating-pointtype-conversionbitwise-operators

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.

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