3 Situations for Using Named Arguments in VBA

Mike Wolfe -

Here is the Add method for a Collection:

object.Add item, key, before, after

Here is the Add method for a Dictionary:

object.Add key, item

Anything jump out at you?  

How about the fact that the two methods have nearly identical parameters, except that the order of the first two–item and key–is reversed between the two object types.  This annoys me to no end and is a frequent source of errors for me when working with these two objects.

Named Arguments

VBA supports named argument syntax (e.g., ParamName:=Value) when calling subroutines and functions.  

It works for both required and optional arguments, and the order you call them does not need to match the order in which they appear in the procedure definition.  While you could use them every time you made a procedure call, that would only add noise to your code.  Instead, I recommend you use them in certain situations where they can greatly enhance the readability of your code:

  • Inconsistent Argument Order
  • Boolean Flags
  • Long Lists of Optional Parameters

Inconsistent Argument Order

The simplest way to improve the readability of code that references these two methods is by using the named argument syntax:

Const MyKey As String = "PA"
Const MyVal As String = "Harrisburg"

Dim MyDict As New Dictionary, MyColl As New Collection

'Order doesn't matter when using named parameters in calling code
'	the Select Case statement below will pick one out of five
'	different code paths at runtime, but all produce identical results
Select Case Int(Rnd() * 5 + 1)
Case 1	
    MyColl.Add MyVal, MyKey
    MyDict.Add MyKey, MyVal

Case 2
    MyColl.Add item:=MyVal, key:=MyKey
    MyDict.Add key:=MyKey, item:=MyVal
    
Case 3
    MyColl.Add item:=MyVal, key:=MyKey
    MyDict.Add item:=MyVal, key:=MyKey

Case 4
    MyColl.Add key:=MyKey, item:=MyVal
    MyDict.Add key:=MyKey, item:=MyVal
    
Case 5
    MyColl.Add key:=MyKey, item:=MyVal
    MyDict.Add item:=MyVal, key:=MyKey
    
End Select

Explicit Key/Value Variable Names

I find it also helps with readability to explicitly assign your key and item values to variables named something like Key and Item or Value.  When combined with named parameters, it helps make wrong code look wrong.  

For example, let's say we want a dictionary object where the keys represent US state abbreviations, and the associated values are those states' capital cities.  In such an example, this code is obviously wrong:

Dim Key As String
Key = StateAbbreviation

Dim Value As String
Value = CapitalCityName

MyDict.Add key:=Value, item:=Key   '<-- this should clearly be key:=Key

This code is also wrong, but it's now much less obvious:

MyDict.Add key:=CapitalCityName, item:=StateAbbreviation

Boolean Flags

Another place where I find named arguments especially useful is when dealing with boolean (True/False) arguments.

For example, here is a sample usage of my RegEx function:

FirstMatch = RegEx(SearchPattern, TextToSearch, True)

Even if you correctly guessed that True deals with case sensitivity, do you know which direction it goes?  The parameter name might be IsCaseSensitive or it might be IgnoreCase.  Those two things have opposite meanings.

Using a named argument, particularly with boolean parameters, makes the calling code easier to read on its own (without needing to look up the function signature):

FirstMatch = RegEx(SearchPattern, TextToSearch, IgnoreCase:=True)

Long Lists of Optional Parameters

Over time, some procedures accumulate a lot of optional parameters.

One example of this is the DoCmd.OpenForm method:

expression.OpenForm FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs

The only required parameter in the above method definition is FormName.  So, if I wanted to open a form named "MyForm" and pass it a value via the OpenArgs parameter, I have two options.

Using Positional Arguments

DoCmd.OpenForm "MyForm", , , , , MyOpenArgs

There are several problems with this approach.  It's only marginally readable because of what I named my variable (MyOpenArgs).  The bigger problem, though, is that it's way too easy to miscount the commas and pass my value to the wrong parameter.

Using Named Arguments

DoCmd.OpenForm "MyForm", OpenArgs:=MyOpenArgs

Much better.  Even without a well-named variable, it would be clear which optional parameter I intended to pass.