XLL UDF - Optional parameters with default values

Add-in Express™ Support Service
That's what is more important than anything else

XLL UDF - Optional parameters with default values
How to implement Excel user-defined function in XLL with some parameters having default values specified in code 
grantwohl




Posts: 3
 Joined: 2019-12-11
How can I specify the default values for parameters in an XLL UDF? I've tried using the Optional keyword in my VB.NET code, but my specified default values are not used.

Here is a simple test case: 


        Public Shared Function TestOptionalParameters_XLL(Optional a As Object = Nothing, Optional b As Double = 5) As String
            Return "a is: " & If(a?.ToString, "Nothing") & "; b is: " & b
        End Function


If I call this in Excel with no parameters, I get "a is: System.Reflection.Missing; b is: 0" -- I would expect "a is: Nothing; b is: 5".

I know I could use an Automation Add-In instead, but for various reasons (e.g. the ability to include parameter descriptions), I prefer to use an XLL if possible.

What would you recommend for getting the desired behavior with an XLL UDF? Do I have to declare all my parameters as Object and test if the type is System.Reflection.Missing, or is there an easier way?
Posted 11 Dec, 2019 11:31:02 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Hello grantwohl,

Optional parameters are part of the programming language used. For the end user, all parameters of all Excel functions (including built-in ones)are optional. To enforce providing a value required for your function, you handle the case when the corresponding argument is missing. This is demonstrated in the code of the sample function AllSupportedExcelTypes; the function is commented out in the code of the XLL module. It shows a parameter declared as non-optional; in the code you see the function handling the case (VB) "TypeOf arg is System.Reflection.Missing" (C#) "arg is System.Reflection.Missing". This is your way to having an optional parameter: the parameter is of the type Object and you check if it is missing. Then you emulate the default values: if the first parameter is System.Reflection.Missing, you supply your logic with Nothing; if the second parameter is missing, pass 5 to your logic.


Andrei Smolin
Add-in Express Team Leader
Posted 12 Dec, 2019 03:59:58 Top
grantwohl




Posts: 3
 Joined: 2019-12-11
OK, that will work. Thanks!
Posted 12 Dec, 2019 08:21:55 Top
Andrei Smolin


Add-in Express team


Posts: 18821
 Joined: 2006-05-11
Welcome!


Andrei Smolin
Add-in Express Team Leader
Posted 12 Dec, 2019 08:23:39 Top