XL98: ParamArrays Are Always Zero-Based (182647)
The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
This article was previously published under Q182647 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
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:
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.
Modification Type: | Major | Last Reviewed: | 9/11/2002 |
---|
Keywords: | kbdtacode kbprb kbProgramming KB182647 |
---|
|