Microsoft Knowledge Base

• 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.

1. 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
   
   

2. Run the macro "RunDemo."

REFERENCES

For more information about ParamArray in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:

   ParamArray

   Paramarray