Three Reasons to Use Named Arguments in VBA

There are three main reasons I used named arguments in my VBA code:

  1. Calling procedures with long lists of optional parameters
  2. To improve code readability with certain boolean parameters
  3. When adding items to Collection or Dictionary objects

Reason 1: Long Lists of Optional Parameters

I tend not to include long lists of optional parameters in my own code.

Sometimes you have to call methods in external libraries, though, that do take that approach.  In fact, some of the biggest offenders are Office application libraries, such as the Excel and Word object models.  For example, here's the syntax for the Find.Execute method in Word:

expression.Execute (FindText, MatchCase, MatchWholeWord, MatchWildcards, MatchSoundsLike, MatchAllWordForms, Forward, Wrap, Format, ReplaceWith, Replace, MatchKashida, MatchDiacritics, MatchAlefHamza, MatchControl)

And, yes, all fifteen (!) of those arguments are Optional Variants.  

So, let's say you were writing a copy-editing application for an American news organization.  With a few lines of code, you could help make obsolete the jobs of half a dozen editors on the British correspondents' desk.

Find.Execute "colour", , , , , , , , , "color"
Find.Execute "flavour", , , , , , , , , "flavor"
Find.Execute "armour", , , , , , , , , , "armor"
Find.Execute "elevatour", , , , , , , , , "elevator"
Find.Execute "Terminatour", , , , , , , , , "Terminator"
Find.Execute "Terminatour 2", , , , , , , , , "Terminator 2"
Ah, those silly Brits...

Can you spot the bug in the above code?

The value "armor" in the third line is being passed as the MatchKashida argument instead of the Replace argument.  Relying on positional arguments with that many optional parameters is a recipe for disaster.  Also, it's impossible to look at a glance and know which parameter each value corresponds to.

Contrast the above code with this version, which takes advantage of named arguments:

Find.Execute "colour", Replace:="color"
Find.Execute "flavour", Replace:="flavor"
Find.Execute "armour", Replace:="armor"
Find.Execute "elevatour", Replace:="elevator"
Find.Execute "Terminatour", Replace:="Terminator"
Find.Execute "Terminatour 2", Replace:="Terminator 2"

Reason 2: Improved Readability of Method Flags

A method need not have a large number of optional parameters to benefit from the improved readability of named arguments.  This is especially true of Boolean arguments, that are often used as "flags" to modify how the method behaves.

Consider the CopyFile method of the FileSystemObject:

fso.CopyFile SourcePath, TargetPath, True

Even if you've never seen the FileSystemObject's CopyFile method before, you can infer just by looking at the above code that the file currently saved at SourcePath will be copied to TargetPath.

Additionally, from the True value included at the end of the call, we can infer...well, several different possibilities:

  • Any missing subfolders in TargetPath will be created if needed
  • If a file exists at TargetPath it will be overwritten
  • If a file exists at TargetPath it will be preserved
  • The file will be copied even if the source file is marked as read-only
  • The file will be copied even if the target folder is marked as read-only
  • The code will execute synchronously (i.e., the next line will not execute until the file copy operation is complete)
  • The code will execute asynchronously (i.e., the next line will execute immediately even if the file copy operation is ongoing)
  • The file security settings on the new file will match the source file
  • The file security settings on the new file will match the target folder's settings

In other words, the True value–absent any other context–at best, tells us nothing, and at worst, misleads us.  

However, by including the named argument in the function call, the meaning is abundantly clear:

fso.CopyFile SourcePath, TargetPath, Overwrite:=True

Reason 3: Collections vs. Dictionaries

Here's the Add method for a Collection:

object.Add item, key, before, after

Here's the Add method for a Dictionary:

object.Add key, item

Notice any differences?

That's right, the Key and Item positions are swapped between collections and dictionaries.  

That little nuance has bitten me more times than I like to admit, but it has not happened to me in many years now.  Why?  Because I always use named arguments with the Add method for both Collections and Dictionaries.

Private MyColl As New Collection
Private MyDict As New Scripting.Dictionary

Sub AppendEmployee(EmpID As Long, DoB As Date)
    MyColl.Add Item:=DoB, Key:=EmpID
    MyDict.Add Key:=EmpID, Item:=DoB
End Sub

Note that item is a required parameter in both Dictionaries and Collections.  So, this technique is not limited to use only with optional parameters.  Another side benefit to named arguments is that you can call your arguments in whatever order you would like, regardless of their position in the method signature.

In fact, the code below is functionally equivalent to the code above and (I would argue) even easier to read:

Private MyColl As New Collection
Private MyDict As New Scripting.Dictionary

Sub AppendEmployee(EmpID As Long, DoB As Date)
    MyColl.Add Key:=EmpID, Item:=DoB
    MyDict.Add Key:=EmpID, Item:=DoB
End Sub

Reader Use Cases

What are some situations where you find that named arguments improve the readability of your code in some meaningful way?  Do you have additional use cases?  Let me (and the rest of the community) know in the comments below.

Acknowledgements
  • Cover image created with Microsoft Designer