Excel VBA ParamArray: A Function That Handles Unknown Number of Arguments

What if you need to accommodate an unknown number of arguments to be handled by a single function? As with most functional languages, VBA supports this using a ParamArray type * Here’s all you need to know about ParamArray

Passing Arguments to a Function

As you probably know, a function (or subroutine) can accept arguments (or parameters) from the calling function (or sub). These arguments are part of the definition (or “stub”) of the function. For example:

Function AddTwoNumbers(_
ByVal FirstNumber as Single, _
ByVal SecondNumber as Single) as Single

The above function expects to receive two Single type arguments: FirstNumber and SecondNumber. These arguments will be passed by their value (as opposed to by referencing them) and the function will return a Single type value.

Calling the AddTwoNumbers function without providing exactly two arguments – will raise an error.

When an Argument is Optional

What if we want to enhance our little function to apply one of several arithmetic operations, not just adding our two arguments. We may accommodate a third argument to specify the required operation: add, subtract, multiply or divide. I’ll have a string of a single character representing the required operation.

However, we may want to allow all calling functions requiring an addition operation to omit the third argument and only pass two arguments. The function definition can easily accommodate this, by defining the third argument as optional:

Function TwoNumbersMath(_
ByVal FirstNumber as Single, _
ByVal SecondNumber as Single, _
Optional Operation as String = “+”) as Single

Not only does the third argument is optional, the third argument will be set to a default value, “+” in our example, when only two arguments are provided by the calling function.

Multiple optional arguments are supported, but they must be the last arguments in the function definition.

We still need more flexibility

Consider the following requirements from different functions:

• A function that fills out a row of cells with a list of values it receives as arguments
• A function that adds a new row to a table and fills its cells with values it receives
• A function that receives a list of column name + value pairs and updates a Database table with the passed values in their respective columns

All of these functions are generic and not designed to handle a specific table or range. The passed list of arguments can hold any number of arguments. One table may have 2 columns, another table has 15 columns.

Can we define 15 optional arguments? We probably can, however it would be inefficient and difficult to process. How will we loop through all 15 arguments to see which are used? And what if there are 40 arguments?

ParamArray to the rescue

Why not exploit the wonders of arrays for passing long and unknown list of arguments when calling functions?

Arrays are dynamic, they can accommodate any number of elements as they are being assigned values, and they are easily looped through for fast processing.

Passing an array of values as a parameter to a function is indicated by the ParamArray type, as in the following example:

Sub UpdateWorksheetRowWithFields(_
ByVal strSheetName As String, _
ByVal lngRow As Long, _
ByVal intFirstColumn As Integer, _
ParamArray values() as Variant)

This subroutine is defined to serve our first example: fill out a row of cells with a list of values.

After passing three fixed arguments: The Worksheet name, the Worksheet row index and the Worksheet column index, any number of values can be added by the calling function. These values will be assigned as members of the values() array. Here’s an example of calling this function:

Call UpdateWorksheetRowWithFields(“Data”, 2, 1, “Dan”, 24, True, “N”, 5412, “”)

Following a successful execution of the above call, the range A2: F2 in the Data Worksheet will have the six values sitting nicely in its six cells.

The UpdateWorksheetRowWithFields subroutine, by the way, is included in the 105 Excel VBA Functions pack you can download for free on my website, and a detailed explanation of every line in those 105 functions is given in my Udemy course here.

Within the UpdateWorksheetRowWithFields subroutine, we access all passed values as we usually access members of any 0-based array. values(0) stores “Dan”, values(1) stores 24, etc.

The values array, therefore, looks like this:

What if I have my values already arranged within an array?

The above list of values (“Dan”, 24, True, “N”, 5412, “”) may already be arranged within an array, before the subroutine is called. Let’s say, they’re stored in an array variable called arrCustomer().

Intuitively, we feel it’s a great fit – just pass an array to a function that expects a ParamArray – what a match!

The calling statement will therefore look like this:

Call UpdateWorksheetRowWithFields(“Data”, 2, 1, arrCustomer)

What happens when the arrCustomer argument is mapped to the values() ParamArray in the called subroutine?

The trick with ParamArray is that the passed values are split into the array by the separating commas. In the first call, the separating commas in the passed list of arguments: “Dan”, 24, True, “N”, 5412, “” – break the values into the array nicely.

However, arrCustomer is a SINGLE variable, not a comma-separated list of values.

Can ParamArray argument receive a SINGLE value? Sure it can, it will result in an array containing a single member. In our example, values() will have a single member, which is itself an array. The following picture shows what happens.

Now, we only have access to the one and only values() array member, like so: values(0). Attempting to access the second member: values(1) will raise a subscript out of range error, as there is not second member in this array.

Since values(0) holds an array, we can further reference its members like that:

Values(0)(0) – here “Dan” is stored.

Values(0)(1) – here 24 is stored.

And direct assignment of the whole array into a range will be:

myRange.Value = values(0)

Or in a more generic way, as implemented in the above Sub:

ThisWorkbook.Worksheets(strSheetName).Range("A1").Offset(lngRow
- 1, intFirstColumn - 1).Resize(1, UBound(values(0)) -
LBound(values(0)) + 1).Value = values(0)

And for the case in which a list of values were passed, and not a single array, we just omit the (0) from the assigned values(0).

By the way, had arrCustomers() was a result of a range assignment, meaning, it was populated from a range of cells, it would be a 2-dimentional array, and accessing its members would therefore look like: values(0)(1, 1). However, I make sure to always pass (and handle) 1-dimentional arrays, or just the list of values separated by comma, to a ParamArray argument.

ParamArray – Final Notes

While ParamArray allows great flexibility when structuring our program, some rules must be remembered:

• ParamArray must be the last argument defined in a function
• ParamArray must be declared as variant
• ParamArray is always passed by reference (implicitly – avoid explicit ByRef qualifier)
• ParamArray is always 0-based (even with Option Base 1)
• ParamArray cannot be optional – must receive at least one value