Discussion:
Returning to multiple cells via UDF
(too old to reply)
Steven Miller
2010-10-09 21:08:06 UTC
Permalink
Hey,

For those of you who knows the Bloomberg API, I'm trying to replicate
the BDH functions functionality. For those of you not familiar with
this Excel Bloomberg UDF, it retreives historical data of e.g. a stock
within a specified date interval, and then puts the appropriate amount
of data in the right amount of cells under the cell in which the
formula was entered - for example, if the user were to enter
=BDH("Microsoft Equity", "PX_LAST", 01/31/2010, 02/28/2010) the
function would retrieve historical data from between dates 01/31/2010
and 02/28/2010 - it would put the first date on top and all subsequent
data points in the cells below.

So far I've found no way of returning values to other than the cell
that the formula was entered into. How can this be done? If the
Bloomberg developers can, I'm guessing it is somehow possible for all
of us :)

Thanks guys,
Govert van Drimmelen
2010-10-10 09:29:01 UTC
Permalink
Hi Steven,

You are subverting Excel's spreadsheet and calculation model, so you
should be careful when doing this. Anyway....

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.

One idea when doing the update is not just to set the target cell
values, but to set an array formula to the target range. The array
formula would check whether the returned data is bigger then the
calling range or not, and only do the async work when the calling
range needs to change size. That way recalculations that only update
the data, not the result size, need not re-do the ugly async work.

If you are willing to use .NET for your .xll, the above schemes can be
implemented quite easily using Excel-Dna. This thread has some
discussion, in which there is an example concluding that the separate-
thread Automation plan seems to work:
http://groups.google.com/group/exceldna/browse_thread/thread/6c44b0a90a7aafeb.

Regards,
Govert
Excel-Dna - Free and easy .NET for Excel
http://exceldna.codeplex.com
Post by Steven Miller
Hey,
For those of you who knows the Bloomberg API, I'm trying to replicate
the BDH functions functionality. For those of you not familiar with
this Excel Bloomberg UDF, it retreives historical data of e.g. a stock
within a specified date interval, and then puts the appropriate amount
of data in the right amount of cells under the cell in which the
formula was entered - for example, if the user were to enter
=BDH("Microsoft Equity", "PX_LAST", 01/31/2010, 02/28/2010) the
function would retrieve historical data from between dates 01/31/2010
and 02/28/2010 - it would put the first date on top and all subsequent
data points in the cells below.
So far I've found no way of returning values to other than the cell
that the formula was entered into. How can this be done? If the
Bloomberg developers can, I'm guessing it is somehow possible for all
of us :)
Thanks guys,
Steven Miller
2010-10-12 16:51:03 UTC
Permalink
Post by Govert van Drimmelen
Hi Steven,
You are subverting Excel's spreadsheet and calculation model, so you
should be careful when doing this. Anyway....
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
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.
One idea when doing the update is not just to set the target cell
values, but to set an array formula to the target range. The array
formula would check whether the returned data is bigger then the
calling range or not, and only do the async work when the calling
range needs to change size. That way recalculations that only update
the data, not the result size, need not re-do the ugly async work.
If you are willing to use .NET for your .xll, the above schemes can be
implemented quite easily using Excel-Dna. This thread has some
discussion, in which there is an example concluding that the separate-
thread Automation plan seems to work:http://groups.google.com/group/exceldna/browse_thread/thread/6c44b0a9....
Regards,
Govert
Excel-Dna - Free and easy .NET for Excelhttp://exceldna.codeplex.com
Post by Steven Miller
Hey,
For those of you who knows the Bloomberg API, I'm trying to replicate
the BDH functions functionality. For those of you not familiar with
this Excel Bloomberg UDF, it retreives historical data of e.g. a stock
within a specified date interval, and then puts the appropriate amount
of data in the right amount of cells under the cell in which the
formula was entered - for example, if the user were to enter
=BDH("Microsoft Equity", "PX_LAST", 01/31/2010, 02/28/2010) the
function would retrieve historical data from between dates 01/31/2010
and 02/28/2010 - it would put the first date on top and all subsequent
data points in the cells below.
So far I've found no way of returning values to other than the cell
that the formula was entered into. How can this be done? If the
Bloomberg developers can, I'm guessing it is somehow possible for all
of us :)
Thanks guys,
Govert,

Thank you so much for your help! I will be trying these suggestions
out and I'll definitely return with the results.

Kind regards,

Loading...