dll library function returning an array

Sep 22, 2010 at 12:20 PM

Dear Govert;

First of all I would like to thank your for such a great tool.

I am trying to create an excel function (user defined - from dll file) which returns an array of stirngs and would like each array element to be displayed in consequent rows of a column. Is it possible?

Thanks in advance, regards, veysel

Coordinator
Sep 22, 2010 at 6:40 PM

Hi Veysel,

One- and two-dimensional arrays of type object are supported (types object[] and object[,]).

A one-dimensional arrays is interpreted by Excel as a single row, so you probably want to try:

 

public static object[,] GetStrings()
{
   return new object[,] {{"One"}, {"Two"}, {"Three"}};
}

 

This would then be entered into your sheet as an array formula (Ctrl+Shift+Enter).

Regards,

Govert

Sep 22, 2010 at 7:09 PM

Dear Govert;

Thanks for the quick response. But I am trying to accomplish something different....

How can I manage to fill

A2 = One

A3 = Two

A4 = Three

Once I enter "getStrings()" function in A1 cell? Please consider the fact that the array size is not known by the excel user who calls the function.

I believe that it is possible to enter a value in a specific sheet and a cell from C# code...

Regards, veysel

 

Coordinator
Sep 22, 2010 at 7:39 PM
Edited Sep 22, 2010 at 8:22 PM

Ah!

 

This is not so easy, because it breaks Excel's functional model. Excel does not like you to change the sheet while computing a function, so you cannot set cell values from inside a UDF - whether the UDF is in C or VBA or whatever. So there is no built-in support for this in Excel-Dna at the moment.

 

However, there are some standard tricks to simulate this behaviour. Here is a recent MSDN thread on the topic: http://social.msdn.microsoft.com/Forums/en/exceldev/thread/8d27d3b7-73ae-4443-aac6-8b5b5e1130d3, and there is a relevant StackOverflow question: http://stackoverflow.com/questions/847301/how-to-fill-up-cells-within-a-excel-worksheet-from-a-vba-function too.

I think you should consider:

1. Set up a macro to run a bit later. I think it is OK to call Application.OnTime(...) from your UDF. This allows you to call a processing handler as a macro after the recalculation is complete. An example of this is the DoLater() function in the MoreSamples.dna file in the Distribution\Samples folder. There are reports that Application.OnTime is not perfectly reliable though.

2. As an alternative, you can hook the calculate event (setting a handler macro using xlcOnRecalc in your AutoOpen). Then store info on work to do in some shared memory (like a static variable) which gets processed in the event handler.

3. You could set up a completely separate thread to poll some shared memory structure and apply changes as needed.

All of these options should make you proceed with some caution - you are trying to do something that subverts Excel's calculation model.  

 

Any reports on what you try, and what your findings are, will be most appreciated.

 

Regards,

Govert