Nested cell formula using XLL

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

Nested cell formula using XLL
Find cell dependents using XLL C APIs. 
Ashim Mishra




Posts: 51
Joined: 2016-03-01
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 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 ?
Posted 26 Jul, 2021 05:46:29 Top
Andrei Smolin


Add-in Express team


Posts: 18007
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";
            }


Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 26 Jul, 2021 07:27:02 Top
Ashim Mishra




Posts: 51
Joined: 2016-03-01
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 ?
Posted 27 Jul, 2021 07:37:10 Top
Andrei Smolin


Add-in Express team


Posts: 18007
Joined: 2006-05-11
Hello Ashim,

Check Excel XLL AcceptAsArray = False causes the function to be volatile.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 27 Jul, 2021 08:13:03 Top
Ashim Mishra




Posts: 51
Joined: 2016-03-01
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 ?
Posted 27 Jul, 2021 08:16:19 Top
Andrei Smolin


Add-in Express team


Posts: 18007
Joined: 2006-05-11
I've never seen such issues.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 27 Jul, 2021 08:18:25 Top