Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. XL2000: ParamArrays Are Always Zero-Based

XL2000: ParamArrays Are Always Zero-Based

View products that this article applies to.

This article was previously published under Q213567

↑ Back to the top

Symptoms

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

↑ Back to the top

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).

This behavior is by design of Microsoft Excel. The behavior of versions of Microsoft Excel earlier than Excel 97 is incorrect.

↑ Back to the top

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 like this 

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

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

↑ Back to the top

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 versions of Microsoft Excel earlier than Excel 97, 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 97 or later, 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 97 or later than it does in versions of Microsoft Excel earlier than Excel 97, 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 and 7.0, the formula returns the value 5. In Microsoft Excel 97 or later, 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 versions of Excel only applies when you call the function in question from a formula in a worksheet cell.

↑ Back to the top

Keywords: KB213567, kbprogramming, kbprb, kbdtacode

↑ Back to the top

Article Info
Article ID : 213567
Revision : 5
Created on : 10/11/2006
Published on : 10/11/2006
Exists online : False
Views : 227