Excel-DNA: one-dimensional array limited to 65,536 rows

Question

When I try to call the next Excel-DNA method in VBA, I get only an array of size 1 (after 65536 lines, the array seems to change to the size of the real array - 65537). When a method is called in a sheet as an array function, everything works.

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

I work with Excel 2007, the sheet is 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 two-dimensional arrays, on the contrary, is the same as if one happens

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

Does anyone have any ideas 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 

the output has a size of 65537 (indexing starts at 0), as well as numbers greater than 65537.

Answer 1

Given that you specified different performance using 2-dimensional arrays and flipping sizes, it sounds like you are running into a limit of rows and / or columns.

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

I assume that when you request an object with 65537 columns, the constructor silently handles the overflow and resolves it 1.