Optional arguments to VB.NET don't use default values

[Malene]

Hi,

I'm using ExcelDna 0.11 in conjunction with a DLL written in VB.NET.

In the VB.NET DLL, some of my functions accept optional input
arguments, e.g.

Public Function GetData(ByVal symbol As String, _
Optional ByVal asOfDateOpt As Date = Nothing, _
Optional ByVal dataSourceOpt As String = "Default", _
Optional ByVal atmLevelOpt As Double = 100)

' some code here....

End Function

VB.NET requires that default values must be defined for optional
arguments. As you can see from the code snippet above, the default
value for the three optional arguments are Nothing, "Default", and
100, respectively.

However, when I call this function from an Excel formula and don't
specify the optional arguments, the default values are not used... it
seems like something else is setting default values and overriding the
defaults specified by me.

For instance, I launch Excel from the Visual Studio .NET debugger, and
run the formula:

=GetData("VOD.L")

When the program control enters the GetData() function, I see that
asOfDateOpt is set to "#12/30/1899#" instead of Nothing. dataSourceOpt
is an empty string "" instead of "Default". atmLevelOpt is 0.0 instead
of 100.

I'm guessing that ExcelDna.xll is setting these default values
somewhere between when the program control leaves the Excel formula,
and when it enters my VB.NET code.

Is there any workaround for this? Any ideas would be greatly
appreciated!

Thanks,
Malene

[Govert]

Hi Malene,

I would have to handle Optional arguments explicitly to make your case
work. AFAIK an attribute is emitted by the VB compiler that denotes
the argument as optional, and that could be read be ExcelDna, so it
should be possible, but it is not in place at the moment.

For now you will have to handle these arguments explicitly in your
functions - you can type them as Object, and then you will be able to
see whether they were assigned a value from Excel or not (if not you
get a System.Reflection.Missing - see the Describe function in
MoreSamples.dna).

So your function can look perfect to the user, as if there are default
values used when they do not give some parameters explicit values. But
ExcelDna is not (yet) able to do all the plumbing automatically from
your VB assembly.

Regards,
Govert

[rleth...@gmail.com]

For what it's worth I've found that if you miss arguments out when you
call a function you get the default value of that type i.e. null for a
string, 0 for a double etc... You can use this to interpret certain
arguments as the default values i.e. if you have a bool argument then
you know that if the user ignores this then you will be passed false
so make that it's default behaviour.

In your example test your Date argument against DateTime.MinValue and
if it matches you know that no date was entered. Similarly with the
other arguments. It might happen that you find that the default value
for the type is part of the valid range of values and does not signify
that the value is omitted. That's more awkward of course, and the only
solution then might be a redesign!