Excel does not recognize C# function with DateTime arguments

Question

I want to construct Excel addin with the following functions:

[ExcelFunction()]
public static  double myAverage1(double[] inputVector)
{
    return inputVector.Sum()/inputVector.Length ;
}

[ExcelFunction()] 
public static double myAverage2(DateTime[] TS1_DateTime) 
{
    return 12.5;
}

Problem is that Excel does not recognize function myAverage2.

The problem is related to the DateTime type, I tried to change it into String type but the function is strill not recognized. Any idea how to resolve this ?

What I eventually want to acheive is to give the function two DateTime arrays and get back the intersection of these.

Matt Johnson-Pint · Accepted Answer · 2014-09-29 21:35:26Z

3

According to this thread, Excel-DNA is supposed to automatically marshal DateTime values as double using ToOADate and FromOADate methods.

I haven't tried it myself, but my guess is that it is not set up to detect arrays or lists of DateTime, but only single DateTime parameters.

Try declaring the parameter as double[], and then within the function, do something like:

DateTime dt[] = values.Select(x => DateTime.FromOADate(x)).ToArray();

Or just call FromOADate on each value individually, depending on the needs of your function.

Despite the function being declared as a double[], you should still be able to pass an array of cells containing dates, as Excel will want to pass them as OLE Automation Dates anyway.

answered Sep 29, 2014 at 21:35

Matt Johnson-Pint

244k75 gold badges468 silver badges621 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel does not recognize C# function with DateTime arguments

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related