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.
The VBA Round() function uses Banker's rounding while the spreadsheet cell
function uses arithmetic rounding.
Write a custom function to get the desired results.
This behavior is by design.
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:
- 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
- The result is 2.
For additional information on rounding, click the article number below
to view the article in the Microsoft Knowledge Base:
HOWTO: Implement Custom Rounding Procedures