Sep 22, 2010 at 7:39 PM
Edited Sep 22, 2010 at 8:22 PM
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.