Since you cannot change the sheet while the UDF is calculating, you
need some way to do the changes after calculation is complete. The
basic plan is to save enough information as a to-do list in some
global state (e.g. a static variable) so that a subsequent update can
make the changes. Among other bits, you'll need xlfCalller to keep
track of which range was called. Then to trigger the update there are
a few plans:
1. Make a separate thread that will call into Excel using the
Automation interface (other threads can't call Excel4/Excel12).
2. Trap the sheet calculate event, then check your to-do list and do
the updates,
3. Set a timer to trigger a macro that does the update (though
xlcTimer does not work from a UDF, so you'd have to use
Application.Timer). Or you could set up a repeated timer based on
xlcTimer from the AutoOpen to poll for work to do. There is some user-
inconvenience from timer-calls though (e.g copy/cut selection is
cleared).
It has been suggested that Bloomberg uses DDE to poke Excel. I don't
know how this would work.
Partager