Results 1 to 5 of 5

Single Float to IEEE-754 32bit hex fails

This is a discussion on Single Float to IEEE-754 32bit hex fails within the Excel Questions forums, part of the Question Forums category; 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 ...

  1. #1
    New Member
    Join Date
    Jul 2011
    Posts
    8

    Default Single Float to IEEE-754 32bit hex fails

    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 webbased 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
    Last edited by Viper7; Aug 4th, 2011 at 03:34 PM. Reason: dropped half a sentence

  2. #2
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,282

    Default Re: Single Float to IEEE-754 32bit hex fails

    Here's the function I use for that:

    Code:
    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

  3. #3
    New Member
    Join Date
    Jul 2011
    Posts
    8

    Thumbs up Re: Single Float to IEEE-754 32bit hex fails

    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

  4. #4
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,282

    Default Re: Single Float to IEEE-754 32bit hex fails

    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
    Last edited by shg; Aug 4th, 2011 at 07:04 PM.

  5. #5
    New Member
    Join Date
    Jul 2011
    Posts
    8

    Default Re: Single Float to IEEE-754 32bit hex fails

    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

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com