Does VBA support keyword parameters?

[JenniferMurphy]

Does VBA support any kind of "keyword" parameter? By this, I mean parameters of the form keyword=value. Something like this:

=MyFun(A1, B2, Keyword4=C3, Keyword2=D4, ...)

I am working on a UDF that has half a dozen optional parameters with more to come. Most of them are omitted on most calls, taking the default value. As currently coded, this means that I have to remember the order, which is error prone and tedious. It also means that if I add a parameter, it has to go at the end, even if it will be the most frequently used parameter, or else I have to find and edit all previous calls.

If I could code them with keywords, I could enter them in any order and they would be self-documenting. I could also add parameters without having to change any existing calls.

I know I can do this with a Paramarray parameter, but then I have to parse the string. I was hoping that there might be an easier way.

 

[shg]

myNickname = Left(String:="Jennifer", Length:=4)

 

[mikerickson]

You can pass arguments to a UDF by named Parameters.
Here is a simple example

Function MyFunction(First As String, Optional Second As String = "2nd", Optional Third As String = "3rd")
    MyFunction = First & "," & Second & ", " & Third
End Function

Sub test()
    MsgBox MyFunction("cat", "dog", "fish")
    MsgBox MyFunction("cat", , "fish")
    MsgBox MyFunction(First:="cat", Third:="fish")
End Sub

 

[JenniferMurphy]

Thanks, guys.

Names parameters looks like exactly what I need, but for some reason I can't get it to work.

Here's my test function:

Function NamedParmTest(P1, P2, Optional P3 As String = "?", _
                                  Optional P4 = "?", Optional P5 As String = "?")
NamedParmTest = "P1=" & P1 & ", P2=" & P2 & ", P3=" & P3 & ", P4=" & P4 & ", P5=" & P5
End Function

It works fine if I pass the parameters by position, but fails if I try passing a parameter by name.

[TABLE="class: grid, width: 601"]
<tbody>[TR]
[TD]R/C
[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Function Call[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]P1=A, P2=b, P3=?, P4=?, P5=?[/TD]
[TD]C4: =NamedParmTest("A", "b")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]P1=A, P2=b, P3=C, P4=?, P5=?[/TD]
[TD]C5: =NamedParmTest("A", "b", "C")[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]P1=A, P2=b, P3=C, P4=d, P5=E[/TD]
[TD]C6: =NamedParmTest("A", "b", "C", "d", "E")[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]Error
[/TD]
[TD]C7: =NamedParmTest("A", "b", P4:="d")[/TD]
[/TR]
</tbody>[/TABLE]

The function call in C7 as depicted by the expression in D7, gets an error.

What am I doing wrong?

 

[Rick Rothstein]

Named parameters only works within the VB world... because of syntax differences, it will not work inside of the Excel world.

 

[JenniferMurphy]

****! It would have been such a great solution. But thanks for helping me worry about whether I was going insane.