
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



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.Length1, so your loop should go:
for (int i = 0; i < cells.Length ; i++)
{
sum += cells[i];
}
Govert



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



Thank you very much, it's working!

