Excel DNA resize return array vertically

Question

I am trying to return dynamic array with help of Excel-DNA api. I am able to produce desired output. But I want to produce output in vertical .

here is the c# code for my UDF. i have used this link for producing my sample. http://excel-dna.net/2011/01/30/resizing-excel-udf-result-arrays/

Below code produces this output:

(0,0) (0,1) (0,2) (0,3)

I want output in this format:

(0,0)
(0,1)
(0,2)
(0,3)

Here is the code:

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



namespace ExcelUDFs
{
    public class UDFs
    {


        [ExcelFunction(Description = "Make Array")]
        public static object MakeArrayAndResize(int columns)
        {

            object result = MakeArray(columns);
            // Call Resize via Excel - so if the Resize add-in is not part of this code, it should still work.
            var a = XlCall.Excel(XlCall.xlUDF, "Resize", result);
            return a;
        }

         [ExcelFunction(Description = "Make Array")]
        public static object MakeArray( int columns)
        {
            object[,] result = new string[1, columns];
            for (int i = 0; i < 1; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    result[i, j] = string.Format("({0},{1})", i, j);
                }
            }

            return result;
        }




         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;
            //object[,] retArray = new object[columns, rows];
            //for(int i=0;i<array.Length;i++)
            //{
            //    retArray[i, 0] = array[0, i];
            //}
            //return retArray;
        }

        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=null;
            try
            {
                 xlApp = ExcelDnaUtil.Application;
                xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, xlApp, new object[] { macroName });
            }
            catch (TargetInvocationException tie)
            {
                throw tie.InnerException;
            }
            finally
            {
                Marshal.ReleaseComObject(xlApp);
            }
        }

        const uint RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
        const uint VBA_E_IGNORE = 0x800AC472;
        static bool IsRetry(COMException e)
        {
            uint errorCode = (uint)e.ErrorCode;
            switch (errorCode)
            {
                case RPC_E_SERVERCALL_RETRYLATER:
                case VBA_E_IGNORE:
                    return true;
                default:
                    return false;
            }
        }
    }
}

The return value of your array function can be a 2D object[,] array, and can contain the data in any shape you want. It's not clear from your question why you couldn't just return a 4 by 1 array from your function. — Govert
– Govert, Commented May 2, 2014 at 17:14
I just converted 1*4 array to 4*1 and it gave desired output. Thanks. — user781700
– user781700, Commented May 2, 2014 at 18:46

Pragmateek · Accepted Answer · 2018-01-19 10:28:29Z

3

Convert your 1D array to 2D array and call resize method.

object[,] retArray = new object[a.Length, 1];
for (int i = 0; i < a.Length; i++)
{
    retArray[i, 0] = a[i];
}
var ab = XlCall.Excel(XlCall.xlUDF, "Resize", retArray);
return ab;

edited Jan 19, 2018 at 10:28

Pragmateek

13.6k9 gold badges80 silver badges112 bronze badges

answered May 2, 2014 at 18:50

user781700

9644 gold badges15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel DNA resize return array vertically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related