Best syntax for keyword parameters

[JenniferMurphy]

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:

=Test(J22,,,,"0.000",,TRUE)

I would like to replace this call with something like:

=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.

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

 

[JenniferMurphy]

Immediately after you type the opening paren ...

=JMUDF(

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

Apparently, this can only be used once and only before any arguments have been entered. If I need to change any of the parameters or add any, I either have to do it manually or delete them all and start over. Correct?

 

[shg]

That's what the "immediately" means, yes.

 

[Rick Rothstein]

Apparently, this can only be used once and only before any arguments have been entered. If I need to change any of the parameters or add any, I either have to do it manually or delete them all and start over. Correct?

That's what the "immediately" means, yes.

It appears you can click anywhere within the function (any function, UDF or built-in) name or its argument list and then click the fx (it's a button) immediately to the left of the formula bar and bring up that same dialog box without having to delete anything.

 

[JenniferMurphy]

It appears you can click anywhere within the function (any function, UDF or built-in) name or its argument list and then click the fx (it's a button) immediately to the left of the formula bar and bring up that same dialog box without having to delete anything.

OMG, Rick. How do you know all this stuff? Are you some kind of VB savant?

Now, just to push my luck, it there any chance that you know how to assign that fx button to a keyboard shortcut, say, oh, I dunno, Ctrl+a?

 

[Rick Rothstein]

OMG, Rick. How do you know all this stuff?

I don't know if there is a technical term for the type of memory I have, but I call it a "reactive" memory. I would not actually be able to recall any of the things I post here on my own, but when I read a question, it reminds me, in a fair amount of detail, of things I have seen related to it in the past. It would never occur to me to tell anyone about then fx, and I don't actually use it myself at all, but when I tried shg's CTRL+A keystroke and saw the dialog box that resulted, I immediately remembered that the fx button brought up that same dialog box.

Are you some kind of VB savant?

No, I am not a VB savant, but even if I were, that would not have any bearing on what I posted as clicking the fx button with the mouse is not a VB "thing".

Now, just to push my luck, it there any chance that you know how to assign that fx button to a keyboard shortcut, say, oh, I dunno, Ctrl+a?

No, the problem is in order to make the dialog box useful, you would have to enter edit mode for the cell and move the text cursor somewhere into the function you want to call the dialog box for... unfortunately you cannot run VB code while Excel is in edit mode. However, you can call up the dialog box from the keyboard by pressing the ALT key while pressing the I key followed by the F key. Of course, if you are clicking into some part of the function to set what the dialog box should display, I think it would be easier to simply then click the fx button rather than to move your hands to the keyboard in order to press the ALT key combination. True, you could do all this without using the mouse by pressing the F2 key to enter edit mode and then use the Left Cursor Key to position the text cursor and then follow that up with the ALT+I+F combination, but that seems like so much extra work as compared to clicking with the mouse.

 

[JenniferMurphy]

I don't know if there is a technical term for the type of memory I have, but I call it a "reactive" memory.

Well, whatever it's called, it's impressive and a little daunting. At my age, I'm lucky to be able to remember where my glasses are -- and they are usually on top of my head.

No, I am not a VB savant, but even if I were, that would not have any bearing on what I posted as clicking the fx button with the mouse is not a VB "thing".

OK, than I hereby confer on you the title of Excel Savant.

No, the problem is in order to make the dialog box useful, you would have to enter edit mode for the cell and move the text cursor somewhere into the function you want to call the dialog box for... unfortunately you cannot run VB code while Excel is in edit mode. However, you can call up the dialog box from the keyboard by pressing the ALT key while pressing the I key followed by the F key. Of course, if you are clicking into some part of the function to set what the dialog box should display, I think it would be easier to simply then click the fx button rather than to move your hands to the keyboard in order to press the ALT key combination. True, you could do all this without using the mouse by pressing the F2 key to enter edit mode and then use the Left Cursor Key to position the text cursor and then follow that up with the ALT+I+F combination, but that seems like so much extra work as compared to clicking with the mouse.

Whew! I'll just click on the fx button.