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–
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.
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
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
Another place where I find named arguments especially useful is when dealing with boolean (
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.