search

gdemin / excel.link

Convenient Data Exchange between R and Microsoft Excel
56 stars 16 forks source link

REvaluate #2

Closed tonyday567 closed 8 years ago

tonyday567 commented 8 years ago

Hi,

I'm using REvaluate and the live R session and it's working very well. I understand the experimental nature of it but I have a costly computation and calling R once is a must have for me.

There is one main issue preventing me from getting it into production - not knowing when the underlying R process throws an error and/or dies. To help get feedback somehow onto the sheet, I wrap all my R calls in a tryCatch and return an 'ok' or error message to communicate what's going on. Something like this:

=REvaluate("xl[['a1']]=errorGuard(someFunction()))

And it works fine. I have a lot of these cells, however, and I tried to swap to a cell reference like so:

=REvaluate("xl[['&cell("address",a1)&"']]=errorGuard(someFunction()))

But this creates an infinite loop. I presume excel sees that a1 changes and assumes that this function cell then needs to be updated - again and again and again.

Is there any way around this, or am I heading down the wrong track?

gdemin commented 8 years ago

Hi! Yes, it's circular reference. cell("address",a1) always returns $A$1 so REvaluate put results in cell A1 that force recalculation of REvaluate and et cetera. Calculations will never finish. You could write something like this:

 =REvaluate("xl[['&A1&"']]=errorGuard(someFunction()))

And REvalute will return result to address contained in cell A1.