XL5: Incorrect Result Using Large Array in Worksheet Function

ID: Q120405

5.00 WINDOWS kbprg

The information in this article applies to:

Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, if you create a large array in a Visual Basic procedure, and use a worksheet function to return an element of the array, the incorrect value is returned.

CAUSE

This behavior occurs because when you use a worksheet function to access an element in a large array, Microsoft Excel assumes that the elements wrap around, or start again at the 4096th element. Because of this, the value of the 4096th element is returned as 0, the value of the 4097th element is returned as the value of the first element, the value of the 4098th element is returned as the value of the second element, and so on.

This problem occurs when you use the INDEX function to return an element in a large array. For example, if you use the Visual Basic function Test to return an array of 6000 elements, and you use this array in the following function on a worksheet

   =INDEX(Test(),5000)

the value of the 904th element (5000-4096) is returned.

Note that this behavior also occurs if you use the INDEX function in a Visual Basic procedure, as in the following example:

   MsgBox Application.Index(Test(),5000)

Note that this behavior does not occur when you use a Visual Basic procedure to return an element of a large array, as in the following example:

   MsgBox Test(5000)

WORKAROUND

To avoid returning the incorrect value for an element of an array, use a Visual Basic procedure to return the array element when your array contains more than 4096 elements. For example, to return the 5000th element in the array Test, use the following syntax:

   result = Test(5000)

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel versions 5.0c for Windows and 7.0 for Windows 95.

Note that in Microsoft Excel versions 5.0c and 7.0, you cannot use an array that has greater than 4096 elements in a worksheet function. The #VALUE! error value is returned when you use an array with greater than 4096 elements in a function on a worksheet. If you use a worksheet function such as INDEX in a Visual Basic procedure with an array that contains more than 4096 elements, you receive the following error message:

   Run-time error '1004':

   Index method of Application class failed

Use the workaround provided above to avoid these errors.

REFERENCES

For more information about the Function Statement, choose the Search button in the Visual Basic Reference and type:

    Function

KBCategory: kbprg KBSubcategory:

Additional reference words: 1.00 5.00 5.00c 7.00

Version           : 5.00
Platform          : WINDOWS

Last Reviewed: June 1, 1997