Excel: Best syntax for keyword parameters

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:

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:

I would like to replace this call with something like:

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.

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

This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of April 29, 2017.

For more resources for Microsoft Excel