array udf as a parameter to another udf

Mar 14, 2015 at 12:37 PM
Not sure if this is an Excel problem or related to Excel DNA. I have an udf called Append which returns an array (defined as Object[,] in C#). This works fines and if your ctrl-shift-enter in Excel it shows up as an array. However, if I pass this as a parameter to another udf say =index(append(...), 2, 1) it doesn't work. The append() array appears as a 1x1 cell. I've even tried ctrl-shift-enter but when passed into Index it always appears as a 1x1 cell.

I recall getting this to work before when using the Excel SDK. Using excel functions this is similar to =index(transpose(a1:a5), 2, 1) . where transpose returns an array which is passed into index and returns row 2 column 1 of the transposed array.

Mar 14, 2015 at 4:53 PM
Hi Jon,

That's not what I see.

Using this function
    public static object GetArray(int rows, int cols)
        var result = new object[rows, cols];
        for (int i = 0; i < rows; i++)
            for (int j = 0; j < cols; j++)
                result[i, j] = i + "," + j;
        return result;
The formula: =INDEX(GetArray(3,2), 2, 1) returns 1,0 as expected.

Mar 14, 2015 at 5:31 PM
Hi Govert,

Yes you are correct. I had a bug in my code which would allocate the result depending on the size of the caller function. So in which case it was 1x1 as it was being called from the index() call. Guess it's better to always return the entire result array as you don't know where else it'll get pass to.

Thanks for your quick reply.