Passing optional arguments with named parameters from the spreadsheeet to a custom function (UDF)

[Cornan]

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

Here's my header. The function works (body not included) but won't be of much use if I have to put in a lot of commas to get to the optional arguments:

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!

 

[Norie]

As far as I know functions on worksheets, whether native or UDF, don't work with named arguments, optional or otherwise.

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this can help
Formulas > Insert Function
in Select a Category pick User Defined and select your function

M.

 

[Eric W]

You could pass the entire parameter list as a string, then write a parser in your routine to handle it.

=YesOrNo("AcceptTF:=TRUE, YesValue:=Y")

Probably not what you're looking for, but I'm not sure there's a better option.