Single Float to IEEE-754 32bit hex fails

[Viper7]

Hello VBAers -

I built an Excel 2010 VBA application using the great routines sitting at:

http://www.tek-tips.com/faqs.cfm?fid=6404

These convert IEEE 32 bit hex values to/from single precison Excel numbers. I might be doing something wrong, but most times the Function i3efp(num_in) under FAQ Item # 4 works perfectly. When I say perfectly I mean it matches what I get from a nice on-line web<NOBR style="COLOR: #2b65b0" id=itxthook3w2nobr class="itxtrst itxtrstnobr itxthooknobr">based</NOBR> conversion utility at:
http://babbage.cs.qc.edu/IEEE-754/Decimal.html

I find i3efp() works great for passed in values like 30, 35.5, 45, 50, and 59.1234. However, I get a mismatch with the above web tool for numbers like 35.1 and 133. Then i3efp() fails for numbers like 32, 64, and 128.

When 32 comes in, the internal "e" becomes 132, and "f" becomes 0.
An "f" of zero bombs during an internal "Right()" call inside the function.

Am I doing something wrong? Anyone have a fix for the Function called i3efp( ) for the mismatches and/or downright failures?

I have put a demo spreadsheet on MediaFire, an Excel 2010 XLSM at

http://www.mediafire.com/?kq3v78ayyiaa124

Thanks in advance,
Viper

 

[shg]

Here's the function I use for that:

Type uai4: ai(0 To 3) As Byte: End Type
Type uFlt: f        As Single: End Type
 
Function Sng2Hex(f As Single, Optional sSep As String = "") As String
    ' shg 2007
 
    ' Returns the conversion of float f to a hex string
    ' E.g., Sng2Hex(0.5) returns 3F000000
 
    ' UDF or VBA
    Dim uf          As uFlt
    Dim uai         As uai4
    Dim i           As Long
 
    uf.f = f
    LSet uai = uf
    For i = 0 To 3
        Sng2Hex = Right("0" & Hex(uai.ai(i)), 2) & sSep & Sng2Hex
    Next i
    Sng2Hex = Left(Sng2Hex, Len(Sng2Hex) - Len(sSep))
End Function

E.g., =Sng2Hex(59.1234) returns 426C7E5D

 

[Viper7]

Dear shg:

Your solution works perfect! In all the cases tested your
code matches up with that web based conversion tool.

Now I know why they say, "Don't Mess with Texas!" and
why you're an "Excel MVP" !!

Many many thanks to you (and this Forum/Website) and
best regards,

Viper

 

[shg]

Wow, I like effusive praise! You're welcome, glad it worked for you.

That's one of a family of related routines. The rest are at http://www.box.net/shared/530h20x44e

 

[Viper7]

A minor followup: In a way similar to the aforementioned bug in i3efp() discussed above, there is a bug in i3e2p() too, at least in the version sitting at that other forum at http://www.tek-tips.com/faqs.cfm?fid=6404 in August 2011.

I discovered the hard (embarrassing) way that when a hex string of "3B03126F" is passed to i3e2p() it returns the meaningfully erroneous 0.002421875 when the correct response would be something extremely close to: 0.002000000094994026 Yes it worked fine for a few hundred other values, but not 0.00200000

Bottom line: Don't trust the unfixed versions of i3efp() OR i3e2p() kicking around on the web. As of Aug 2011, I do not think there are any fixed versions out there so instead use Mr. SHG's functions mentioned above instead.

Thanks SHG!

Viper7

 

[oliverg]

Hi shg.

Thank you very much for your posted code. It teached me, helped me, and saved me a lot of working time. It works for me.

Regards
Oliver.