Calling Excel functions in C#

[shamus]

Hi,

Does anyone know how to call the Excel functions from C#?

I found this link that helped a little:
http://www.experts-exchange.com/Programming/Languages/.NET/Visual_CSharp/Q_23258035.html

However, this approach doesn't expose all of the Excel functions. In
particular, I am looking for the YEARFRAC(start_date,end_date,basis)
function.

Below is the sample C# code for calling the worksheet function
Days360:

using System;
using System.Collections.Generic;
using System.Text;
using ExcelDna.Integration;
using IExcel = Microsoft.Office.Interop.Excel;

namespace NYUAddin
{
public class Test : XllAddin
{
/// <summary>
/// Dummy function for testing
/// </summary>
[ExcelFunction(Description = "Days360"
, Category = "Marvelous functions")]
public static double aDays360()
{
IExcel.Application myxl = (IExcel.Application)
ExcelDna.Integration.Excel.Application;
IExcel.WorksheetFunction wsf = myxl.WorksheetFunction;
double result = wsf.Days360(39092, 39823, true); //
should be 720
return result;
}
}
}


thanks,
-shamus

[Govert van Drimmelen]

Hi Shamus,

ExcelDna contains a class ExcelDna.Integration.XlCall that allows you
to call the native Excel API easily. It is not advisable to call the
COM API from a user-defined function.

The list of function and command constants were updated in the Excel
2007 SDK, but I forgot to update the list in the latest ExcelDna
version. The next version will have an up-to-date list. In the mean
time you can check the example below on how to call these functions,
and how to work around the missing xlfYearfrac constant.

Regards,
Govert

<DnaLibrary Language="CS">
using ExcelDna.Integration;

public class Test
{
public static double aDays360(
double start_date,
double end_date,
bool method)
{
return (double)XlCall.Excel(
XlCall.xlfDays360,
start_date,
end_date,
method);
}

public static object aYearFrac(
double start_date,
double end_date,
double basis)
{

/* XlCall.xlfYearfrac is missing */
int xlfYearfrac = 451;

return XlCall.Excel(
xlfYearfrac,
start_date,
end_date,
basis);

/* returning 'object' type ensures
that errors are propagated correctly */
}
}
</DnaLibrary>

On Jan 8, 10:12 pm, shamus <shamus.nevi...@us.hsbc.com> wrote:
> Hi,
>
> Does anyone know how to call the Excel functions from C#?
>

> I found this link that helped a little:http://www.experts-exchange.com/Programming/Languages/.NET/Visual_CSh...

[shamus]

Hi Govert,

Thanks for your reply.
I tried the sample you provided for aYearFrac, but I get a return
value of #VALUE.
I am using Excel 2003 SP3 and ExcelDna-0.7.

Does this call work for you?
I used the following:
start_date=9/2/2008
end_date=10/7/2008
basis=0 (I also tried 1 through 4)

thanks,
-shamus

> > -shamus- Hide quoted text -
>
> - Show quoted text -

[Govert van Drimmelen]

Hi Shamus,

It seems under Excel 2003 the API does not allow the new functions
like YearFrac to be called.

You might like to try this:

public static object aYearFrac(double start_date,
double end_date,
double basis)
{
string formula = string.Format("YEARFRAC({0}, {1}, {2})",
start_date, end_date, basis);
return XlCall.Excel(XlCall.xlfEvaluate, formula);
}

Regards,
Govert

[shamus]

thanks Govert.

That worked.

> > > - Show quoted text -- Hide quoted text -