Function returning single row 2D array automatically transposed into 1D array in VBA?

Aug 7, 2013 at 2:30 PM

Pardon my inexperience as I'm rather new to Excel-DNA, Excel development in general and VBA as I'm more of a C# developer.
The particular problem I'm dealing with is that I noticed that on a function made available through Excel DNA that returns a 2D array (object[,] return type) is automatically turned into a variant variable in VBA with an array of a single dimension instead of two. This is only happening if a single row is returned.
Is there anything I could do to prevent that, I'm wondering if this is by design and it's very well possible this has nothign to do about Excel DNA.

The other issue I've got is that no matter what I do (no Option Base statement and tried Option Base 0) the variant variable will be set as a 1 based array when returned from the function. I read that it's base 0 by default and I therefore expected 0 based arrays but this isn't happening.

Any help would be greatly appreciated and thanks for your lib.

Aug 7, 2013 at 4:12 PM
Hi Daniel,

I think the two issues you raise have nothing to do with Excel-DNA, but are artifacts of the COM conversions between Excel and VBA.
It seems the only way to get the number of array dimensions is to add an error handler:

The array index base is likely also an artifact of the COM marshaling - Excel-DNA uses the Excel C API which only supports 0-based arrays.

Aug 14, 2013 at 8:33 AM
Hi there
Thanks for your help.
In the end I exposed the function via registration on IExcelAddin.AutoOpen call which then makes a call to ExcelComAddInHelper.LoadComAddIn(comAddin), in this way the array is of the correct format so it's all good.
Sadly hitting all sort of troubles with Excel VSTO deployment with ClickOnce where addins when uninstalled in some particular ways from that point on can't be easily re-enabled even though the correct key is present in HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins.