XLL CallWorksheetFunction vs ExcelApp.WorksheetFunction

Add-in Express™ Support Service
That's what is more important than anything else

XLL CallWorksheetFunction vs ExcelApp.WorksheetFunction
 
nwein




Posts: 577
 Joined: 2011-03-28
I've noticed that calling the ADX function CallWorksheetFunction, some function with a limit of 255 parameters (such as some of the Excel built-in statistical functions) won't work if passed with more than 255 parameters. Makes sense.
However, I can definitely call the the ExcelApp.WorksheetFunction with an array of more than 255 parameters and it seems to work just fine.
I prefer using your own method than the object module one but with this limitation I can't.
Is it possible for you guys to support it? (is it a bug?)
And really, why do you provide this method if it's available from the object module? Is it not safe to call if from an XLL?
Thanks

Just so you get the picture: 
public static object Myfunc(int numbersToCompute)
{
	List<int> values = new List<int>();
	for (int i = 0; i < numbersToCompute; i++)
	{
		values.Add(i);
	}
	
	double value = Module.ExcelApp.WorksheetFunction.StDevP(values.ToArray()); // works regardless of size of numbersToCompute
    object adxValue = Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Stdevp, values.ToArray()); // returns ADXXlReturnValue.xlReturnInvCount when numbersToCompute > 255
	return adxValue;
}
Posted 02 Mar, 2015 15:17:35 Top
Andrei Smolin


Add-in Express team


Posts: 19138
 Joined: 2006-05-11
Hello Nir,

This isn't a bug.

ExcelApp.WorksheetFunction.StDevP accepts only 255 parameters, please see http://www.excelfunctions.net/Excel-Stdev-Function.html. Since you don't get a problem when passing it more than 255 parameters, this means, they check the parameters and do something with the extra parameters; say, they can just ignore them.

It seems this accepts more than 255 parameters on the Excel object model only. This is because CallWorksheetFunction(ADXExcelWorksheetFunction.Stdevp invokes the Excel's native implementation of this function. This implementation doesn't ignore extra parameters; it just returns an error.

nwein writes:
And really, why do you provide this method if it's available from the object module? Is it not safe to call if from an XLL?


Calling the Excel object model from an XLL is not recommended.


Andrei Smolin
Add-in Express Team Leader
Posted 03 Mar, 2015 10:29:41 Top