PRB: Round Function different in VBA 6 and Excel Spreadsheet
This article was previously published under Q194983
This article has been archived. It is offered "as is" and will no longer be updated.
When using the Round() function in Visual Basic 6.0, a different result maybe returned than when using it in a cell formula of an Excel spreadsheet.
The VBA Round() function uses Banker's rounding while the spreadsheet cellfunction 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 VisualBasic for Applications 6, uses Banker's rounding, which rounds .5 either upor 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 window:
? Round(2.5, 0)
- The result is 2.
For additional information on rounding, click the article number below to view the article in the Microsoft Knowledge Base:
196652 HOWTO: Implement Custom Rounding Procedures
Article ID: 194983 - Last Review: 12/05/2015 09:34:05 - Revision: 2.1
Microsoft Visual Basic 6.0 Learning Edition, Microsoft Visual Basic 6.0 Professional Edition, Microsoft Visual Basic 6.0 Enterprise Edition, Microsoft Excel 97 Standard Edition
- kbnosurvey kbarchive kbprb KB194983