Microsoft KB Archive/152761

= Microsoft Knowledge Base =

XL: Using Mixed Data Types with ParamArray
Last reviewed: June 19, 1997

Article ID: Q152761

The information in this article applies to:


 * Microsoft Excel for Windows 95, versions 7.0, 7.0a
 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SUMMARY
ParamArray can be used when passing arguments to a function. The ParamArray will accept an arbitrary number of arguments from the calling macro or formula and package them into an array of variants.

MORE INFORMATION
ParamArray is used only as the last argument in a list of variables representing arguments that are passed to a procedure. It is used to indicate that the final argument is an optional array of Variant elements. The ParamArray keyword allows you to provide an arbitrary number of arguments, and it may not be used with Optional, ByRef, or ByVal Keywords.

Visual Basic Code Example
Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft AnswerPoint Engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following are examples of how to retrieve data when you use ParamArray.

  In a new Microsoft Excel module sheet, enter the following code: Option Base 1 Function DemoParamArray(Arg1, Arg2, ParamArray ArrayOfArguments) ' Arg1 will store the first argument passed to this function. ' Arg2 will store the 2nd argument passed to this function. ' ArrayOfArguments is an array that holds all other arguments. MsgBox Arg1   ' Show First Argument passed to this function1. MsgBox Arg2   ' Show 2nd Argument passed to this function.

MsgBox ArrayOfArguments(1) 'Show 3rd argument passed to this function MsgBox ArrayOfArguments(2) 'Show 4th Argument passed to this function

' Note that the 5th argument passed to this function is an array.

MsgBox ArrayOfArguments(3)(1) ' Show 1st element in array. MsgBox ArrayOfArguments(3)(2) ' Show 2nd element in array. MsgBox ArrayOfArguments(3)(3) ' Show 3rd element in array.

MsgBox ArrayOfArguments(4)   ' Show 6th argument passed to this ' function. End Function

Sub RunDemo ' This sub will call DemoParamArray and will pass to it 6 arguments. ' Note that the 5th argument is an array. DemoParamArray "1st Argument", "2nd Argument", _ "ParamArray Arguments start here ", "Arg4", _ Array("Array in ParamArray", "ele2", "ele3"), "6th Arg" End Sub  Run the macro "RunDemo."