Variable number of arguments in ParamArray ArgList()

Question

If I want to pass a number of values for the ParamArray arglist via an array, how do I do it? From what I've read so far, on VBA, it appears that I need to explicitly list the values that I want to pass. But what if there are potentially different numbers of values to pass, so I do not know in advance how many I'll want to pass to the function? Is there not some way of using an array (a one-dimensional array) with a variable dimension?

Answer 1

There are several ways to achieve that:

• dimension the array to be larger than you require, keep a count of how many items actually get added and then Redim Preserve back to the correct size
• use a Collection object instead (as a normal parameter rather than a ParamArray)

Answer 2

You can pass a variable number of values to a function if the function includes a Param Array:

http://msdn.microsoft.com/en-us/library/538f81ec(v=VS.71).aspx

http://msdn.microsoft.com/en-us/library/aa164809(v=office.10).aspx