Detect if array function was executed with Enter or Ctrl+Shift+Enter

[David]

Copy pasted from here.

[ExcelFunction(Description = "Check if function was called as dynamic array or not")]
public static bool IsDynamicArray()
{
    //What goes in here?
}

I'd like to know the answer to this because the function I'm using does quite a large DB lookup, and so if it is called as a legacy array, regardless of how many items are retrieved from the DB, it will only display the number of cells selected.

Therefore, I want to know if the array function was called as a legacy array so I can save doing such a large DB lookup, and only return the number of items needed to fill the selected cells.

[Govert van Drimmelen]

A function is not ‘called as a dynamic array or not’.

How the array result will be used depends on whether the version of Excel supports the Dynamic Arrays feature.

 

If Excel _does_ support the Dynamic Arrays feature, the calling formula might also apply the Implicit intersection operator: @ (microsoft.com), and only look at one of the values.

Or the formula might be processing the array result using functions like SORT or FILTER, and then using the results further from there.

The final result might depend on the whole array, even though it only goes into a single cell.

Or (more normally) it might spill to the correct-size region of the sheet.

For more about this feature see Dynamic array formulas and spilled array behavior (microsoft.com)

 

If Excel _does not_ support the Dynamic Arrays feature, the function might be called from a single cell, or as a ‘Ctrl+Shift+Enter’ (CSE) formula from a region of the sheet.

If it is a CSE formula, the calling region might be larger, smaller or the right size for the array.

 

You can disambiguate some of these cases.

You can check whether the version of Excel supports Dynamic Arrays with code like this:

    public static class UtilityFunctions

    {

        static bool? _supportsDynamicArrays;

        [ExcelFunction(IsHidden = true)]

        public static bool dnaSupportsDynamicArrays()

        {

            if (!_supportsDynamicArrays.HasValue)

            {

                try

                {

                    var result = XlCall.Excel(614, new object[] { 1 }, new object[] { true });

                    _supportsDynamicArrays = true;

                }

                catch

                {

                    _supportsDynamicArrays = false;

                }

            }

            return _supportsDynamicArrays.Value;

        }

    }

 

 

If Excel does not support Dynamic Arrays, you can get some caller information like this:

            var caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

 

Not if caller == null, the function is not called from a worksheet.

Otherwise you can examine caller.RowFirst / RowLast / ColumnFirst / ColumnLast to determine the calling region’s size.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/6fb8e981-ccbf-4de2-b3bd-282957ff0c80n%40googlegroups.com.