Select the product you need help with

PRB: Round Function different in VBA 6 and Excel Spreadsheet

Article ID: 194983 - View products that this article applies to.

This article was previously published under Q194983

SYMPTOMS

When using the Round() function in Visual Basic 6.0, a different result may be returned than when using it in a cell formula of an Excel spreadsheet.

Back to the top | Give Feedback

CAUSE

The VBA Round() function uses Banker's rounding while the spreadsheet cell function uses arithmetic rounding.

Back to the top | Give Feedback

RESOLUTION

Write a custom function to get the desired results.

Back to the top | Give Feedback

STATUS

This behavior is by design.

Back to the top | Give Feedback

The Round() function in an Excel spreadsheet uses Arithmetic rounding, which always rounds .5 up (away from 0). The Round() function in Visual Basic for Applications 6, uses Banker's rounding, which rounds .5 either up or down, whichever will result in an even number.

Steps to Reproduce Behavior

In Excel, open a new spreadsheet and type the following formula into one of the cells:
=Round(2.5, 0)
The result is 3.
In Visual Basic 6.0 or other applications using VBA 6, open a new project and type the following expression into the Debug or Immediate window:
? Round(2.5, 0)
The result is 2.

Back to the top | Give Feedback