UDF returning an array - always returns a Horizontal array

[newbie]

Govert,

I encountered the kind of issue as described in this
http://exceldna.codeplex.com/discussions/255330.

After some trial and error, I discovered that when an array is
returned, its orientation is always set to horizontal. Take your
TestArray example:

[ExcelFunction(Description = "TestArray", Category = "test
functions")]
public static object[] TestArray()
{
object[] result = new object[2];
result[0] = 1.1;
result[1] = 2.3;
return result;
}

If this is called over A1:B1, I got the result as expected. If this
is called over A1:A2, I get two 1.1's.

In another project which connects unmanaged C++ code to Excel, I have
an utility function that detects the orientation of the vector by
inspecting the dimension of the XLOPER. Is it possible to incorporate
something similar to ExcelDNA? Thanks.

----------------------
ObjectHandler::CallerDimensions::Type
getCallerDimension()
{
ObjectHandler::Xloper xCaller;
ObjectHandler::Xloper xMulti;

Excel(xlfCaller, &xCaller, 0);
Excel(xlCoerce, &xMulti, 2, &xCaller, TempInt(xltypeMulti));

ObjectHandler::CallerDimensions::Type callerDimensions;
if (xMulti->val.array.rows == 1 && xMulti->val.array.columns > 1) {
callerDimensions = ObjectHandler::CallerDimensions::Row;
} else {
callerDimensions = ObjectHandler::CallerDimensions::Column;
}

return callerDimensions;
}
----------------------

[Govert van Drimmelen]

Hi Candy,

You are right, Excel will interpret a 1D array as a row-vector.
If you change the return type to a 2D array, object[,], you have full
control of the shape of the array you return.

You can detect the shape of the caller with code similar to what you
show. Maybe the c# would be:

ExcelReference caller =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);
// Now you can inspect the size of the caller with
int rows = caller.RowLast - caller.RowFirst + 1;
int cols = caller.ColumnLast - caller.ColumnFirst + 1;

and then return a 2D array with many rows and one column, if
appropriate.

-Govert

On Mar 23, 5:22 pm, newbie <candy.chiu...@gmail.com> wrote:
> Govert,
>

> I encountered the kind of issue as described in thishttp://exceldna.codeplex.com/discussions/255330.