Limit of the Array Resizer

[Somesh Khandelia]

Hi Govert,

I have been using the Array Resizer function given by you, and it seems for a few of my use cases, I have reached its limit.

A part of the code that I have been using:

static void DoResize(ExcelReference target)

        {

            // Get the current state for reset later

            using (new ExcelEchoOffHelper())

            using (new ExcelCalculationManualHelper())

            {

                ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);

                // Get the formula in the first cell of the target

                string formula = (string)Excel(xlfGetCell, 41, firstCell);

                bool isFormulaArray = (bool)Excel(xlfGetCell, 49, firstCell);

                if (isFormulaArray)

                {

                    // Select the sheet and firstCell - needed because we want to use SelectSpecial.

                    using (new ExcelSelectionHelper(firstCell))

                    {

                        // Extend the selection to the whole array and clear

                        Excel(xlcSelectSpecial, 6);

                        ExcelReference oldArray = (ExcelReference)Excel(xlfSelection);

                        oldArray.SetValue(ExcelEmpty.Value);

                    }

                }

                // Get the formula and convert to R1C1 mode

                bool isR1C1Mode = (bool)Excel(xlfGetWorkspace, 4);

                string formulaR1C1 = formula;

                if (!isR1C1Mode)

                {

                    object formulaR1C1Obj;

                    XlReturn formulaR1C1Return = TryExcel(xlfFormulaConvert, out formulaR1C1Obj, formula, true, false, ExcelMissing.Value, firstCell);

                    if (formulaR1C1Return != XlReturn.XlReturnSuccess || formulaR1C1Obj is ExcelError)

                    {

                        string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);

                        Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - formula might be too long when converted to R1C1 format.");

                        firstCell.SetValue("'" + formula);

                        return;

                    }

                    formulaR1C1 = (string)formulaR1C1Obj;

                }

                // Must be R1C1-style references

                object ignoredResult;

                //Debug.Print("Resizing START: " + target.RowLast);

                XlReturn formulaArrayReturn = TryExcel(xlcFormulaArray, out ignoredResult, formulaR1C1, target);

                //Debug.Print("Resizing FINISH");

                // TODO: Find some dummy macro to clear the undo stack

                if (formulaArrayReturn != XlReturn.XlReturnSuccess)

                {

                    string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);

                    Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - result might overlap another array.");

                    // Might have failed due to array in the way.

                    firstCell.SetValue("'" + formula);

                }

            }

        }

Now when I am using a big 2D table of Objects with around 60,000 rows and 3 columns, I am getting the message displayed by this part of the above code:

                        string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);

                        Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - formula might be too long when converted to R1C1 format.");

                        firstCell.SetValue("'" + formula);

Any suggestions via which I can resize bigger tables?

Thanks,

Somesh

[Govert van Drimmelen]

Hi Somesh,

 

You should figure out whether the limit is perhaps related to the formula length.

 

The message from that line gives a clue:

Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - formula might be too long when converted to R1C1 format.");

 

If your formula gets too long (more than 255 characters after conversion to R1C1 format) then Excel can’t use it as an array formula. This is an Excel limitation and you can add your voice to show them this is important to you by:

• Voting for this issue on the Excel feedback site: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10208379-remove-the-255-character-limit-in-excel-object-mod
• Adding a comment to this discussion: https://social.msdn.microsoft.com/Forums/office/en-US/54d259bd-a2ae-4118-87b0-a59035e6ef75/please-remove-remaining-255-character-api-limitations?forum=exceldev

 

So the limit might not be related to the result array size, but to the formula length.

 

-Govert

[Somesh Khandelia]

Thank you Govert.

I got the issue.

Thanks,

Somesh