XL98: ParamArray Must Be Declared as an Array of Variant (182649)
The information in this article applies to:
- Microsoft Excel 98 Macintosh Edition
This article was previously published under Q182649 SYMPTOMS
When you attempt to run a Visual Basic for Applications macro in Microsoft
Excel 98 Macintosh Edition, 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 error 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, as follows:
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 98 Macintosh Edition, 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 98 Macintosh Edition.
STATUS
This behavior is by design of Microsoft Excel 98 Macintosh Edition.
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 98 Macintosh Edition. 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 on ParamArrays, please see the following
article in the Microsoft Knowledge Base:
151582 XL: Methods to Use Custom Functions with Varying Arguments
Modification Type: | Major | Last Reviewed: | 9/11/2002 |
---|
Keywords: | kbdtacode kberrmsg kbprb kbProgramming KB182649 |
---|
|