The Coalesce Function / The ParamArray Keyword for VBA Procedure Arguments

by Philipp Stiefel, originally published February 28th, 2019, last updated March 1st, 2019

Picture by Manuchi. Used here under CC0 licensing

The Coalesce Function in ANSI-SQL92

Recently I read a forum discussion where the Coalesce function was mentioned, and someone remarked that it would be very convenient to have something like the Coalesce function in Microsoft Access and VBA.

Coalesce is a function defined in the ANSI-SQL92-Specification and it is available in major DBMS products like Microsoft SQL-Server, Oracle and MySQL. Unfortunately, it is not included in neither Access/Jet-SQL, which only supports SQL89, nor in VBA.

Coalesce is defined as:

The Transact-SQL-Documentation on Coalesce says:
Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL.

So, that is similar to the NZ-Function in Access. The difference lies in the [ ,...n ] part of the specification. It means, you can pass any number of arguments to that function, not just two.

Examples:

In all the above cases, Coalesce will return the first Non-NULL value from the columns in the argument list. Of course, you can achieve the same result with several nested calls to the NZ-Function in Access/VBA. It works, but it is pretty unwieldy to write and becomes hard to read quickly.

Probably you’ve got the obvious question on your mind already. Is it possible to write a replacement function in VBA?

The ParamArray Keyword for VBA Procedure Arguments

The difficult part in implementing a similar function in VBA is the variable number of arguments.

You might think about creating such a function using a lot of spare optional arguments. That would work to a certain extent. But regardless how many optional parameters you define, there will be a situation where they are not enough. And, almost worse in my opinion, the code of that function would be disgusting to look at.

Another idea for such a function might be to use an array argument. That is the right direction and it would make the code of the actual function simple and effective. – Unfortunately, it is not possible to call functions with array arguments from queries in Access. This will either result in just “#Error” as output or in the error message “Wrong number of arguments used with function in query expression.” – So, this would work in VBA, but not when used from a query, which is the key purpose of the Coalesce function.

There is actually a better and much simpler solution available in VBA. However, it is so rarely used, that I guess most VBA developers have hardly heard about it. That is a very good reason to write this text and make more people aware of its existence. The ParamArray keyword for procedure arguments.

This allows us to implement an indefinite number of arguments to a function in a clean and easy to implement way. You would just apply the ParamArray keyword to a procedure array argument and any number of individual arguments you specify in the procedure call will automatically get wrapped into that array inside the procedure.

The Coalesce function described above is an ideal example to show how this works. In its very essence it depends on the core ParamArray functionality and does hardly need any distracting code around it.

Here is my implementation of it right away.

The arguments() array contains any number of arguments passed to that function. I can just iterate through that array and assign the first non-NULL value to the return value of the function.

Here are some examples how this function can be called.

With all these function calls, the function will return the Variant (subtype: String) “ABC” because that is the first non-Null value passed to the function. I added the other lines to show that you can use any number of arguments to the function and that those can be of any type.

And, of course, once you created this custom VBA function in your Access database file, all the SQL Statement using the Coalesce function, shown in the first part of this article, will work as well.

Video on ParamArray and Coalesce

Update 2019-03-06: I just published a new video on YouTube explaining these topics and showing the code in action.

Share this article: