How to resize array downward when "Resizing Excel UDF result arrays"?

Aug 27, 2012 at 3:11 AM

Hello,

It's great work of ExcelDNA that made me develop C# addins possible. The VBA has trouble me a lot, as I'm more familiar with C#.

Array returns trouble me a lot before found code in "Resizing Excel UDF result arrays". The author is so talented!

I do a simple test on the code ,add codes from "Resizing Excel UDF result arrays" to my project, slightly modify the code to make it show no errors in VS2010:

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

Then my own function:

        public static double[] RangeCopy(double[] range )
        {
            double[] range_return = new double[range.Length];
            range_return = range;
            return range_return;      
        
        }

Number 1 to 5 is filled in a column at left.Then just at right column of number 1, use function "=Resize(RangeCopy(I21:I25))", the results shows as below, return numbers fills laterally:
1	1	2	3	4	5
2					
3					
4					
5					
But I want them fills downward, how to achieve this purpose?


Coordinator
Sep 18, 2012 at 5:45 AM

Hi,

Excel interprets a 1D array as a single row.

If you return a 2D array from your RangeCopy function, you can control the shape exactly.

So maybe change your function to 

public static double[,] RangeCopy(double[] range )
{
     double[,] range_return = new double[range.Length, 1];
     for (int i = 0; i < range.Length; i++
     {
         range_return[i,0] = range[i];
     }
     return range_return;
}

Please write back if that doesn't address your question.

Regards,

Govert