Solved: Is It "Round" or "Application.Round"??

[Cyberdude]

I was just modifying some old code and I noticed that some of the macros I had used "Round" and others I had used "Application.Round". Both forms seem to work OK. Is there some subtle difference between the two?

[Bob Phillips]

They use different rounding algorithms, which you should undedrstand if using them. See

http://support.microsoft.com/default.aspx?kbid=194983
PRB: Round Function different in VBA 6 and Excel Spreadsheet

[matthewspatrick]

Thus, if you see:

[VBA]Debug.Print Round(x, 2)[/VBA]

that is the VB/VBA function Round, which uses banker's rounding. If you see:

[VBA]Debug.Print Application.Round(x, 2)
Debug.Print Application.WorksheetFunction.Round(x, 2)[/VBA]

you are using the Excel ROUND function, which always rounds up if the digit you're checking is >= 5.

Patrick

[Ken Puls]

FYI, Round is not supported in Excel 97, although the worksheetfunction version is if called upon in VBA.

[Cyberdude]

xld, that is a GREAT reference on rounding. And it referred to an even better one. Arrrgh! Now I know why I used the spreadsheet Round. The Banker's round is an interesting technique, which I personally would never have a use for. However, in my youth as an engineer, I can look back and recall instances in which the Banker's round would have been more appropriate than the arithmetic round. Thanks to all of you for the informative response!

[mdmackillop]

The Banker's Round.
On deposits they round down, on borrowing they round up. Or have I got it wrong?