Best syntax for keyword parameters

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,491
Office Version
  1. 365
Platform
  1. Windows
I write a lot of custom UDFs for a range of functions that I need for my work. Many of them have a number of optional parameters. As I use these UDFs, I often find that I need to add new optional parameters. If the number of optional parameters exceeds 3 or 4, the UDFs becomes a bit unwieldy and even error-prone. More than once I have gotten the parameters in the wrong order or miscounted the number of commas needed to skip over the parameters I don't need for that call.

I have decided to write a little tool to allow me to use "keyword" parameters that I can pass in any order. Suppose I have a UDF that takes these parameters:
Code:
Function Test(pValue as Double, _
     Optional pMax as Double = 100, _
     Optional pMin as Double = 10, _
     Optional pUnits as String = "Metric", _
     Optional pFormat as String = "0.00", _
     Optional pMsgBox as Boolean = FALSE, _
     Optional pBreak as Boolean = FALSE, _
     ) as String
Suppose I want to call this Test with just the Format & Break options. The call would look like this:
Code:
=Test(J22,,,,"0.000",,TRUE)
I would like to replace this call with something like:
Code:
=Test(J22,Format="0.000",Break=True)
Since Excel doesn't support anything like that, as far as I can tell, I'm going to try and come up with the best workaround that I can. I have three possible formats. I would appreciate comments and suggestions.
Code:
1 =Test(J22,"Format","0.000","Break",True)
2 =Test(J22,"Format=0.000","Break=True")
3 =Test(J22,"Format=0.000, Break=True")

My preference is #3. It's the closest to a true keyword parameter format and the easiest to read. My concern is that I might run into problems converting the string values ("0.000", "True") to numeric, boolean, etc. This would be a major problem if any of the "values" were expressions ("Max=C10/C12").

My second choice for readability is #2. It's almost as readable as #3. It will require me to use a ParamArray parameter, which is not a problem. and it has the same concern about converting strings to numbers.

All things considered, #1 seems like the most flexible format. It's not quite as readable as the others, but the parameters are passed in pairs and the values are passed in their native formats so there should not be any conversion problems. I'd need to use a ParamArray parameter defined as Variant and process the parameters in pairs.

Does anyone see any other problems with any of these approaches?

Are there any suggestions for other formats that might be better?

Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
... you can type Ctrl+A to see the order of arguments.
And, germane to counting commas, enter the arguments in the boxes provided, skipping those you don't need.
 
Last edited:
Upvote 0
Immediately after you type the opening paren ...

=JMUDF(

... you can type Ctrl+A to see the order of arguments.

Nice keyboard shortcut to clicking the "Insert Function" button at the same juncture.
 
Upvote 0
Immediately after you type the opening paren ...

=JMUDF(

... you can type Ctrl+A to see the order of arguments.

Wow! I never knew about that option. Thank you.

But I still want to write the code for the keyword parameters, so if you have any comments on that... :)
 
Upvote 0
And, germane to counting commas, enter the arguments in the boxes provided, skipping those you don't need.

I wonder what logic the geniuses at M$FT used to provide this Ctrl+A functionality for UDFs, but not provide the same automatic prompting that we get for built-in functions. They clearly have all of the information that they need to make it available. Jerks

Anyway, thanks for the tip.
 
Upvote 0
But I still want to write the code for the keyword parameters, so if you have any comments on that... :)
Just these:

o It seems less likely to me that you would remember the exact argument names but not their positions

o Your approach would unnecessarily complicate using the functions in VBA

o If you type JMUDF in the Names box, left of the formula bar, you will be transported to the function's signature and eliminate all questions
 
Upvote 0
Your approach would unnecessarily complicate using the functions in VBA
:confused: The functionality Jennifer proposes already exists in the VBA world, just not in UDFs on a worksheet. Functions and subroutines with optional arguments can call those optional argument in any order by using "named arguments"... the argument name followed by := (colon equal sign) followed by the value to assign to that optional argument. For example...
Code:
Function Test(Arg1, Optional Arg2, Optional Arg3, Optional Arg4, Optional Arg5)
  Test = Arg3 & " - " & Arg5
End Function
In a module...
Code:
Sub TestTest()
  MsgBox Test("Required", Arg3:="Argument 3", Arg5:="Argument 5")
End Sub
Unfortunately, you can only do this in VBA... it fails when the function is used as a UDF on a worksheet.
 
Last edited:
Upvote 0
Just these:

o It seems less likely to me that you would remember the exact argument names but not their positions

o Your approach would unnecessarily complicate using the functions in VBA

o If you type JMUDF in the Names box, left of the formula bar, you will be transported to the function's signature and eliminate all questions

Thanks for the tip in your third point.
 
Upvote 0
:confused: The functionality Jennifer proposes already exists in the VBA world, just not in UDFs on a worksheet. Functions and subroutines with optional arguments can call those optional argument in any order by using "named arguments"... the argument name followed by := (colon equal sign) followed by the value to assign to that optional argument. For example...
Code:
Function Test(Arg1, Optional Arg2, Optional Arg3, Optional Arg4, Optional Arg5)
  Test = Arg3 & " - " & Arg5
End Function
In a module...
Code:
Sub TestTest()
  MsgBox Test("Required", Arg3:="Argument 3", Arg5:="Argument 5")
End Sub
Unfortunately, you can only do this in VBA... it fails when the function is used as a UDF on a worksheet.
I've had similar functionality in other programming languages. This is yet another in the endless list of examples of the AAs at M$FT doing an asymmetrical and half-assed job. (sigh)

So, given that even M$FT values the ability to enter optional parameters in any order via keyword, do you have any opinions on the three possible workaround implementations I suggested in the original post?
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top