DOCUMENT:Q194983  30-OCT-1998  [vbwin]
TITLE   :PRB: Round Function Different in VBA 6 and Excel Spreadsheet
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:6.0,97
OPER/SYS:WINDOWS
KEYWORDS:kbVBp600 kbVBA600 kbExcel97

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Learning, Professional, and Enterprise Editions
   for Windows, version 6.0
 - Microsoft Excel 97 for Windows
---------------------------------------------------------------------

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
==========


(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by
Malcolm Stewart, Microsoft Corporation.

Additional query words:
======================================================================
Keywords          : kbVBp600 kbVBA600 kbExcel97
Version           : WINDOWS:6.0,97
Platform          : WINDOWS
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.