VBA Fix Function Discrepancy?

Question

Why do the following two calls to the Fix function produce different results?

Private Sub Test_Fix()
    Dim d As Double

    d = -17.555

    Debug.Print "Fix(" & d & " * 1000) = " & Fix(d * 1000)

    d = d * 1000

    Debug.Print "Fix(" & d & ") = " & Fix(d)

    ' Output: 
    '     Fix(-17.555 * 1000) = -17554
    '     Fix(-17555) = -17555
End Sub

The second of the two calls give the result I would expect.

I assume the difference has something to do with inexact representation of some numbers as type Double, whereby the first call would actually compute something like Fix(-17.554999999). However, it still seems the two should produce consistent results since the d is of type Double.

The documentation mentions that the parameter can be

A number of type Double or any valid numeric expression.

Therefore, I would have assumed that both function calls above would be valid and produce identical results.

I'm not sure exactly what is going on, but a) `Fix(CDbl(d * 1000))` produces `-17555` as expected, even though b) `TypeName(d * 1000)` is `Double` and c) `d * 1000 = CDbl(d * 1000)` yields `True`. — GSerg, Jun 30 '15 at 15:14
@GSerg, that's interesting. I found another example of odd behavior: `Fix(CDbl(1.001 * 1000)) = 1000`, while `Fix(CDbl(1001)) = 1001`, as expected. — neizan, Jun 30 '15 at 15:17
That is not interesting because it's a floating point precision issue where `1.001 * 1000` <> `1001` and both `fix(cdbl(1.001 * 1000))` and `fix(1.001 * 1000)` return `1000`. — GSerg, Jun 30 '15 at 15:21
In the previous example I gave, `Debug.Print CDbl(1.001 * 1000)` prints "1001", but `CDbl(1.001 * 1000) = CDbl(1001)` yields `False`. — neizan, Jun 30 '15 at 15:23
Correct, but this is merely a formatting problem when printing numbers to screen. Internally the numbers are different and it is detectable with `=`. In your original example the numbers are strictly equal as far as the `=` is concerned, and they have the same type, and yet `Fix` returns different values depending on whether or not the value was saved to a variable. It's as if VB uses different floating point precision for intermediate calculations. — GSerg, Jun 30 '15 at 15:26
While the duplicated question is totally different, the underlying reason is the same: 80-bit "extended" precision for intermediate floating-point calculations. — GSerg, Jun 30 '15 at 15:32

VBA Fix Function Discrepancy?

0 Answers0