XL97: ParamArray Must be Declared as an Array of Variant

Last reviewed: February 27, 1998
Article ID: Q158355
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SYMPTOMS

When you attempt to run a Visual Basic for Applications macro in Microsoft Excel 97, or while you are editing code in a Visual Basic module, you may receive the following error message:

   Compile error:
   ParamArray must be declared as an array of Variant

CAUSE

This will occur if you have declared a variable as a ParamArray and either of the following conditions are true:

  • The variable name is not immediately followed by an open and a close parenthesis, like this:

          ParamArray MyVar()
    

        -or-
    
  • The open and close parentheses that follow the variable name are followed by "As <vartype>", where <vartype> is any variable type other than Variant.

RESOLUTION

In Microsoft Excel 97, variables declared as ParamArrays must be immediately followed by an open and a close parenthesis, and must be declared either as type Variant or as no type at all. Note that if a data type is not specified, the variable will default to the Variant data type.

Making this change will allow your macros to work in all versions of Microsoft Excel that support the Visual Basic for Applications macro language, including Microsoft Excel 97.

STATUS

This behavior is by design of Microsoft Excel 97.

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 earlier versions of Microsoft Excel, 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 earlier versions of Microsoft Excel, but it will not work in Microsoft Excel 97. 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, like this:

   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 on ParamArrays, please see the following article(s) in the Microsoft Knowledge Base:

   Article-ID: Q151582
   TITLE     : XL: Methods to Use Custom Functions with Varying Arguments


Additional query words: 8.00 97 XL97
Keywords : kbprg xlvbahowto xlvbainfo xl97vbmigrate
Version : WINDOWS:97
Platform : WINDOWS
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: February 27, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.