What is the Best way to update other cell value on UDF

[Sam.Park]

Hi

I'd like to provide a user defined function which manupulate cell
values beside target cell.
I see UDF only permit to change target cell value. But In my
situation I'd like to get hitory data from server and show all data
below the target cells. for instance If end user types UDF
"=GetStockPriceForOneYear("GOGL")" , the function
will get array data from server containing stock prices for one year.
And I'd like to show all data from serer below
target cell ( like range )
Is there any way to acheive this ? I wonder If i use RTD I can
acheive this situation .
I am not good at english So Please excuse my short english skill.
Please Let me know if i didn't make clear what i'd lie to do

Regards,
Park

[Govert van Drimmelen]

Hi,

Yours is a common request, but not so easy because it goes against the
way Excel works. So it takes a some care to get right.

I discussed some ideas on the Excel SDK newsgroup here:
http://groups.google.com/group/microsoft.public.excel.sdk/browse_thread/thread/f40761028f0b59f2
and there is a related discussion on this group:
http://groups.google.com/group/exceldna/browse_thread/thread/6c44b0a90a7aafeb

If we got a sensible and reliable way to implement this, I could build
something into Excel-Dna. But I'm not confident about what the right
plan would be yet, so you'll have to experiment a bit.
Maybe some of the exports who have tried this will give some
feedback.....

Cheers,
Govert

[Sam.Park]

Thanks for reply Govert

The first link you gave is the exact same thing what i'd like to do.
Our mission is to make the exact same funtionality like Boolmberg
Excel Add-In
But I'm not sure it is the stable way to use Application Timer. I
think using Timer is too risky to get over what i want to .
Is there any way to use RTD Server for this situation. Is is possible
to change other cell values using RTD Server. I think Bloomberg used
DDE server to get through this situation but i am not sure.

Regards,
Park

On 10월16일, 오후4시32분, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>
> Yours is a common request, but not so easy because it goes against the
> way Excel works. So it takes a some care to get right.
>

> I discussed some ideas on the Excel SDK newsgroup here:http://groups.google.com/group/microsoft.public.excel.sdk/browse_thre...
> and there is a related discussion on this group:http://groups.google.com/group/exceldna/browse_thread/thread/6c44b0a9...

>
> If we got a sensible and reliable way to implement this, I could build
> something into Excel-Dna. But I'm not confident about what the right
> plan would be yet, so you'll have to experiment a bit.
> Maybe some of the exports who have tried this will give some
> feedback.....
>
> Cheers,
> Govert
>
> On Oct 16, 7:23 am, "Sam.Park" <bborie0...@gmail.com> wrote:
>
>
>
> > Hi
>
> > I'd like to provide a user defined function which manupulate cell
> > values beside target cell.
> > I see UDF only permit to change target cell value. But In my
> > situation I'd like to get hitory data from server and show all data
> > below the target cells. for instance If end user types UDF
> > "=GetStockPriceForOneYear("GOGL")" , the function
> > will get array data from server containing stock prices for one year.
> > And I'd like to show all data from serer below
> > target cell ( like range )
> > Is there any way to acheive this ? I wonder If i use RTD I can
> > acheive this situation .
> > I am not good at english So Please excuse my short english skill.
> > Please Let me know if i didn't make clear what i'd lie to do
>
> > Regards,

> > Park- 원본 텍스트 숨기기 -
>
> - 원본 텍스트 보기 -

[Govert van Drimmelen]

Hi,

I don't see how RTD could help with this issue.

I suggest you first try having a separate thread that updates Excel
using the Automation interface, and implement the array formula
insert / caller size checking idea to minimize how often this is
needed and maintain a sane dependency graph inside Excel.
You'll need to be careful also to manage errors on the COM interfaces
that occur when Excel is 'busy' (e.g. when a user is editing a cell).

Let us know what you find.

Regards,
Govert

[rleth...@gmail.com]

You could do it with RTD, but you would have to modify your formulas
(and also write the RTD server). In particular you can only return one
cell value at a time with RTD, rather than a whole array of values, so
instead of having "GetStockPriceForOneYear(string)" you would need
"GetStockPrice(string, date)" as your formula and you would need to
copy down a range of these formulae to get every date you are
interested in. Of course your RTD server would just extract a year (or
whatever) of data, and cache the values.

I'm assuming you want to do the server query asynchronously rather
than entering a formula and waiting?

Here's another idea: your original function goes to the server and
gets the data. The function then returns some unique id that other
worksheet functions can use to extract properties from the data you
extracted (like number of days in data sample, price/date of stock at
position x in series etc).

If either of these approaches sound in any way interesting to you I'd
be happy to expand the description a little bit.

Regards

[gobansaor]

Here's a way of doing it using C++ and VBA http://excelmvf.sourceforge.net

I've implemented the same idea many times in pure VBA but without the
use of array formulas.

The basic idea is:

First time the UDF is called it spawns an object of a "calculation-
event listening" class that is initiated with:

the address of the calling cell,
the formula within the cell,
and the destination address to paste the results to.
If a generic class, the "function" plus associated arguments to
perform the task required would also be passed (in my case it's a SQL
statement) but you could of course have a different class for each
function.

The UDF having successfully spawned an object, returns an intermediate
value to the cell (I usually return 0).

The action of returning a value to the underlying cell fires the
calculate event which is picked you by the already in place object. If
the calculation applies to the object's assigned calling cell it does
whatever it should do (in my case select a table of data from a
database) and pastes the resulting data back to Excel. Remember this
is code independent of the UDF so the no-side-effects rule doesn't
apply, it can affect other cell values. When the data is pasted back,
the calling cell's ".formula" value is set to the original formula.
The object also locks itself i.e. will run only once and deletes
itself (or rather sets in motion the process by which the garbage
collector will do so).

The re-action of rewriting the cell's formula is to fire the cell a
second time. The UDF recognises this is a 2nd call and goes about
getting a result from the calling object (in VBA I use dictionary
objects keyed on the cell's full address to allow result values to be
passed back & forth and keep track of the calling sequence; this is
also the mechanism used to "keep alive" and subsequently de-commission
the spawned object). The UDF then returns this value as its value
(typically an indication of success or failure, the "real" data will
already have been pasted to another location by the now deceased
helper object)

Obviously for this to work, the workbook must be operating in auto-
calculate mode. Also, as others have said,this is against the spirit-
of-Excel's no-side-effects rule, but it does work!

Tom



On Oct 19, 3:38 pm, "rlethbri...@gmail.com" <rlethbri...@gmail.com>
wrote: