XL97: Indexes for Methods and Collections are Rounded

Last reviewed: March 13, 1998
Article ID: Q159874
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, if you specify a decimal value for the index of a method or collection in a Visual Basic for Applications macro, the index is rounded. This behavior differs from that of earlier versions of Microsoft Excel. In earlier versions, the index is truncated rather than rounded.

Because of this difference, if your macro relies on Microsoft Excel to convert decimal values for the indexes of methods or collections, your macro may provide different results in different versions of Microsoft Excel.

CAUSE

Microsoft Excel 97 utilizes stricter typing for arguments of collections and methods. If an argument is typed as an integer, decimal values that are passed to that argument are automatically rounded.

RESOLUTION

The example below demonstrates a situation where you receive conflicting results between versions of Microsoft Excel.

   MsgBox Sheets(1.6).Name

In Microsoft Excel 97, 1.6 is rounded to 2 and this line of code will reference the second Sheet in the Sheets collection. In Microsoft Excel versions 5.0 and 7.0, the number 1.6 is truncated to 1 and this line of code references the first Sheet in the Sheets collection.

You should not rely on Microsoft Excel to automatically convert your decimal values for indexes. If you require that the index be truncated in all versions of Microsoft Excel, use the INT function:

   MsgBox Sheets(INT(1.6)).Name

If you require that the index be rounded in all versions of Microsoft Excel, use the CInt function:

   MsgBox Sheets(CInt(1.6)).Name


STATUS

This is by design in Microsoft Excel 97.


Additional query words: XL97 8.00 fraction integer convert conversion
whole range offset array
Keywords : kbcode kbprg xlvbainfo
Version : WINDOWS:97
Platform : WINDOWS


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.

Last reviewed: March 13, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.