|
Ashim Mishra
Guest
|
Hi Team,
One of our UDF accepts another UDF as the input and outputs the stats about it.
For e.g On cell A1, we have original UDF which gives actual data. i.e GetData and on cell B2 we have GetStats(A1, "Median").
In GetStats XLL UDF, we fetch the calling cell address and using the Range object tries to find the A1 cell address and its formula.
But as AddinExpress suggested in https://www.add-in-express.com/docs/net-excel-udf-tips.php#excel-object-model-xll about not using the interop calls in XLL, we have also started to getting few issues with this approach.
As an alternative I am thinking to find solution using XLL C API.
for e.g To find the address of the current cell I am using GetFormula
var callingRange = Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller) as ADXExcelRef;
object currentCellFormula = Module.CallWorksheetFunction(ADXExcelWorksheetFunction.GetFormula, callingRange);
Using this I can get the current cell formula and accordingly the cell address of GetData as well. Now I am stuck on how I can get the formula of the GetData UDF without using any introp calls.
Do you know any C api which I can use to do this ? |
|
|
Andrei Smolin
Add-in Express team
Posts: 18821
Joined: 2006-05-11
|
Hello Ashim,
If ADXExcelParameterDescriptor.AcceptAsArray = false for the first parameter of GetStats, the actual parameter supplied to GetStats is of type ADXExcelRef. Here's the AllSupportedExcelTypes method commented out in your XLL module; note how it handles this scenario:
public static string AllSupportedExcelTypes(object arg)
{
if (arg is double)
return "Double: " + (double)arg;
else if (arg is string)
return "String: " + (string)arg;
else if (arg is bool)
return "Boolean: " + (bool)arg;
else if (arg is AddinExpress.MSO.ADXExcelError)
return "ExcelError: " + arg.ToString();
else if (arg is object[,])
return string.Format("Array[{0},{1}]", ((object[,])arg).GetLength(0), ((object[,])arg).GetLength(1));
else if (arg is System.Reflection.Missing)
return "Missing" + " " + DateTime.Now.ToLongTimeString();
else if (arg == null)
return "Empty";
else if (arg is AddinExpress.MSO.ADXExcelRef)
{
AddinExpress.MSO.ADXExcelRef reference = arg as AddinExpress.MSO.ADXExcelRef;
return string.Format("Reference [{0},{1},{2},{3}]", reference.ColumnFirst, reference.RowFirst, reference.ColumnLast, reference.RowLast);
}
else if (arg is short)
return "Short: " + (short)arg;
else
return "Unknown Type";
}
Andrei Smolin
Add-in Express Team Leader |
|
|
Ashim Mishra
Guest
|
Thank you Andrei, for the quick response.
Issue with keeping the parameter of any UDF as object type is that it recalculates UDF on every edit on the worksheet.
Generally for any heavy sheet containing 200 - 300 UDF, this will be a huge performance bottleneck. Since it will trigger every UDF on each edit operation.
Can you suggest some other way to parse ADXExcelRef in the UDF parameter ? |
|
|
Andrei Smolin
Add-in Express team
Posts: 18821
Joined: 2006-05-11
|
|
|
Ashim Mishra
Guest
|
|
However, there are reports that this can confuse Excel?Â?Ð?és order-of-recalculation logic, so the advice would be to decide at the outset whether your functions need to be volatile or not, and stick with that.
Do you still see this kind of issues or any recent communication regarding the same ? |
|
|
Andrei Smolin
Add-in Express team
Posts: 18821
Joined: 2006-05-11
|
I've never seen such issues.
Andrei Smolin
Add-in Express Team Leader |
|
Forums
Topics
Search
Help
Login
Register