Round function in Excel, worksheet function vs VBA

Question

I had an application for returning closest matches to certain values in a large cluster of values( as in my earlier question) and I chose a VBA solution. While in the course of using the said application, I observed that the results for the value of 0.5 were not correct. I had been using the VBA Round funtion which I found to be returning 0 for 0.5 rounded to integer whereas the worksheet round function returns 1. Strangely, the VBA round function returns 2 for 1.5. I had to substitue the worksheet function in place of the VBA one.

Am i missing something?

Answer 1

It's a known issue. The VBA Round() function uses Banker's rounding while the spreadsheet cell function uses arithmetic rounding. Check the details here:

PRB: Round Function different in VBA 6 and Excel Spreadsheet

The workaround proposed by Microsoft is to write a custom function to get the desired results.

Banker's rounding always rounds 0.5 to the nearest even number and is standard in accounting, which is why Excel works that way. Arithmetic rounding rounds 0.5 up to the next number.

Answer 2

You can blame Microsoft for this one: http://support.microsoft.com/kb/194983

Here is a list of ways around it: http://www.excelforum.com/excel-programming/401104-worksheet-rounding-vs-vba-rounding.html

Answer 3

Question answered completely here:

http://stackoverflow.com/questions/137114/how-to-round-in-ms-access-vba#137177