XL98: ParamArrays Are Always Zero-Based

Last reviewed: March 18, 1998
Article ID: Q182647
The information in this article applies to:
  • Microsoft Excel 98 Macintosh Edition

SYMPTOMS

When you call a custom function created in Visual Basic for Applications from a formula in a worksheet cell, the function returns an incorrect result.

CAUSE

This will occur if the custom function being called accepts a ParamArray argument and assumes that the ParamArray is a one-based array (where the first element in the array is element 1), rather than a zero-based array (where the first element in the array is element 0).

WORKAROUND

In many cases, you should be able to work around this problem by decreasing the index within your custom function by one. For example, if your function looks as follows

   Function MyFunc(ParamArray X())
       MyFunc = X(5)
   End Function

you would decrease the index within the parentheses from 5 to 4.

STATUS

This behavior is by design of Microsoft Excel 98 Macintosh Edition. The behavior of earlier versions of Microsoft Excel is incorrect.

MORE INFORMATION

When you write a custom Visual Basic function in Microsoft Excel, the last argument accepted by the function can be declared as a ParamArray. When you do this, the function will accept one or more values and place them in the specified variable as an array. The value(s) can then be used within the function. For example, if you have the following function

   Function Test(X As Integer, ParamArray Y())
       Test = "Hello"
   End Function

when you enter the formula

   =Test(6,7,8,9,10)

in a cell, the first argument (6) will be used as the value of the variable X. The remaining arguments (7, 8, 9, 10) will become elements in the array Y().

In earlier versions of Microsoft Excel, if you call a custom function from a formula in a worksheet cell, and if the function contains a ParamArray, the ParamArray is a one-based array. That is, the first element in the array is element 1. In Microsoft Excel 98 Macintosh Edition, the first element in such an array is element 0. You can demonstrate the difference by entering the following function in a Visual Basic module in Microsoft Excel:

   Function TestIndex(ParamArray T())
       TestIndex = T(3)
   End Function

When you enter this formula:

   =TestIndex(1,3,5,7,9)

the formula will return a different value in Microsoft Excel 98 Macintosh Edition than it does in earlier versions of Microsoft Excel, because the array of values is zero-based, not one-based:

     T() Element Numbers     Array
   Zero-Based    One-Based   Value
   -------------------------------
       0             1         1
       1             2         3
       2             3         5
       3             4         7
       4             5         9

So, in Microsoft Excel 5.0, the formula returns the value 5. In Microsoft Excel 98 Macintosh Edition, the formula returns 7.

Note that arrays declared by ParamArray are always zero-based if called from another Visual Basic macro. The change in behavior between earlier versions of Microsoft Excel and Microsoft Excel 98 Macintosh Edition only applies when you call the function in question from a formula in a worksheet cell.


Additional query words: XL98
Keywords : kbprg kbdta kbdtacode xlvbahowto xlvbainfo xl97vbmigrate
Version : MACINTOSH:98
Platform : MACINTOSH
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.

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