XL97: Indexes for Methods and Collections are Rounded

• 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

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

   MsgBox Sheets(CInt(1.6)).Name

STATUS

This is by design in Microsoft Excel 97.