How to call an excel function?

[J]

Hi,

I am writing an exceldna function in C# which would call an excel
function at some point. How to do it?

Thanks

J

[Govert van Drimmelen]

Hi J,

You'd use the XlCall helper class, with the xlfUDF option:

object myResult = XlCall.Excel(XlCall.xlfUDF, "AVERAGE", 12, 18,
14.3);


-Govert

[Govert van Drimmelen]

Sorry - I see now that this won't work for the built-in functions.

For the built-in functions you need to use the right XlCall.xlfXXX
function, e.g.
return (double)XlCall.Excel(XlCall.xlfAverage, 18, 12);

The XlCall.xlUDF (not xlfUDF as I wrote before) option is to call user-
defined functions, like those registered by Excel-DNA or VBA.

-Govert

[gpe....@gmail.com]

I'd like to handle both user defined and Excel functions. Is there a way without using "try and catch the exception" to achieve that?

You wrote in another post that we cannot list the Excel functions so my only idea for now would be to check your UDF list (somewhere in Integration) and if not found then assume its an Excel function.

PS: Thanks for your awesome work Govert!

[Naju Mancheril]

Just curious... how can you handle the Excel functions without knowing which enum to pass to XlCall.Excel?

It seems like you need to check for each known Excel function and pass in it's appropriate XlCall value. For example:

switch(name.ToLowerInvariant()) {
case "average":

  return ...XlCall.Excel(XlCall.xlfAverage, ...);

default:

  return XlCall.Excel(XlCall.xlfUDF, name, ...);
}

Isn't that the case?

Thanks,
Naju

[gpe....@gmail.com]

Yes you are right. 

There seems to be no way to get the list of these functions and their descriptions anyway. But my application needs this list (with localized name and descriptions...) so it will have to be hard-coded in some way. Adding the mapping to its XlCall.xlfXXX constant will just be a step more in this dirty process :/

[Govert van Drimmelen]

Can't you just call xlfEvaluate or something?

http://msdn.microsoft.com/en-us/library/office/bb687913.aspx

-Govert

[gpe....@gmail.com]

Oh that looks like a helpful method. But it does not solve all problems.
So I can check the list of UDFs and execute it if that's one of them,
else xlfEvaluate it to be sure that Excel won't explode.

Thanks for the input