0

While writing a program to display a diagram I came across something that I can't find an explanation for and I would appreciate someone explaining to me whether this is a bug, or a configuration option, or other, and how I might resolve or work around the problem.

The problem scope for me is with the Shape object and the Top, Bottom, Width and Height properties and as you will see from the output it seems weird.

Here is a piece of code I wrote to illustrate the problem in Microsoft Excel 2007 and below is the output.

Sub Main()
    Dim S As Shape
    Dim T As Single
    Set S = Worksheets(1).Shapes.AddShape(msoShapeRectangle, 0, 0, 20, 20)
    For T = 0 To 40 Step 0.2
        S.Top = T           ' This is the assignment
        If S.Top = T Then   ' I would expect this to always be true
            Debug.Print "Yes: " & S.Top & " = " & T
        Else
            Debug.Print "No:  " & S.Top & " <> " & T
        End If
    Next
End Sub

And here is the output

Yes: 0.2 = 0.2
Yes: 0.4 = 0.4
Yes: 0.6 = 0.6
Yes: 0.8 = 0.8
Yes: 1 = 1
Yes: 1.2 = 1.2
No:  1.4 <> 1.4
No:  1.6 <> 1.6
No:  1.8 <> 1.8
No:  2 <> 2
No:  2.2 <> 2.2
No:  2.4 <> 2.4
No:  2.6 <> 2.6
No:  2.8 <> 2.8
No:  3 <> 3
No:  3.2 <> 3.200001
No:  3.4 <> 3.400001
No:  3.6 <> 3.600001
No:  3.8 <> 3.800001
No:  4 <> 4
No:  4.2 <> 4.2
Yes: 4.4 = 4.4
No:  4.599921 <> 4.6
No:  4.799921 <> 4.8
No:  4.999921 <> 5
No:  5.199921 <> 5.199999
No:  5.399921 <> 5.399999
No:  5.599921 <> 5.599999
No:  5.799921 <> 5.799999
No:  5.999921 <> 5.999999
No:  6.199921 <> 6.199998
No:  6.399921 <> 6.399998
No:  6.599921 <> 6.599998
No:  6.799921 <> 6.799998
No:  6.999921 <> 6.999998
No:  7.199921 <> 7.199997
No:  7.399921 <> 7.399997
No:  7.599921 <> 7.599997
No:  7.799921 <> 7.799997
No:  7.999921 <> 7.999997
No:  8.199922 <> 8.199997
No:  8.399921 <> 8.399997
No:  8.599921 <> 8.599997
No:  8.799921 <> 8.799996
No:  8.999921 <> 8.999996
No:  9.199922 <> 9.199996
No:  9.399921 <> 9.399996
No:  9.599921 <> 9.599996
No:  9.799921 <> 9.799995
No:  9.999921 <> 9.999995
No:  10.19992 <> 10.2
No:  10.39992 <> 10.39999
No:  10.59992 <> 10.59999
No:  10.79992 <> 10.79999
No:  10.99992 <> 10.99999
No:  11.19992 <> 11.19999
No:  11.39992 <> 11.39999
No:  11.59992 <> 11.59999
No:  11.79992 <> 11.79999
No:  11.99992 <> 11.99999
No:  12.19992 <> 12.19999
No:  12.39992 <> 12.39999
No:  12.59992 <> 12.59999
No:  12.79992 <> 12.79999
No:  12.99992 <> 12.99999
No:  13.19992 <> 13.19999
No:  13.39992 <> 13.39999
No:  13.59992 <> 13.59999
No:  13.79992 <> 13.79999
No:  13.99992 <> 13.99999
No:  14.19992 <> 14.19999
No:  14.39992 <> 14.39999
No:  14.59992 <> 14.59999
No:  14.79992 <> 14.79999
No:  14.99992 <> 14.99999
No:  15.19992 <> 15.19999
No:  15.39992 <> 15.39999
No:  15.59992 <> 15.59999
No:  15.79992 <> 15.79999
No:  15.99992 <> 15.99999
No:  16.19992 <> 16.19999
No:  16.39992 <> 16.39999
No:  16.59992 <> 16.59999
No:  16.79992 <> 16.79999
No:  16.99992 <> 16.99999
No:  17.19992 <> 17.19999
No:  17.39992 <> 17.39999
No:  17.59992 <> 17.59999
No:  17.79992 <> 17.8
No:  17.99992 <> 18
No:  18.19992 <> 18.2
No:  18.39992 <> 18.4
No:  18.59992 <> 18.6
No:  18.79992 <> 18.8
Yes: 19 = 19
Yes: 19.2 = 19.2
No:  19.4 <> 19.4
No:  19.6 <> 19.6
No:  19.8 <> 19.8
No:  20 <> 20
No:  20.2 <> 20.2
No:  20.4 <> 20.40001
No:  20.6 <> 20.60001
No:  20.8 <> 20.80001
No:  21 <> 21.00001
No:  21.2 <> 21.20001
No:  21.4 <> 21.40001
No:  21.6 <> 21.60001
No:  21.8 <> 21.80001
No:  22 <> 22.00001
No:  22.2 <> 22.20001
No:  22.4 <> 22.40001
No:  22.6 <> 22.60001
No:  22.8 <> 22.80001
No:  23 <> 23.00002
No:  23.2 <> 23.20002
No:  23.4 <> 23.40002
No:  23.6 <> 23.60002
No:  23.8 <> 23.80002
No:  24 <> 24.00002
No:  24.2 <> 24.20002
No:  24.4 <> 24.40002
No:  24.6 <> 24.60002
No:  24.8 <> 24.80002
No:  25 <> 25.00002
No:  25.2 <> 25.20002
No:  25.4 <> 25.40002
No:  25.6 <> 25.60003
No:  25.8 <> 25.80003
No:  26 <> 26.00003
No:  26.2 <> 26.20003
No:  26.4 <> 26.40003
No:  26.6 <> 26.60003
No:  26.8 <> 26.80003
No:  27 <> 27.00003
No:  27.2 <> 27.20003
No:  27.4 <> 27.40003
No:  27.6 <> 27.60003
No:  27.8 <> 27.80003
No:  28 <> 28.00003
No:  28.2 <> 28.20004
No:  28.4 <> 28.40004
No:  28.6 <> 28.60004
No:  28.8 <> 28.80004
No:  29 <> 29.00004
No:  29.2 <> 29.20004
No:  29.4 <> 29.40004
No:  29.6 <> 29.60004
No:  29.8 <> 29.80004
No:  30 <> 30.00004
No:  30.2 <> 30.20004
No:  30.4 <> 30.40004
No:  30.6 <> 30.60004
No:  30.8 <> 30.80005
No:  31 <> 31.00005
No:  31.2 <> 31.20005
No:  31.4 <> 31.40005
No:  31.6 <> 31.60005
No:  31.8 <> 31.80005
No:  32 <> 32.00005
No:  32.2 <> 32.20005
No:  32.4 <> 32.40005
No:  32.6 <> 32.60005
No:  32.8 <> 32.80005
No:  33 <> 33.00005
No:  33.2 <> 33.20005
No:  33.4 <> 33.40005
No:  33.6 <> 33.60006
No:  33.8 <> 33.80006
No:  34 <> 34.00006
No:  34.2 <> 34.20006
No:  34.4 <> 34.40006
No:  34.6 <> 34.60006
No:  34.8 <> 34.80006
No:  35 <> 35.00006
No:  35.2 <> 35.20006
No:  35.4 <> 35.40006
No:  35.6 <> 35.60006
No:  35.8 <> 35.80006
No:  36 <> 36.00006
No:  36.2 <> 36.20007
No:  36.4 <> 36.40007
No:  36.6 <> 36.60007
No:  36.8 <> 36.80007
No:  37 <> 37.00007
No:  37.2 <> 37.20007
No:  37.4 <> 37.40007
No:  37.6 <> 37.60007
No:  37.8 <> 37.80007
No:  38 <> 38.00007
No:  38.2 <> 38.20007
No:  38.4 <> 38.40007
No:  38.6 <> 38.60007
No:  38.8 <> 38.80008
No:  39 <> 39.00008
No:  39.2 <> 39.20008
No:  39.4 <> 39.40008
No:  39.6 <> 39.60008
Yes: 39.80008 = 39.80008
Tai
  • 1

1 Answers1

1

In a nutshell, its due to the magic of floating point numbers. When you compare two floats after the same operation, the further away from 0 you are, the less likely you will get the same results.

So, its not a glitch, its just how floats work.

This may bring another question: "So why use floats if they aren't as accurate?"

Performance and scalability. Check out this link to a similar question: https://gamedev.stackexchange.com/questions/3351/about-floating-point-precision-and-why-do-we-still-use-it

Community
  • 1
  • 1
Justin Self
  • 6,137
  • 3
  • 33
  • 48