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:
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.
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:
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.
Consider the following requirements from different functions:
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?
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:
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:
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.
While ParamArray allows great flexibility when structuring our program, some rules must be remembered:
The in-depth course, Beyond Excel Boundaries with Databases & SQL, is now open.
Its pre-launch price of $97 is still valid for few more days only, until the last 5 missing sessions are added and the full course is published.
This price will double in a few days, so if you are serious about your career, or about understanding Databases, SQL and how to develop Excel VBA business-grade programs with Databases - take advantage of this opportunity today