Optional and default parameters
Johannes Burtscher
I wanted to try out the extended default registration introduced in version 1.9.0. However, I'm a bit confused by the results I'm seeing.
Here's the code I'm working with:
namespace Test;
using ExcelDna.Integration;
public static class Functions
{
[ExcelFunction]
public static double DateTimeRequired(DateTime value) => value.ToOADate();
[ExcelFunction]
public static double DateTimeNullable(DateTime? value = null) => value?.ToOADate() ?? 42;
[ExcelFunction]
public static double DateTimeDefault(DateTime value = default) => value.ToOADate();
}
And here are the results/questions I have:
- =DateTimeRequired() → 0: Shouldn't this return a #VALUE! error? Or do I need to declare the value as an object and explicitly check for ExcelDna.Integration.ExcelMissing?
- =DateTimeRequired("2025-10-13") → 45943: As expected.
- =DateTimeNullable() → 42: As expected
- =DateTimeNullable("2025-10-13") → #VALUE!: Why doesn't this work?
- =DateTimeDefault() → #NAME?: Why isn't this function being registered?
I appreciate any insights!
Best,
Govert van Drimmelen
Hi Johannes,
Thanks – this is an interesting question.
In addition to your functions, suppose we also have these ones
[ExcelFunction]
public static double doubleRequired(double value) => value;
[ExcelFunction]
public static double doubleNullableWithoutDefault(double? value) => value ?? 7.89;
[ExcelFunction]
public static double doubleNullable(double? value = 12.3) => value ?? 42.0;
[ExcelFunction]
public static double doubleDefault(double value = 23.4) => value;
And this one
[ExcelFunction]
public static double DateTimeNullableWithoutDefault(DateTime? value) => value?.ToOADate() ?? 42;
Excel has no DateTime data type – there are only some display formats for the internal double representation.
So in Excel-DNA, old versions too, we register a DateTime parameter as a double, and perform a conversion before calling the .NET function.
Thus, DateTimeRequired is registered like doubleRequired, telling Excel we want a double.
Excel will call this function with 0.0 if there is no argument in the calling formula – it does not return #VALUE in this case.
There is no way to tell Excel that a UDF argument is ‘required’ and have it behave like e.g. the built-in =ABS() function.
Then on the Excel-DNA side of the UDF call, we cannot distinguish this from a formula like =doubleRequired(0.0).
So =DateTimeRequired() calling your function with 0.0 is expected and compatible with previous versions.
You are right that you can change the parameter type to ‘object’ to distinguish different cases like ExcelMissing, ExcelEmpty etc.
That’s exactly what is happening behind the scenes in the extended registration for the other examples.
=DateTimeDefault() works fine on my machine. Can you check this again? You should not get an error under v1.8 or v1.9.
To see the difference between Excel-DNA v1.8 and v1.9, compare =doubleDefault() in the two versions.
Under 1.8 you would get 0.0, while under 1.9 you get 23.4 - the default is being applied when the argument is not passed in – this is because of the extended registration wrapper.
You can’t see that difference between v1.8 and v1.9 with =DateTimeDefault() since you can only set the compile-time default to be the DateTime representation of 0.0, which is exactly what our conversion gives you when Excel passes ExcelMissing.
Next, look at =DateTimeNullableWithoutDefault() – you should get 42.
This function would not be registered under v1.8 since the nullable reference types are not supported.
Under v1.9 it works as you expect – the argument value you get in the function is null if it is missing in the formula, and you can handle this in the implementation to return some default.
Also, it works when you pass in an argument.
Similar for =doubleNullableWithoutDefault() – you get null from the extended registration wrapper, and can then check for null and return 7.89 as a fallback.
Next, look at =doubleNullable() – this works as expected giving you the default value into the function if called with a missing argument.
So you get 12.3 from the default value, and never 42 (since we’ll never get a null from Excel - we either get ExcelMissing which is converted to the default, or a valid double).
Finally, the problem case, which is DateTimeNullable, where we have both the nullable and a compile-time default value of `null`.
When called without an argument as =DateTimeNullable() everything works as expected, and the default value of null is applied for the missing argument.
But for a valid DateTime input, say =DateTimeNullable(NOW()) we get an exception:
* Unable to cast object of type 'System.Double' to type 'System.Nullable`1[System.DateTime]'.
I think what is going wrong here is that we are not chaining all the conversions together correctly.
So that looks like a bug in this edge case.
However, for DateTime? giving an explicit default is not really useful, so you should just follow the pattern from DateTimeNullableWithoutDefault instead.
Could you have a look and let me know if you agree with these observations?
Regards,
Govert
--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/exceldna/33c60fb7-b78e-48b3-855f-01ea6b3e1154n%40googlegroups.com.
Johannes Burtscher
at System.Reflection.MdConstant.GetValue(MetadataImport scope, Int32 token, RuntimeTypeHandle fieldTypeHandle, Boolean raw)
at System.Reflection.RuntimeParameterInfo.GetDefaultValueInternal(Boolean raw)
at System.Reflection.RuntimeParameterInfo.GetDefaultValue(Boolean raw)
at ExcelDna.Registration.ExcelParameterRegistration..ctor(ParameterInfo parameterInfo)
at ExcelDna.Registration.ExcelFunctionRegistration.<>c.<.ctor>b__27_1(ParameterInfo pi)
at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at ExcelDna.Registration.ExcelFunctionRegistration..ctor(MethodInfo methodInfo)
at ExcelDna.Integration.AssemblyLoader.IsMethodSupported(MethodInfo mi, Boolean explicitExports)
at ExcelDna.Integration.AssemblyLoader.GetExcelMethods(Type t, Boolean explicitExports, List`1 excelMethods, List`1 excelFunctionsExtendedRegistration)
at ExcelDna.Integration.AssemblyLoader.ProcessAssemblies(List`1 assemblies, List`1 methods, List`1 excelParameterConversions, List`1 excelReturnConversions, List`1 excelFunctionProcessors, List`1 excelFunctionsExtendedRegistration, List`1 excelFunctionExecutionHandlerSelectors, List`1 addIns, List`1 rtdServerTypes, List`1 comClassTypes)