MS chose not to display more than 15 digits because digits beyond the 15th
may not be what you expect (because numbers are stored in binary, not
decimal). Consider dblContainer*lngExpon which you round to produce
dblContner. To 17 figures, the actual values of dblContainer*lngExpon for
your two cases are
10168000.500000000
10163000.500000002
the 1st number ends in exactly 0.5, so it rounds to the nearest even integer
(down). The 2nd number exceeds 0.5, so it rounds up.

VBA did what you told it to do, but that is different than what you wanted
it to do. If I were you, I would look at CDbl(CStr(dblInput*lngExpon)). The
CStr step removes figures beyond the 15th, so that fractional parts should
round up or down depending on whether they are or < 0.5. Fractional values
of exactly 0.5 can round as you choose. You said that you wanted 5's up
rounding, but this approach will also give you more expected results in
"banker's rounding" than the VBA Round function, which does not buffer
against the vagaries of binary representations.

Jerry

"Michiel via OfficeKB.com" wrote:

Hi all,

This rounding issue in VBA drive me crazy!
Many posts report that VBA uses the bankers rounding. But I need the standard
rounding.

So I tried to make my own Mathematical Rounding function. For some
mysterieous reasons it does not always work as expected.

Anyone who likes solving strange behaviour of VBA is kindly invited to see
why.

MathRound(101.68000000000001,5) works OK -- 101.68000
MathRound(102.63000000000001,5) does not work --- 102.63001 (102.63000
expected)

The function:
Function MathRound(dblInput As Double, intDigits As Integer) As Double
'Rounds the input value (DblInput) to the number of digits specified in
intDigits
'The container values dblContainer and dblContner seem to be necessary
because
'without them the calculations are wrongly passed through.
Dim dblContainer As Double
Dim lngExpon As Long
Dim lngLong As Long
Dim dblContner As Double
dblContainer = dblInput + 5 * (10 ^ -(intDigits + 1)) 'Add a bit to make
sure truncation is done right.
lngExpon = 10 ^ intDigits 'calculate the power of ten to be multiplied
based on the number of digits
dblContner = Round(dblContainer * lngExpon, 0) 'PUT value in second
container
lngLong = Fix(dblContner) 'truncate value
MathRound = lngLong / lngExpon 'Devide back to the correct number of
digits
End Function

Thanks!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200809/1