PDA

View Full Version : Solved: Is It "Round" or "Application.Round"??



Cyberdude
01-31-2006, 11:15 AM
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?

xld
01-31-2006, 11:38 AM
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
01-31-2006, 11:51 AM
Thus, if you see:

Debug.Print Round(x, 2)

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

Debug.Print Application.Round(x, 2)
Debug.Print Application.WorksheetFunction.Round(x, 2)

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

Patrick

Ken Puls
01-31-2006, 12:36 PM
FYI, Round is not supported in Excel 97, although the worksheetfunction version is if called upon in VBA.

:)

Cyberdude
02-03-2006, 02:25 PM
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
02-03-2006, 02:29 PM
The Banker's Round.
On deposits they round down, on borrowing they round up. Or have I got it wrong?:devil: