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.

Fortuna

Did you want to use object[] instead of object[,] in the first example? – Bell

If you make a VBA function returning such a one-dimensional array, and also call it via Application.Run, do you have the same issue? – Oireachtas

yes the first example is an object[] – Appointor

No the code in VBA works fine, I added an example above – Appointor

Could you compare the object[65537,1] case with object[1, 65537]? I think the latter should be the same as object[65537]. – Oireachtas

you are right, as in the object[65537] case I get a one-dimensional variant in vba with object[1,65537] – Appointor

@Xelluloid can I confirm that you want to 1. return one dimensional array indexing from 0 to 1 or 2. return two dimensional array indexing from 0 to 1, 0 to 65537? – Ginn

I want to return an one-dimensional array indexing from 1 to x where x > 65537 but that does not work as the array (variant) size in excel is limited to 65536 (as in old excel versions this was the row limitation) – Appointor

The Excel C API has no one-dimensional arrays. Excel-DNA (which uses the C API) marshals object[65537] as object[1,65537]. What remains is to confirm that this is a really limitation (bug?) in the Excel C API by making a small C add-in that returns an array type XLOPER12 with the right contents, and checking that it has the same problem. This would take Excel-DNA out of the question, at least. – Oireachtas

Interesting to see. Used to run into this in VB3 (the language, not Office) where the array size limit was a signed 16-bit integer, it would allow arrays with user-defined lower bounds (including negative numbers), but you still couldn't have a size greater than 2^15. – Dalliance

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.

Poundfoolish

Incidentally, 65537 when stripped of the bits above 2^13 (16384) is 1 – Poundfoolish