Using Named Arguments in VBA

In Passing complex arguments with default values in VBA Bruce Mcpherson isn't sure if you can use named arguments in custom functions.

And I quote:

Some built in functions allow you to use named arguments, and avoid this kind of thing where you have to count commas in examples like this
foo (a,b,,,,,,,,,,,d)

by using named arguments like this
foo (arg1:=a, arg2:=b,lastarg:=d)

This is all good stuff, but I'm not aware how you can set that up for your own functions. Maybe you can but I don't know how.

I'm here to confirm that you can do this. It works equally for functions and subs. You simply use the parameter variable when calling the function.

Example:

Function GetValue(s As String, Optional a As String, _
    Optional p As String, Optional d As String) As String
  GetValue = s & a & p & d
End Function

Here's a nonsense function that takes one required parameter and three optional parameters. I can cherry pick the arguments I want by naming them:

Sub TestValue()
  Debug.Print GetValue(s:="abc", p:="def")
End Sub

Using named arguments also lets us specify parameters out of order. The code from my Add Data Validation article can be called like this:

Sub TestAdd()
  AddValidation validationType:=xlValidateList, _
    targetRange:=Range("A1:A10"), AlertStyle:=xlValidAlertStop
End Sub