How to pass named arguments to custom (UDF) functions in Excel 2016

This post is not about passing optional arguments within VBA. This is about passing optional arguments from the spreadsheet to a custom function written in VBA. I've been surprised to find not a single mention of passing optional arguments, especially named parameters, from the spreadsheet.

My current work-around is to use the "f"unction pop-up to build a list with empty commas, for example =YesNo(B4,,,,,"value"), but I'd much rather pass named parameters if possible. I also discovered Application.MacroOptions, which makes the work-around work better.

Here's my header. The function works (body not included) but would be even easier to use if I can supply named parameters from the Excel spreadsheet formula:

Public Function YesOrNo(InputValue, _
Optional InvalidResponse As String = "xlErrValue", _
Optional AcceptTF As Boolean = True, _
Optional Accept01 As Boolean = True, _
Optional AcceptTrueFalse As Boolean = True, _
Optional YesValue = "Yes", _
Optional NoValue = "No") As String

YesNo = "Of Course" ' dummy function body

Exit Function

Is there a way to pass optional arguments by name, from the spreadsheet to the VBA custom function (aka UDF) ???


Thanks!

mehmetcik, thank you for responding, but your response is not related to my question as it includes no mention of passing parameters to custom functions. named ranges and named parameters are not the same thing.

In SQL Server, for example, a procedure call using named parameters might appear as follows:

EXECUTE YesOrNo "true", AcceptTF = false, NoValue = "negative"

or in c#:

result = YesOrNo(MyVariable, AcceptTF:false, NoValue:"negative");

If Excel supports named parameters, then a spreadsheet formula call a compatible function might appear as follows (in a cell on the spreadsheet):

=YesOrNo(B4, AcceptTf = false, NoValue = "negative")

instead of:

=YesOrNo(B4, , false, , , , "negative")

When I wrote that I was surprised to see not even a single mention of named parameters, that means that I did not find out how to do it, or even find reports of other people asking about it. Named parameters may not be a feature yet in Excel formulas, but they are common elsewhere (I omitted any mention of VBA / VB to avoid confusion with calls from one VBA function to another).

I too am looking for a way to pass named arguments from the spreadsheet using a UDF call to a vba function having multiple optional named arguments. Basically I want to calculate a property that can be derived from a variety of different inputs. In other programming languages I would create an overloaded function. In VBA I can accomplish the same basic functionality using a generic UDF call to a vba function having a large number of optional arguments. At the same time I write several variants of the function each having a different name that are setup to solve for the desired property from each of the appropriate combinations of input arguments. The generic function call contains logic to determine which of the optional arguments are present, validates that it is an appropriate combination, and then passes those arguments to the appropriate variant of the function and then returns the solution back to the cell. This all works, BUT, the long list of optional arguments on the generic call is awkward. Similarly requiring the user to remember the name of which variant of the function to call is also not desired. If I could find an elegant method of passing named arguments from a UDF I believe I would have a satisfactory solution