DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 194983 - Last Review: March 7, 2005 - Revision: 2.1

This article was previously published under Q194983

On This Page

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.

CAUSE

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

RESOLUTION

Write a custom function to get the desired results.

STATUS

This behavior is by design.

MORE INFORMATION

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

  1. In Excel, open a new spreadsheet and type the following formula into one of the cells:
           =Round(2.5, 0)
    						
  2. The result is 3.
  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)
    						
  4. The result is 2.

REFERENCES

For additional information on rounding, click the article number below to view the article in the Microsoft Knowledge Base:
196652  (http://support.microsoft.com/kb/196652/EN-US/ ) HOWTO: Implement Custom Rounding Procedures



APPLIES TO
  • 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
Keywords: 
kbprb KB194983
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support