Does VBA support keyword parameters?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,185
Office Version
365, 2016, 2007
Platform
Windows
Does VBA support any kind of "keyword" parameter? By this, I mean parameters of the form keyword=value. Something like this:

Code:
=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

MrExcel MVP
Joined
May 7, 2008
Messages
21,722
Office Version
2010
Platform
Windows
Code:
myNickname = Left(String:="Jennifer", Length:=4)
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,884
You can pass arguments to a UDF by named Parameters.
Here is a simple example

Code:
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

Well-known Member
Joined
Jul 23, 2011
Messages
1,185
Office Version
365, 2016, 2007
Platform
Windows
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:

Code:
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
CD
3Function CallFormula
4P1=A, P2=b, P3=?, P4=?, P5=?C4: =NamedParmTest("A", "b")
5P1=A, P2=b, P3=C, P4=?, P5=?C5: =NamedParmTest("A", "b", "C")
6P1=A, P2=b, P3=C, P4=d, P5=EC6: =NamedParmTest("A", "b", "C", "d", "E")
7Error
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,571
Office Version
2010
Platform
Windows
Named parameters only works within the VB world... because of syntax differences, it will not work inside of the Excel world.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,185
Office Version
365, 2016, 2007
Platform
Windows
****! It would have been such a great solution. But thanks for helping me worry about whether I was going insane.
 

Forum statistics

Threads
1,085,581
Messages
5,384,574
Members
401,909
Latest member
dlo1503

Some videos you may like

This Week's Hot Topics

Top