How to declare Optional and Required function parameter/arguments in ExcelDNA Function

[Rajini K]

Hi Govert,

 

How can we declare Optional and Required function parameter/arguments in ExcelDNA Function?

 

In Excel-DNA, Are we have this support?  If yes, Please share the sample code snippets.

 

 

In VB Macros Function, we are able to declare Required and Optional parameters:-

 

Function History(Year As Variant, Optional event As Variant, Optional referenceData As Variant) As Variant

  

End Function

Thanks,

Marik

[Govert van Drimmelen]

Hi Marik,

If your parameter is of type 'Object' and is not passed a value from the formula, then you get an object of type "ExcelMissing". So you can check it this way:

Function History(Year As Object, event As Object, referenceData As Object) As Object

    If TypeOf Year Is ExcelMissing Then Return ExcelError.ExcelErrorValue     ' #VALUE

    If TypeOf event Is ExcelMissing Then event = 123     ' default value

    If referenceData Is ExcelMissing Then referenceData = "xyz"     ' default value

    ' the rest of the function

End Function

I suggest you first get this working.

If you have a large number of functions, you might want to automate the handling of these parameters.

There is an extension library for Excel-DNA that automatically deals with Default and Optional parameters (inserting the checks automatically.) The NuGet package to install for this is called ExcelDna.Registration.VisualBasic. Sample code for how to use it is on GitHub: https://github.com/Excel-DNA/Registration/tree/master/Source/Samples/Registration.Sample.VisualBasic

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Rajini K [rajinit...@gmail.com]
Sent: 22 December 2015 11:32 AM
To: Excel-DNA
Subject: [ExcelDna] How to declare Optional and Required function parameter/arguments in ExcelDNA Function

--
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 post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[pune...@gmail.com]

Hi Govert, any updates on this?  I have a large library of user-defined functions as Excel automation-addin using VB.NET.  All the UDFs extensively use optional arguments with default values.  These optional arguments could be of type double, string, integer beside Object.  An example of such a function definition (a function to value credit default swaps) is below.  I'm open to some automated solution, but it would be great if this could be somehow be handled explicitly.  In my case, this has unfortunately been a show-stopper in using ExcelDNA.  Would appreciate your thoughts/comments.

Regards,

Puneet

Public Function CDSWAP(

ByVal Notional As Double,

ByVal Stdate As Double,

ByVal Endate As Double,

ByVal DealSpreadBP As Double,

ByVal PremFreq As String,

ByVal PremDaycount As String,

ByVal RecoveryRate As Double,

ByVal DfCv As Object,

ByVal FwdHazCv As Object,

Optional ByVal AccruConv As String = "I",

Optional ByVal DayRoll As String = "F",

Optional ByVal PremAdj As String = "Y",

Optional ByVal DatesFromEnd As String = "Y",

Optional ByVal LongCpn As String = "N",

Optional ByVal Cdr As String = "NY",

Optional ByVal HazDaycount As String = "ACT/365",

Optional ByVal IsProtectBuyer As String = "Y",

Optional ByVal OutType As String = "NPV",

Optional ByVal num As Integer = 1

) As Object

To post to this group, send email to exc...@googlegroups.com.

[Govert van Drimmelen]

Hi Puneet,

The ExcelDna.Registration extension library takes care of this: https://github.com/Excel-DNA/Registration

In particular, have a look at the Visual Basic sample: https://github.com/Excel-DNA/Registration/tree/master/Source/Samples/Registration.Sample.VisualBasic

-Govert