Dyanmic Array and spilling support for older version of Excel. Office 2019 to be specific

[Vishal Bajoria]

Hi All,

We have developed a bunch of UDFs that returns dynamic 2D arrays. Everything is working as expected in Office 365.

Our company has decided to downgrade Office to 2019 due to some licensing. 

While testing in office 2019 version of excel, we found out that none of the UDFs that were returning 2D arrays work properly. Only the cell that makes the call has value and non other cell has data in them. 

My question is does ExcelDNA support resizing and dynamic array response back to office 2019 or older version of Excel? If so could anyone share an example for it?

Much appreciate it. Looking forward to hearing back soon.

Thanks in advance,

Best Regards,

Vishal

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

You can add Excel-DNA support with easy billing through a corporate GitHub account.

--------------------------------------------------

Hi Vishal,

Dynamic Arrays are a really deeply integrated feature in the new Excel versions, and you'll find that older Excel can only approximate the feature poorly.

With Excel-DNA we have a partial workaround for old Excel versions, which is to automatically extend a Ctrl+Shift+Enter style array formula to a region of the correct size.

This helps in some cases, but you still lose out on lots of the Dynamic Arrays functionality, including the array-friendly functions and the # references.

I paste a current version of the resizer code below - you can put that into an Excel-DNA project.

Make sure you have a reference to the Excel COM interop library, possibly by adding the ExcelDna.Interop package.

Then try the MakeArrayAndResize function on a sheet.

-Govert

using System;

using System.Collections.Generic;

using ExcelDna.Integration;

using Microsoft.Office.Interop.Excel;

namespace ResizeError

{

    // This class defines a few test functions that can be used to explore the automatic array resizing.

    public static class ResizeTestFunctions

    {

        // Just returns an array of the given size

        public static object[,] MakeArray(int rows, int columns)

        {

            object[,] result = new object[rows, columns];

            for (int i = 0; i < rows; i++)

            {

                for (int j = 0; j < columns; j++)

                {

                    result[i, j] = i + j;

                }

            }

            return result;

        }

        // Makes an array, but automatically resizes the result

        public static object MakeArrayAndResize(int rows, int columns)

        {

            object[,] result = MakeArray(rows, columns);

            return ArrayResizer.Resize(result);

            // Can also call Resize via Excel - so if the Resize add-in is not part of this code, it should still work

            // (though calling direct is better for large arrays - it prevents extra marshaling).

            // return XlCall.Excel(XlCall.xlUDF, "Resize", result);

        }

    }

    public class ArrayResizer : XlCall

    {

        // This function will run in the UDF context.

        // Needs extra protection to allow multithreaded use.

        public static object Resize(object[,] array)

        {

            var caller = Excel(xlfCaller) as ExcelReference;

            if (caller == null)

                return array;

            int rows = array.GetLength(0);

            int columns = array.GetLength(1);

            if (rows == 0 || columns == 0)

                return array;

            if ((caller.RowLast - caller.RowFirst + 1 == rows) &&

                (caller.ColumnLast - caller.ColumnFirst + 1 == columns))

            {

                // Size is already OK - just return result

                return array;

            }

            var rowLast = caller.RowFirst + rows - 1;

            var columnLast = caller.ColumnFirst + columns - 1;

            // Check for the sheet limits

            if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||

                columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)

            {

                // Can't resize - goes beyond the end of the sheet - just return #VALUE

                // (Can't give message here, or change cells)

                return ExcelError.ExcelErrorValue;

            }

            // TODO: Add some kind of guard for ever-changing result?

            ExcelAsyncUtil.QueueAsMacro(() =>

            {

                // Create a reference of the right size

                var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId);

                DoResizeCOM(target); // Will trigger a recalc by writing formula

            });

            // Return what we have - to prevent flashing #N/A

            return array;

        }

        public static void DoResizeCOM(ExcelReference target)

        {

            string wbSheetName = null;

            Range firstCell = null;

            string formula = null;

            try

            {

                var xlApp = ExcelDnaUtil.Application as Application;

                xlApp.DisplayAlerts = false;

                wbSheetName = Excel(xlSheetNm, target) as string;

                int index = wbSheetName.LastIndexOf(']');

                var wbName = wbSheetName.Substring(1, index - 1);

                var sheetName = wbSheetName.Substring(index + 1);

                var ws = xlApp.Workbooks[wbName].Sheets[sheetName] as Worksheet;

                var targetRange = xlApp.Range[

                    ws.Cells[target.RowFirst + 1, target.ColumnFirst + 1],

                    ws.Cells[target.RowLast + 1, target.ColumnLast + 1]] as Range;

                firstCell = targetRange.Cells[1, 1];

                formula = firstCell.Formula;

                if (firstCell.HasArray)

                    firstCell.CurrentArray.ClearContents();

                else

                    firstCell.ClearContents();

                targetRange.FormulaArray = formula;

            }

            catch (Exception ex)

            {

                Excel(xlcAlert, $"Cannot resize array formula at {wbSheetName}!{firstCell?.Address} - result might overlap another array.\r\n({ex.Message})");

                firstCell.Value = "'" + formula;

            }

        }

    }

}

[Vishal Bajoria]

Thank you so much Govert,

I will surely try this out

On Oct 1, 2021, at 12:05 PM, Govert van Drimmelen <gov...@icon.co.za> wrote:



--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/WK6RfD5cym0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/011f9c24-0d47-46d9-8d8f-963fa9b06699n%40googlegroups.com.

[Anthony LaPaso]

Govert,

Regarding the line of code, " var caller = Excel(xlfCaller) as ExcelReference;" in the Resize() method: This line works fine when Resize() is called from a method decorated with ExcelFunction but when called from an ExcelAsyncFunction method, we always get an exception: Exception of type 'ExcelDna.Integration.XlCallException' was thrown.

This happens even when we wrap the line like this:

new WindowsFormsSynchronizationContext().Send((obj) => {

   try {

      caller = Excel(xlfCaller) as ExcelReference;

   } catch (Exception e){

      String r = e.Message; // produces Exception of type 'ExcelDna.Integration.XlCallException' was thrown

   }

}, null);

Is there anyway to make the Resize() method work from a method decorated with ExcelAsyncFunction ??

Thank you.

[Govert van Drimmelen]

Hi Anthony,

 

Unfortunately there are various problems when trying to mix the async functions and the array resizing for old Excel versions.

I don’t know how to make this work at all.

 

Excel-DNA uses the RTD mechanism to implement async functions.

The way RTD functions recalculate when they are called from an old-style array function is problematic, and has some bugs.

Such a function gets called many times – once for each cell and then once for the array. This confuses the attempt at understanding when to resize the caller.

There is also a memory leak bug in the Excel RTD implementation when called from array formulas.

These problems were fixed with the Dynamic Arrays version of Excel, so mixed arrays and async is fine in the newer Excel.

 

-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/65b3577b-12cc-4bf7-921e-aa270d20d2e1n%40googlegroups.com.