+ Reply to Thread
Results 1 to 8 of 8

Comparing double values returns false - floating-point expressions

  1. #1
    Registered User
    Join Date
    06-04-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    6

    Comparing double values returns false - floating-point expressions

    Hi, I can't even begin to describe what's happening to my code. I'm going crazy.

    Basically a lot of my previously working code has stopped functioning correctly. I tracked down the bug in this example (see attachment )and I know what is not working, but why?????

    Apparently it stops working when it gets to cell A36 for no apparent reason

    Please help me...
    Attached Files Attached Files
    Last edited by ericds; 12-15-2014 at 03:22 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,465

    Re: HELP, please, huge bug with VBA is driving me crazy!!!!

    In what way does it fail for you. It runs (for me) and displays a message box with the value 176.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    06-04-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: HELP, please, huge bug with VBA is driving me crazy!!!!

    Thanks for having a look,

    with me it does not recognize 0.70=0.70, the line 'MsgBox VBAalacon = Merde' displays false for line 36. I don't know what this is.
    If it works for you this means it's because my PC is ****, just got a new one from my office. any idea on how to make it the way it was???

    Thanks

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: HELP, please, huge bug with VBA is driving me crazy!!!!

    It's hard to work out exactly what you are trying to achieve...

    Looks like there's floating point arithmetic calculation errors happening, though. You can try rounding the values you are comparing.

    Unpicking and rewriting your code, does this do what you want?
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: HELP, please, huge bug with VBA is driving me crazy!!!!

    Its.. famous.. Double value Precision point problem..

    basic of Data Type Conversion..
    Check this one..
    http://msdn.microsoft.com/en-us/libr...t8(VS.80).aspx

    and for further WTF (When you have Time For reading) check this one..

    http://www.cygnus-software.com/paper...ringfloats.htm
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  6. #6
    Registered User
    Join Date
    06-04-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: HELP, please, huge bug with VBA is driving me crazy!!!!

    Okay, so my code did not work for you too?

    I am not trying to achieve anything with this code, I just did not understand why these values are not the same when they look exactly the same? How come there are floating point errors in a numbers that I got from excel (collumn a is generated by pulling down 0 and 0.02)...

    your code works, but how is this possible, does this mean each time I compare two values I have to round them off???

  7. #7
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,244

    Re: HELP, please, huge bug with VBA is driving me crazy!!!!

    Read Debraj's links in post #5.

  8. #8
    Registered User
    Join Date
    06-04-2013
    Location
    belgium
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: HELP, please, huge bug with VBA is driving me crazy!!!!

    Wuuuut? this is crazy, I guess i will have to round of everything. I'm gonna rename my thread to the title of your article debraj

    I've been using VBA for 3 years now and this is the first time I've encountered this problem...

    thanks everyone


    edit:

    in order to avoid this an option can be clicked somewhere in excel:

    http://support.microsoft.com/kb/214118
    Last edited by ericds; 12-15-2014 at 03:38 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If statement is driving me crazy
    By bobsmith123 in forum Excel General
    Replies: 3
    Last Post: 04-02-2014, 10:40 PM
  2. Printing help. Ugh! Driving me crazy!
    By shootertec in forum Excel General
    Replies: 5
    Last Post: 05-15-2009, 03:36 PM
  3. This is driving me crazy!!
    By rtausch in forum Excel General
    Replies: 0
    Last Post: 10-15-2008, 06:33 PM
  4. please please help driving me crazy
    By jilly jess in forum Excel General
    Replies: 1
    Last Post: 01-14-2007, 09:59 AM
  5. Driving me crazy!
    By RobEdgeler in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2005, 05:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1