Article ID: 182649 - View products that this article applies to.
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 Microsoft Excel 98 Macintosh Edition, or while you are editing code in a Visual Basic module, you may receive the following error message:
ParamArray must be declared as an array of Variant
This error will occur if you have declared a variable as a ParamArray and either of the following conditions are true:
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.
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:
If 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().
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:
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:
Also, note that ParamArrays must be declared either as type Variant or as no type at all:
For additional information on ParamArrays, please see the following article in the Microsoft Knowledge Base:
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
(https://support.microsoft.com/kb/151582/EN-US/ )XL: Methods to Use Custom Functions with Varying Arguments
Article ID: 182649 - Last Review: October 7, 2013 - Revision: 1.0