Excel VBA strange result returning integer of the result of a mathematical sum

[Thebatfink]

Hi, I am scratching my head and can not see what the issue is here..

If I enter a formula in a cell

=INT(559/1.118)

I get the result, 500 as I expect.

If I attempt this in VBA, I consistently get the result 499?? Examples:

Private Sub test()
Dim a As Long
Dim b As Double
Dim c As Double
a = 559
b = 1.118
c = a / b
MsgBox c
MsgBox Int(c)
MsgBox Fix(c)
End Sub

c is returned as 500 as you would expect. But with either Int or Fix, it returns 499 in the message prompt.

Private Sub test2()
MsgBox Int(559 / 1.118)
End Sub

499 is returned in the message prompt.

Why does it do this? I suspect it is something to do with floating point precision perhaps, but I don't understand why exactly?

 

[xplainer]

Could there be a case when thats not possible and how do you know when these errors are then present enough to impact whats coming out at the end

I probably cannot answer to your satisfaction. So this will probably be my last contribution to this thread.

We cannot know ahead of time. The key is to understand the root cause of the problem and to choose a work-around strategy that fits the situation.

The root cause is: most decimal fractions (and integers larger than 2^53) cannot be represented exactly in 64BPF. Instead, they are approximated by a sum of 53 consecutive powers of 2.

And the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number. That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!).

For most calculations that involve or result in decimal fractions, we expect the result to be accurate to some number of decimal places.

In such cases, we can explicitly round calculations to the required number of decimal places (or significant digits or unit of time).

But that did not apply to your situation because you wanted to preserve the full precision of the division.

For your situation, integer arithmetic was a good work-around because the divisor is accurate to a specific number of decimal places.

That might not work when the precision of the divisor is unknown or variable.