Insert a table as an argument

Jul 11, 2013 at 9:05 AM
Hello everybody,

I'm new on Excel DNA and I'm facing some issue to insert a table as an argument.
Here is my code:
  [ExcelFunction(Description = "ComputePrice(varible yield)")]

public static double computePrice (double coupon , int frequency, __[ExcelArgument(AllowReference = true)] double [] variableYield__ , double faceValue  , double settlementDate, double maturityDate, double tradeDate)
I want to insert a table as an argument on my Excel spreadsheet. But even if this code is "working", I have no idea how to insert for instance ten value in a table as an argument of this function when I'm on my Excel sheet.

Thank you for your help!!
Coordinator
Jul 11, 2013 at 9:16 AM
Hi,

I suppose you just want your function to receive a 2D array of doubles. In that case, you don't need the AllowReference=true attribute, and can set your argument to type double[,]. Excel will do the conversion from the range in your formula.

So you'd have
[ExcelFunction(Description = "ComputePrice(varible yield)")]
public static double computePrice (double coupon , int frequency, double [,] variableYield, double faceValue , double settlementDate, double maturityDate, double tradeDate)
and call it from Excel as
=computePrice(A1, B1, C1:E24, F1, G1, H1, I1, J1)
The range C1:E24 will be converted to a double[,] array of the right size.

You can get the number of rows and columns for the array as
int rows = variableYield.GetLength(0);
int cols = variableYield.GetLength(1);
You could also assign a name to your range and use that in the formula.

Regards,
Govert
Jul 11, 2013 at 9:31 AM
Thank you for your answer, but it is still not working. I have tried something simpler, to compute the sum a simple array:
    [ExcelFunction(Description = "test")]

    public static double test  (double[] cells)
    {
        double sum=0;
        for (int i = 0; i <= cells.Length ; i++)
        {
            sum += cells[i];
        }

        return sum;
    } 
And on Excel
=test (H2:H11)
Which is not working....

Thank you for your help!
Coordinator
Jul 11, 2013 at 9:35 AM
Edited Jul 11, 2013 at 9:41 AM
Hi,

Your double array might rather be a 2D array - double[,].

1D array of double[] should work, though. If you get #VALUE then maybe your values are not all doubles?

Ah! - the last array index is cells.Length-1, so your loop should go:
for (int i = 0; i < cells.Length ; i++)
{
    sum += cells[i];
}
-Govert
Jul 11, 2013 at 9:42 AM
Thank you again, but I can't make it work!

Now my code is
    public static double test(double[,] cells)
    {
        double sum=0;
        for (int i = 0; i <= cells.GetLength(0) ; i++)
        {
            sum += cells[i,0];
        }

        return sum;
    }
and on excel I've tried
=test(H2:I11)

=test(H2:H11)
and it's always displaying #VALUE!
Coordinator
Jul 11, 2013 at 9:47 AM
Sorry - see the edit in my reply concerning the loop bounds.

-Govert
Jul 11, 2013 at 10:03 AM
Thank you very much, it's working!