7

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.

Sebastian Müller
  • 5,471
  • 13
  • 51
  • 79
  • Did you want to use `object[]` instead of `object[,]` in the first example? – CodesInChaos Jan 10 '13 at 13:15
  • 1
    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? – Govert Jan 10 '13 at 19:52
  • yes the first example is an object[] – Sebastian Müller Jan 11 '13 at 15:00
  • No the code in VBA works fine, I added an example above – Sebastian Müller Jan 11 '13 at 15:15
  • 2
    Could you compare the object[65537,1] case with object[1, 65537]? I think the latter should be the same as object[65537]. – Govert Jan 16 '13 at 20:31
  • you are right, as in the object[65537] case I get a one-dimensional variant in vba with object[1,65537] – Sebastian Müller Jan 17 '13 at 11:32
  • @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? – bonCodigo Jan 18 '13 at 05:09
  • 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) – Sebastian Müller Jan 18 '13 at 07:25
  • 2
    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. – Govert Jan 18 '13 at 10:21
  • 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. – Adrian Jan 22 '13 at 19:23

1 Answers1

2

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.

Christopher J Smith
  • 150
  • 13