Excel-DNA: One-Dimensional Array limited to 65536 rows

Question

When trying to call the following Excel-DNA-Method in VBA I only get an array of size 1 (after 65536 rows the array seems to be resized to real array size - 65537). When calling the method in the sheet as array function the whole thing works.

[ExcelFunction(Description = "Example", Name = "Example", HelpTopic = "")]
        public static object[] example() {
            object[] ret = new object[65537];
            return ret;
        }

I am working with Excel 2007, the sheet is a xlsm-Worksheet, when using two-dimensional arrays like this, everything works fine.

 [ExcelFunction(Description = "Example", Name = "Example", HelpTopic = "")]
        public static object[,] example() {
            object[,] ret = new object[65537,1];
            return ret;
        }

But using the two-dimension arrays the other way round the same as in case one happens

[ExcelFunction(Description = "Example", Name = "Example", HelpTopic = "")]
        public static object[,] example() {
            object[,] ret = new object[1,65537];
            return ret;
        }

Does someone have an idea how to get around this?

Doing the same thing in VBA works fine

Function test()
    Dim ret As Variant
    ReDim ret(65536)
    test = ret
End Function

Sub testSub()
    Dim output
    output = Application.Run("test")
End Sub

output has a dimension of 65537 (indexing starts with 0), also numbers greater than 65537 work.

Answer 1

Given that you indicated different performance using the 2 dimensional arrays and flipping the dimensions, it sounds like your running into the limit of rows and/or columns.

This page: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx gives the limits for Excel 2007. As you'll see, the number of columns in a worksheet is limited to 16,384 which your value exceeds many times over. On the other hand, the row limit of 1,048,576 easily can accommodate a value of 65537.

My guess would be that when you request the object with 65537 columns, the constructor is silently handling the overflow and resolving it to 1.