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.

R/C	C	D
3	Function Call	Formula
4	P1=A, P2=b, P3=?, P4=?, P5=?	C4: =NamedParmTest("A", "b")
5	P1=A, P2=b, P3=C, P4=?, P5=?	C5: =NamedParmTest("A", "b", "C")
6	P1=A, P2=b, P3=C, P4=d, P5=E	C6: =NamedParmTest("A", "b", "C", "d", "E")
7	Error	C7: =NamedParmTest("A", "b", P4:="d")

<tbody>
</tbody>

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.