Rounding in VBA and Excel

You never know how Microsoft may surprise you. If you think that everything is so obvious try using Round function as an Excel formula and in VBA. Typing in spreadsheet

=Round(2.5, 0)

will give you as a result 3. While “the same” function in VBA

? Round(2.5, 0)

will result in 2. This feature  was included in VBA 6 and Excel 97 – as far as I remember it was also Excel 2003 but I am not sure. It has been corrected in Excel 2010, and the case like above the spreadsheet function returns 2 and is consistent with the value returned in VBA.

There is even a KB document covering this mistake, see KB194983.

Leave a Reply