XL2000: ParamArray Must Be Declared as an Array of Variant (213564)

MORE INFORMATION

When you write a custom Visual Basic subroutine 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, so that they 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
				

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

When you declare a variable as a ParamArray in versions of Microsoft Excel earlier than Excel 97, you can sometimes omit the open and close parentheses immediately following the variable. Specifically, if the variable is declared within a Declare statement, the parentheses may be omitted. For example:

Declare Function MyFunc Lib "XYZ.DLL" (X As String, ParamArray Y As
    Variant) As Variant
				

This Declare statement will work in versions of Microsoft Excel earlier than Excel 97, but it will not work in Microsoft Excel 2000. If you attempt to run any macros when this statement is present, you will receive the error message shown in the "Symptoms" section of this article.

In order for the statement to work, you must add open and close parentheses immediately after the variable name, as follows:

Declare Function MyFunc Lib "XYZ.DLL" (X As String, ParamArray Y() As
    Variant) As Variant
				

Also, note that ParamArrays must be declared either as type Variant or as no type at all:

   Valid ParamArrays           Invalid ParamArrays
   -----------------------------------------------------
   ParamArray A()              ParamArray C() As Integer
   ParamArray B() As Variant   ParamArray D() As Double
                               ParamArray E() As String
                               ParamArray F() As Boolean
                               ParamArray G() As Long
				

For additional information about ParamArrays, click the article number below to view the article in the Microsoft Knowledge Base: