XL98: ParamArray Must Be Declared as an Array of Variant

This article was previously published under Q182649
This article has been archived. It is offered "as is" and will no longer be updated.
When you attempt to run a Visual Basic for Applications macro in MicrosoftExcel 98 Macintosh Edition, or while you are editing code in a Visual Basicmodule, you may receive the following error message:
Compile error:
ParamArray must be declared as an array of Variant
This error will occur if you have declared a variable as a ParamArray andeither of the following conditions are true:

  • The variable name is not immediately followed by an open and a close parenthesis, as follows:
          ParamArray MyVar()						

  • The open and close parentheses that follow the variable name are followed by "As <vartype>", where <vartype> is any variable type other than Variant.
In Microsoft Excel 98 Macintosh Edition, variables declared as ParamArraysmust be immediately followed by an open and a close parenthesis, and mustbe declared either as type Variant or as no type at all. Note that if adata type is not specified, the variable will default to the Variant datatype.

Making this change will allow your macros to work in all versions ofMicrosoft Excel that support the Visual Basic for Applications macrolanguage, including Microsoft Excel 98 Macintosh Edition.
This behavior is by design of Microsoft Excel 98 Macintosh Edition.
When you write a custom Visual Basic subroutine function in MicrosoftExcel, the last argument accepted by the function can be declared as aParamArray. When you do this, the function will accept one or more valuesand place them in the specified variable as an array, so that they can thenbe used within the function. For example, if you have the followingfunction:
   Function Test(X As Integer, ParamArray Y())       Test = "Hello"   End Function				
If you enter the formula
in a cell, the first argument (6) will be used as the value of the variableX. The remaining arguments (7, 8, 9, 10) will become elements in the arrayY().

When you declare a variable as a ParamArray in earlier versions ofMicrosoft Excel, you can sometimes omit the open and close parenthesesimmediately following the variable. Specifically, if the variable isdeclared within a Declare statement, the parentheses may be omitted. Forexample:
   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 98 Macintosh Edition. If youattempt to run any macros when this statement is present, you will receivethe error message shown in the "Symptoms" section of this article.

In order for the statement to work, you must add open and close parenthesesimmediately 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 asno 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 followingarticle in the Microsoft Knowledge Base:
151582 XL: Methods to Use Custom Functions with Varying Arguments

Article ID: 182649 - Last Review: 10/07/2013 21:06:15 - Revision: 1.0

  • Microsoft Excel 98 for Macintosh
  • kbnosurvey kbarchive kbdtacode kberrmsg kbprb kbprogramming KB182649