CODEWITHSUNDEEP
c#excel-dna
VegDork
VegDork

Reputation: 266

Excel Dna - How do I populate mutliple cells from the result of a UDF?

I have the following function:

[ExcelFunction(Name = "GetFundPositions")]
public static object GetFundPositions()
{
    object[,] positions = new object[5, 2];

    positions[0, 0] = "BOOK_ABC"; positions[0, 1] = "USD";
    positions[1, 0] = "BOOK_ABC"; positions[1, 1] = "RUB";
    positions[2, 0] = "BOOK_DEF"; positions[2, 1] = "CHF";
    positions[3, 0] = "BOOK_XYZ"; positions[3, 1] = "EUR";
    positions[4, 0] = "BOOK_XYZ"; positions[4, 1] = "RUB";

    return XlCall.Excel(XlCall.xlUDF, "Resize", positions);
}

Which I am calling in cell A1 with the array formula:

{=GetFundPositions()}

I was expecting the results to be populated in cells A1:B5 but only the first element of the array is being populated in cell A1.

What am I missing?

Upvotes: 0

Views: 818

Answers (1)

C. Augusto Proiete
C. Augusto Proiete

Reputation: 27878

It looks like you don't have the Resize function in your code... Resize is not a function that exists within Excel... It's the name of a function that should exist inside of your add-in (and you can call it whatever you want... Doesn't have to be Resize BTW).

That is the function responsible for doing all the work of resizing the range where the results appear.

You can see an example of the source code you need in the ArrayResizer.dna sample code.

However, this resizing technique has a number of known issues (e.g. 1, 2) and should be avoided... It's better to return the array without any resizing, and let the user select all the cells that should be filled.

Upvotes: 1

Related Questions