In part, the answer is: apparently, VBA does not do simple banker's rounding when converting Double to Currency, which is rounded to 4 decimal places.
The following demonstrates that both banker's and normal rounding are consistent.
VBA Code:
Sub testCur()
Dim i As Variant, d As Double
Dim c1 As Currency, c2 As Currency, c3 As Currency
Dim c4i As Long, c4f As Long
Debug.Print "double", "implicit", "banker's", "normal", "sim cur"
For Each i In Array(0, 1, 2, 3, 4, 5, 6, 8, 9)
d = i + 0.22225
c1 = d ' implicit rounding
c2 = Round(d, 4) ' banker's rounding
c3 = Format(d, "0.0000") ' normal rounding
c4i = Int(d) ' simulate type Currency
c4f = (d - Int(d)) * 10000
Debug.Print d, c1, c2, c3, c4i & "." & c4f
Next
MsgBox "done"
End Sub
Result:
Rich (BB code):
double implicit banker's normal sim cur
0.22225 0.2223 0.2222 0.2223 0.2223
1.22225 1.2223 1.2222 1.2223 1.2223
2.22225 2.2222 2.2222 2.2223 2.2222
3.22225 3.2222 3.2222 3.2223 3.2222
4.22225 4.2222 4.2222 4.2223 4.2222
5.22225 5.2222 5.2222 5.2223 5.2222
6.22225 6.2222 6.2222 6.2223 6.2222
8.22225 8.2223 8.2222 8.2223 8.2223
9.22225 9.2223 9.2222 9.2223 9.2223
Note that the "banker's" and "normal" columns have consistent and expected results [1].
Also note that the "implicit" and "sim cur" columns have consistent results.
For the "sim cur" column, I speculate how that the integer part and the 4-digit fractional part scaled by 10000 are converted separately from the original type Double, instead of the type Double after banker's rounding to 4 decimal places.
The bottom line is: for consistent results [1], explicitly banker's round the right-hand side with VBA Round, because apparently VBA does not (sigh).
But I believe the following is a more-reliable way to do the banker's rounding:
c2 = Round(
CDec(d), 4)
-----
[1] The conversion from type Double (d - Int(d))*10000 to type Long should use banker's rounding.
So we return to the question: why the inconsistency results for 0.22225, 1.22225, 8.22225 and 9.22225?
In part, the answer is: most decimal fractions (0.22225) cannot be represented exactly in 64-bit binary floating-point. And the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number.
I also believe that the banker's rounding is based on the internal 80-bit binary floating-point representation of type Double calculation (in Intel-compatible CPUs).
For 8.22225 and 9.22225, we can see the effect by debug.printing (d - Int(d)) * 10000). The result appears to be 2222.50000000001 instead of 2222.5. With banker's rounding, that rounds up to 2223 because the fractional part is not 0.5.
We cannot see this with 0.22225 and 1.22225, in part because Excel and VBA format only the first 15 significant digits (rounded).
I could explain this in more detail. But it is TMI for most people.
I believe the following is a more-reliable way to convert the 4-digit fractional part scaled by 10000 to type Long:
c4f = Round((
CDec(d) - Int(d)) * 10000, 0)