Strange behaviour of the ArrayResize function

Apr 25, 2014 at 9:41 AM
Edited Apr 25, 2014 at 9:42 AM
Hello,
I have a Problem with the strange behaviour of the resize function. If I use the example:
    public static object MakeArrayAndResize(int rows, int columns)
    {
        object[,] test = new object[1,1];
        object result = MakeArray("Call","t","t",1.0,2.0,45000.0,test);
        // Call Resize via Excel - so if the Resize add-in is not part of this code, it should still work.
        return XlCall.Excel(XlCall.xlUDF, "Resize", result);
    }
Everything is ok and working fine in excel. The same for using the Excel formula directly in the way "=Resize(MakeArray(A1,A2))". This also produces the correct resized results.

Now I have build an own function, which simply collects data from a list and creates an object for each entry of the list. Then I do a Linq-select on those objects and deserialise them again into a single object[,] which I then return to Excel.
     public static object OptionsscheineSelect(string putCall, string emittent, string underlying, double minStrike, double maxStrike, double expiry, object[,] searchRange)
        {

            List<Optionsschein> alleProdukte = new List<Optionsschein>();
            for (int i = 0; i < searchRange.GetLength(0); i++)
            {
                try
                {
                    alleProdukte.Add(new Optionsschein(Convert.ToString(searchRange[i, 0]), Convert.ToString(searchRange[i, 1]), Convert.ToString(searchRange[i, 2]), Convert.ToString(searchRange[i, 3]), Convert.ToDouble(searchRange[i, 4]), Convert.ToDouble(searchRange[i, 5]), ObjectHandler.DateHelper.ConvertToDateTime(Convert.ToDouble(searchRange[i, 6]))));
                }
                catch (Exception)
                {


                }

            }
            DateTime expDateTime = ObjectHandler.DateHelper.ConvertToDateTime(Convert.ToDouble(expiry));
            List<Optionsschein> searchResults = (from ctr in alleProdukte
                                                 where ctr.Typ == putCall && ctr.Emittent == emittent && ctr.Basiswert == underlying && ctr.Basispreis >= minStrike && ctr.Basispreis <= maxStrike && ctr.LetzterBewertungstag == expDateTime
                                                 orderby ctr.Basispreis
                                                 select ctr).ToList();

            object[,] output = new object[searchResults.Count, 9];
            for (int i = 0; i < searchResults.Count; i++)
            {
                output[i, 0] = searchResults[i].Emittent;
                output[i, 1] = searchResults[i].WKN;
                output[i, 2] = searchResults[i].Typ;
                output[i, 3] = searchResults[i].Basiswert;
                output[i, 4] = searchResults[i].Basispreis;
                output[i, 5] = searchResults[i].Bezugsverhältnis;
                output[i, 6] = searchResults[i].LetzterBewertungstag;
                output[i, 7] = searchResults[i].EUWAXRIC;
                output[i, 8] = searchResults[i].EmittentenRIC;
            }


            return output;
        }
This function is working fine as an Array function in Excel, but if I try to resize it within c# or within Excel, I get an #N/V error in some cases, in othe cases fthe whole cells is empty after calculation.

I tried hard to figure out why this is happening, perhaps someone can help?

Kind regards,

Dennis

For complete Information, I am using this code to do the resizing:

``` using System;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Threading;
using ExcelDna.Integration;

public static class ResizeTest
{
public static object MakeArray(string putCall, string emittent, string underlying, double minStrike, double maxStrike, double expiry, object[,] searchRange)
{
    int rows = 9;
    int columns = 9;
    object[,] result = new object[rows, columns];
    for (int i = 0; i < rows; i++)
    {
        for (int j = 0; j < columns; j++)
        {
            result[i, j] = string.Format("({0},{1})", i, j);
        }
    }

    return result;
}

public static object MakeArrayAndResize(int rows, int columns)
{
    object[,] test = new object[1,1];
    object result = MakeArray("Call","t","t",1.0,2.0,45000.0,test);
    // Call Resize via Excel - so if the Resize add-in is not part of this code, it should still work.
    return XlCall.Excel(XlCall.xlUDF, "Resize", result);
}
}

public class Resizer
{
static Queue<ExcelReference> ResizeJobs = new Queue<ExcelReference>();

// This function will run in the UDF context.
// Needs extra protection to allow multithreaded use.
public static object Resize(object[,] array)
{
    ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;
    if (caller == null)
        return array;

    int rows = array.GetLength(0);
    int columns = array.GetLength(1);

    if ((caller.RowLast - caller.RowFirst + 1 != rows) ||
        (caller.ColumnLast - caller.ColumnFirst + 1 != columns))
    {
        // Size problem: enqueue job, call async update and return #N/A
        // TODO: Add guard for ever-changing result?
        EnqueueResize(caller, rows, columns);
        AsyncRunMacro("DoResizing");
        return ExcelError.ExcelErrorNA;
    }

    // Size is already OK - just return result
    return array;
}

static void EnqueueResize(ExcelReference caller, int rows, int columns)
{
    ExcelReference target = new ExcelReference(caller.RowFirst, caller.RowFirst + rows - 1, caller.ColumnFirst, caller.ColumnFirst + columns - 1, caller.SheetId);
    ResizeJobs.Enqueue(target);
}

public static void DoResizing()
{
    while (ResizeJobs.Count > 0)
    {
        DoResize(ResizeJobs.Dequeue());
    }
}

static void DoResize(ExcelReference target)
{
    try
    {
        // Get the current state for reset later

        XlCall.Excel(XlCall.xlcEcho, false);

        // Get the formula in the first cell of the target
        string formula = (string)XlCall.Excel(XlCall.xlfGetCell, 41, target);
        ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);

        bool isFormulaArray = (bool)XlCall.Excel(XlCall.xlfGetCell, 49, target);
        if (isFormulaArray)
        {
            object oldSelectionOnActiveSheet = XlCall.Excel(XlCall.xlfSelection);
            object oldActiveCell = XlCall.Excel(XlCall.xlfActiveCell);

            // Remember old selection and select the first cell of the target
            string firstCellSheet = (string)XlCall.Excel(XlCall.xlSheetNm, firstCell);
            XlCall.Excel(XlCall.xlcWorkbookSelect, new object[] { firstCellSheet });
            object oldSelectionOnArraySheet = XlCall.Excel(XlCall.xlfSelection);
            XlCall.Excel(XlCall.xlcFormulaGoto, firstCell);

            // Extend the selection to the whole array and clear
            XlCall.Excel(XlCall.xlcSelectSpecial, 6);
            ExcelReference oldArray = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);

            oldArray.SetValue(ExcelEmpty.Value);
            XlCall.Excel(XlCall.xlcSelect, oldSelectionOnArraySheet);
            XlCall.Excel(XlCall.xlcFormulaGoto, oldSelectionOnActiveSheet);
        }
        // Get the formula and convert to R1C1 mode
        bool isR1C1Mode = (bool)XlCall.Excel(XlCall.xlfGetWorkspace, 4);
        string formulaR1C1 = formula;
        if (!isR1C1Mode)
        {
            // Set the formula into the whole target
            formulaR1C1 = (string)XlCall.Excel(XlCall.xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
        }
        // Must be R1C1-style references
        object ignoredResult;
        XlCall.XlReturn retval = XlCall.TryExcel(XlCall.xlcFormulaArray, out ignoredResult, formulaR1C1, target);
        if (retval != XlCall.XlReturn.XlReturnSuccess)
        {
            // TODO: Consider what to do now!?
            // Might have failed due to array in the way.
            firstCell.SetValue("'" + formula);
        }
    }
    finally
    {
        XlCall.Excel(XlCall.xlcEcho, true);
    }
}

// Most of this from the newsgroup: http://groups.google.com/group/exceldna/browse_thread/thread/a72c9b9f49523fc9/4577cd6840c7f195
private static readonly TimeSpan BackoffTime = TimeSpan.FromSeconds(1);
static void AsyncRunMacro(string macroName)
{
    // Do this on a new thread....
    Thread newThread = new Thread(delegate()
    {
        while (true)
        {
            try
            {
                RunMacro(macroName);
                break;
            }
            catch (COMException cex)
            {
                if (IsRetry(cex))
                {
                    Thread.Sleep(BackoffTime);
                    continue;
                }
                // TODO: Handle unexpected error
                return;
            }
            catch (Exception ex)
            {
                // TODO: Handle unexpected error
                return;
            }
        }
    });
    newThread.Start();
}

static void RunMacro(string macroName)
{
    object xlApp = ExcelDnaUtil.Application;
    try
    {

        xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, xlApp, new object[] { macroName });
    }
    catch (TargetInvocationException tie)
    {
        throw tie.InnerException;
    }
    finally
    {
        Marshal.ReleaseComObject(xlApp);
    }
}

con
Coordinator
Apr 25, 2014 at 8:34 PM
Hi,

The only thing I can think of is that there might be data types not supported by the Excel arrays - in older versions Int64 (long) was not supported in this context. This is fixed in the current CpdePlex check-ins.

I've also streamlined the ArrayResizer sample's implementation a bit since the first posts and fixed errors in the implementation your post. So you might rather try the current version from the sample here: https://exceldna.codeplex.com/SourceControl/latest#Distribution/Samples/ArrayResizer.dna

To go beyond that, you might like to make a standalone example that I would be able run to reproduce the problem.

-Govert
Apr 28, 2014 at 7:16 AM
Hi Govert,

I thought about the data types too, but this doesn't seem to be the problem, since the function is working well as a "normal" excel array function.

Using the current version didn't help, I just got an value error if I implement it directly in the C# code and also a direct call in excel didn't work.

Can you tell me where to upload the standalone example?

Kind regards,

Dennis
Apr 30, 2014 at 7:45 AM
Hi Govert,

the problem is in this passage of code:
             // Get the formula and convert to R1C1 mode
                bool isR1C1Mode = (bool)Excel(xlfGetWorkspace, 4);
                string formulaR1C1 = formula;
                if (!isR1C1Mode)
                {
                    // Set the formula into the whole target
                    formulaR1C1 = (string)Excel(xlfFormulaConvert, formula, true, false, ExcelMissing.Value, firstCell);
                }
If I turn my excel to R1C1 mode, the resizing works. The xlfFormulaConvert is returning an error.

Is there already a solution for this problem?

Kind regards,

Dennis
Coordinator
Apr 30, 2014 at 8:33 AM
Hi Dennis,

You can mail to govert@icon.co.za.

-Govert
Sep 3, 2014 at 9:41 PM
Edited Sep 4, 2014 at 12:54 AM
Any update or solution for this issue?

Actually I am also getting same strange error. When I reference value from another cell it throws

"Cannot resize array formula at " + firstCellAddress + " - formula might be too long when converted to R1C1 format." error.

But when I type all values in excel function it works fine.

Any idea why am I getting this error.

Resize function:


  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;
                }
                ExcelDna.ComInterop.ComServer.DllRegisterServer();

                // 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);
                    DoResize(target); // Will trigger a recalc by writing formula
                });
                // Return what we have - to prevent flashing #N/A
                return array;
            }

         

            private 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);
                    }
                }
            }
        }

        // RIIA-style helpers to deal with Excel selections
        // Don't use if you agree with Eric Lippert here: http://stackoverflow.com/a/1757344/44264
        public class ExcelEchoOffHelper : XlCall, IDisposable
        {
            private object oldEcho;

            public ExcelEchoOffHelper()
            {
                oldEcho = Excel(xlfGetWorkspace, 40);
                Excel(xlcEcho, false);
            }

            public void Dispose()
            {
                Excel(xlcEcho, oldEcho);
            }
        }

        public class ExcelCalculationManualHelper : XlCall, IDisposable
        {
            private object oldCalculationMode;

            public ExcelCalculationManualHelper()
            {
                oldCalculationMode = Excel(xlfGetDocument, 14);
                Excel(xlcOptionsCalculation, 3);
            }

            public void Dispose()
            {
                Excel(xlcOptionsCalculation, oldCalculationMode);
            }
        }

        // Select an ExcelReference (perhaps on another sheet) allowing changes to be made there.
        // On clean-up, resets all the selections and the active sheet.
        // Should not be used if the work you are going to do will switch sheets, amke new sheets etc.
        public class ExcelSelectionHelper : XlCall, IDisposable
        {
            private object oldSelectionOnActiveSheet;
            private object oldActiveCellOnActiveSheet;

            private object oldSelectionOnRefSheet;
            private object oldActiveCellOnRefSheet;

            public ExcelSelectionHelper(ExcelReference refToSelect)
            {
                // Remember old selection state on the active sheet
                oldSelectionOnActiveSheet = Excel(xlfSelection);
                oldActiveCellOnActiveSheet = Excel(xlfActiveCell);

                // Switch to the sheet we want to select
                string refSheet = (string)Excel(xlSheetNm, refToSelect);
                Excel(xlcWorkbookSelect, new object[] { refSheet });

                // record selection and active cell on the sheet we want to select
                oldSelectionOnRefSheet = Excel(xlfSelection);
                oldActiveCellOnRefSheet = Excel(xlfActiveCell);

                // make the selection
                Excel(xlcFormulaGoto, refToSelect);
            }

            public void Dispose()
            {
                // Reset the selection on the target sheet
                Excel(xlcSelect, oldSelectionOnRefSheet, oldActiveCellOnRefSheet);

                // Reset the sheet originally selected
                string oldActiveSheet = (string)Excel(xlSheetNm, oldSelectionOnActiveSheet);
                Excel(xlcWorkbookSelect, new object[] { oldActiveSheet });

                // Reset the selection in the active sheet (some bugs make this change sometimes too)
                Excel(xlcSelect, oldSelectionOnActiveSheet, oldActiveCellOnActiveSheet);
            }
        }
))
Sep 4, 2014 at 8:31 AM

Hi, no update, just a workaround. The problem ist hat the excel-cell-function cannot have more than 255 characters. If you save the excel file, references in the formula will include Sheetnames and will become very long. So the workaround ist o use excelnames fort he different reference cells.

Kind regards,

Dennis

Sep 4, 2014 at 2:42 PM
I am very new to excel and excelDna could you please give me small example use.

Thanks in advance.