Optional Function Arguments in C#

Dec 2, 2010 at 4:49 PM

I have recently started using ExcelDNA to write UDFs in C# and it works great. I particularly like that I can supply help for the functions and even descriptions of arguments.

The one thing that is still stumping me is how to define optional arguments with default values other than zero (or false, or empty string). I am using VS2010 which of course supports optional arguments, but ExcelDNA supplies zeros anyway. For example, in the following example y gets a zero if only one arguement is supplied in Excel.

[ExcelFunction(Description = "Multiplies two numbers" , Category = FuncCategory)]
public static double FaTest(
    [
ExcelArgument(Description = "The first number")]double x,
    [
ExcelArgument(Description = "The second number")]double y = 17.0)
{
   
return x * y;
}

If I instead use the Optional and DefaultValue attributes for y I get the same result:  

[ExcelFunction(Description = "Multiplies two numbers" , Category = FuncCategory)]
public static double FaTest(
   [
ExcelArgument(Description = "The first number")]double x,
    [
ExcelArgument(Description = "The second number"), Optional, DefaultValue(17.0)]
double y)
{
   
return
x * y;
}
Is there any way to sepcify default values for functions written in C#?

 

Thanks

Peter





Coordinator
Dec 2, 2010 at 5:42 PM

Hi Peter,

There is currently no special support in Excel-DNA for optional parameters or default values. You would implement these by changing your parameter type to be 'object' and then dealing with the different options explicitly. You can do the check and replace with a default value quite easily in some helper functions. I paste some examples below.

Over time this issue is sure to be addressed, but because there is a workaround that gives your users the default-value behaviour with a little bit of extra work in the implementation it is not right at the top of the priority list.

Also, I'm not entirely sure how best the different cases should be handled, so I leave it in your hands to decide how a string or error value should be handled in your function.

I hope this works OK for you.

Govert

 

<DnaLibrary Name="Optional and defaultValue arguments" Language="C#">
<![CDATA[
    using System;
    using ExcelDna.Integration;

    public class MyFunctions
    {
        public static double TestDefault(double x, object yArg)
        {
            double y = Optional.Check(yArg, 17.0);
            
            return x + y;
        }
        
        public static string TestHello(object nameArg)
        {
            string name = Optional.Check(nameArg, " Unknown person!?");
            
            return "Hello " + name;
        }
        
        public static string TestBestDayEver(object dateArg)
        {
            DateTime date = Optional.Check(dateArg, DateTime.Now);
            
            return "My best day ever: " + date.ToString("dd MMMM yyyy");
        }
        
    
    }
    
    internal static class Optional
    {
        internal static string Check(object arg, string defaultValue)
        {
            if (arg is string)
                return (string)arg;
            else if (arg is ExcelMissing)
                return defaultValue;
            else
                return arg.ToString();  // Or whatever you want to do here....

            // Perhaps check for other types and do whatever you think is right ....
            //else if (arg is double)
            //    return "Double: " + (double)arg;
            //else if (arg is bool)
            //    return "Boolean: " + (bool)arg;
            //else if (arg is ExcelError)
            //    return "ExcelError: " + arg.ToString();
            //else if (arg is object[,])
            //    // The object array returned here may contain a mixture of types,
            //    // reflecting the different cell contents.
            //    return string.Format("Array[{0},{1}]", 
            //      ((object[,])arg).GetLength(0), ((object[,])arg).GetLength(1));
            //else if (arg is ExcelEmpty)
            //    return "<<Empty>>"; // Would have been null
            //else if (arg is ExcelReference)
            //  // Calling xlfRefText here requires IsMacroType=true for this function.
			//				return "Reference: " + 
            //                     XlCall.Excel(XlCall.xlfReftext, arg, true);
			//			else
			//				return "!? Unheard Of ?!";
        }        

        internal static double Check(object arg, double defaultValue)
        {
            if (arg is double)
                return (double)arg;
            else if (arg is ExcelMissing)
                return defaultValue;
            else
                throw new ArgumentException();  // Will return #VALUE to Excel
                
        }
        
        // This one is more tricky - we have to do the double->Date conversions ourselves
        internal static DateTime Check(object arg, DateTime defaultValue)
        {
            if (arg is double)
                return DateTime.FromOADate((double)arg);    // Here is the conversion
            else if (arg is string)
                return DateTime.Parse((string)arg);
            else if (arg is ExcelMissing)
                return defaultValue;
                
            else 
                throw new ArgumentException();  // Or defaultValue or whatever
        }
    }
]]>
</DnaLibrary>

Dec 2, 2010 at 6:46 PM

Hi Govert,

Thanks for your help. I appreciate your very quick answer to my question.

Peter

G. Peter Todd

Parilux Investment Technology LLC

510 Thornall Street, Suite 220

Edison, NJ 08837

peter.todd@pariluxtech.com

Phone: 732-584-2399

From: govert [mailto:[email removed]]
Sent: Thursday, December 02, 2010 1:42 PM
To: [email removed]
Subject: Re: Optional Function Arguments in C# [exceldna:236897]

From: govert

Hi Peter,

There is currently no special support in Excel-DNA for optional parameters or default values. You would implement these by changing your parameter type to be 'object' and then dealing with the different options explicitly. You can do the check and replace with a default value quite easily in some helper functions. I paste some examples below.

Over time this issue is sure to be addressed, but because there is a workaround that gives your users the default-value behaviour with a little bit of extra work in the implementation it is not right at the top of the priority list.

Also, I'm not entirely sure how best the different cases should be handled, so I leave it in your hands to decide how a string or error value should be handled in your function.

I hope this works OK for you.

Govert

<DnaLibrary Name="Optional and defaultValue arguments" Language="C#">
<![CDATA[
    using System;
    using ExcelDna.Integration;
 
    public class MyFunctions
    {
        public static double TestDefault(double x, object yArg)
        {
            double y = Optional.Check(yArg, 17.0);
            
            return x + y;
        }
        
        public static string TestHello(object nameArg)
        {
            string name = Optional.Check(nameArg, " Unknown person!?");
            
            return "Hello " + name;
        }
        
        public static string TestBestDayEver(object dateArg)
        {
            DateTime date = Optional.Check(dateArg, DateTime.Now);
            
            return "My best day ever: " + date.ToString("dd MMMM yyyy");
        }
        
    
    }
    
    internal static class Optional
    {
        internal static string Check(object arg, string defaultValue)
        {
            if (arg is string)
                return (string)arg;
            else if (arg is ExcelMissing)
                return defaultValue;
            else
                return arg.ToString();  // Or whatever you want to do here....
 
            // Perhaps check for other types and do whatever you think is right ....
            //else if (arg is double)
            //    return "Double: " + (double)arg;
            //else if (arg is bool)
            //    return "Boolean: " + (bool)arg;
            //else if (arg is ExcelError)
            //    return "ExcelError: " + arg.ToString();
            //else if (arg is object[,])
            //    // The object array returned here may contain a mixture of types,
            //    // reflecting the different cell contents.
            //    return string.Format("Array[{0},{1}]", 
            //      ((object[,])arg).GetLength(0), ((object[,])arg).GetLength(1));
            //else if (arg is ExcelEmpty)
            //    return "<<Empty>>"; // Would have been null
            //else if (arg is ExcelReference)
            //  // Calling xlfRefText here requires IsMacroType=true for this function.
                    //                         return "Reference: " + 
            //                     XlCall.Excel(XlCall.xlfReftext, arg, true);
                    //                  else
                    //                         return "!? Unheard Of ?!";
        }        
 
        internal static double Check(object arg, double defaultValue)
        {
            if (arg is double)
                return (double)arg;
            else if (arg is ExcelMissing)
                return defaultValue;
            else
                throw new ArgumentException();  // Will return #VALUE to Excel
                
        }
        
        // This one is more tricky - we have to do the double->Date conversions ourselves
        internal static DateTime Check(object arg, DateTime defaultValue)
        {
            if (arg is double)
                return DateTime.FromOADate((double)arg);    // Here is the conversion
            else if (arg is string)
                return DateTime.Parse((string)arg);
            else if (arg is ExcelMissing)
                return defaultValue;
                
            else 
                throw new ArgumentException();  // Or defaultValue or whatever
        }
    }
]]>
</DnaLibrary>
 

Read the full discussion online.

To add a post to this discussion, reply to this email (exceldna@discussions.codeplex.com@discussions.codeplex.com)

To start a new discussion for this project, email exceldna@discussions.codeplex.com@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com

Jun 15, 2012 at 12:03 PM
Edited Jun 15, 2012 at 12:08 PM

Hi Govert,

I've been recently playing with F# and integrating some of the libraries that I wrote into Excel, and decided to make them more user friendly by adding optional arguments and default values.

The method you described before for floats (doubles) and strings works like a charm!

[<ExcelFunctionAttribute("multiply")>]
let public MyMultiply [<ExcelArgumentAttribute("lhs")>] x:obj
                      [<ExcelArgumentAttribute("rhs")>] y:obj  =
     let x2 = if x :? ExcelMissing then 0.0 else x |> string |> float
     let y2 = if y :? ExcelMissing then 0.0 else y |> string |> float
     x2 * y2

But I have problems with using the same pattern for testing and converting optional float arrays:

let public MyLength [<ExcelArgumentAttribute("range")>] (x:obj) =
  let y = if x :? ExcelMissing then [||] else x |> float[]
  y.Length

This always gives error messages in Excel, even if I supply the function a range full of numbers.

My question is, in what format does ExcelDNA give .NET the double arrays? Because before when I specified the input x as float[] (and didn't check for optionality) it worked.

Thanks in advance for any help!

Daniel

Coordinator
Jun 15, 2012 at 12:23 PM

Hi Daniel,

It's nice to see some F# code!

For the parameter type 'object', an array will be passed as a 2D object array object[,]. You need to do the further processing to get to doubles from those objects (which might be strings or errors or empty cells.)

If you explicitly set the parameter type to a one-dimensional double[] type, Excel-DNA does some processing to give you the first row (or column, sometimes). Likewise for object[]. Excel natively supports double[,] parameters, but you'd never get that if your parameter is typed as 'object' (which you need for the whole Missing story).

Remember that your function might still be called with a single string or a double or something, so your code for handling the case where the argument is not 'missing' might need to be a bit more clever.

-Govert

Jun 15, 2012 at 2:09 PM

Hi Govert,

thanks for the super quick reply!

I don't know why I didn't realize Excel by default gives everything in a 2D array because of the range structures. I guess I didn't think about it because you guys have hidden all troublesome conversions in ExcelDNA!

let me just post the code snippet that worked for me in case some one is facing a similar issue, it still works with doubles only, but can be extended for strings and integers. It's true, this doesn't work when simple string is entered as the argument, but I'm more into implementing calculation heavy numerical methods because sometimes I simply cannot wait for Excel, and then the function simply erroring out is acceptable.

first two helper functions which retrieve a simple array from a 2D array:

let internal sliceRow (X:'a[,]) (r:int) =
   X.[r..r,0 .. ] |> fun arr -> Array.init arr.Length (fun i -> arr.[0,i])

let internal sliceCol (X:'a[,]) (c:int) =
   X.[0.., c..c ] |> fun arr -> Array.init arr.Length (fun i -> arr.[i,0])

and the function with the optional array argument, which only accepts 1D array (one row or one column) arguments

let public MyLength (x:obj) =
  let x2 =
    let temp = if box observations :? ExcelMissing then
                 (array2D [| [|0.0|]; [|0.0|] |])
               else
                 x :?> obj[,] |> Array2D.map (string >> float)
    if (x |> Array2D.length2 = 1) && (x |> Array2D.length1 > 1) then
      sliceCol x 0                                                
    elif (x |> Array2D.length1 = 1) && (x |> Array2D.length2 > 1) then

      sliceRow x 0
    else
      [||]
  x2.Length   

Thanks for the help again!

Best,

Daniel