Return to selection cell

34 views
Skip to first unread message

Hagai David

unread,
May 15, 2022, 2:54:45 AMMay 15
to Excel-DNA
In the example of the RTD timer in the Functions.cs file we return the value to the chosen cell in the Excel how my code recognize the chosen cell ?   

In my code I'm using " Microsoft.Office.Interop.Excel" to send data to specific cell I determined in advance. 
But somehow in the Timer code it knows exactly to where return the "time"  automatically  , I want to know how could I figure out what cell has been chosen by the user like Govert did and use it to other purpose of mine in my code. 

Here is the Govert's code: 

public static class Functions

{

public static object TimerNow()

{

return XlCall.RTD("TimerRTD.RtdServer", null, "NOW");




I googled a little and found some function which called "selection", but in this code it seems this issue been handled in much easier way, could someone expand about it pleas  ? 

Thanks, 
Hagai 

Govert van Drimmelen

unread,
May 18, 2022, 12:43:50 AMMay 18
to exce...@googlegroups.com

Hi David,

 

For functions that are called from a worksheet, the Excel calculation engine is invoking the function, and it know what formula and cell that call is made from.

For the RTD feature, Excel also keeps track of the cell(s) that called a particular RTD topic. So when the RTD server tells Excel that the topic has a new value, it is Excel itself which then decides which cells to recalculate.

 

So the Timer code does not ‘know’ where to return the value to, it is Excel itself that knows what cells called the timer code.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/566b09a6-dc72-4d8c-8a03-eaf9591ebf86n%40googlegroups.com.

Hagai David

unread,
May 19, 2022, 1:51:50 AMMay 19
to Excel-DNA
Alright then, 
Do I have a method track through the selection file in the Excel  ? 
I explain what my purpose is: 
Eventually I conducted RTD server which keeps data of invoke FunctionName which called by the user.
I suppose to follow after each invoke FunctionName, and when he deletes the cell and the FunctionName no longer written  I delete the some cache from the RTD server. 
Thus, I was thinking about track after the value of each chosen cell and see what "words" its contain. 
Do I have any way get access from my code to the selection on the worksheet  and analyze what the user is doing ? 
Thanks, 

ב-יום רביעי, 18 במאי 2022 בשעה 07:43:50 UTC+3, Govert van Drimmelen כתב/ה:

Kedar Kulkarni

unread,
May 19, 2022, 1:49:28 PMMay 19
to Excel-DNA
Hi Hagai, 
You can not get access to the selection but can identify the cell information (where the formula is called from) using the following code which should give you sheet id and row and column. 

ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

If you are using rtd server then you could store this when it connects in connect data. it should call disconnect data when cell formula is deleted and that my may provide you the opportunity to do the cleanup work. I have not tried it myself with rtd server yet but should work IMO. 

thanks
Kedar

Govert van Drimmelen

unread,
May 20, 2022, 3:20:33 AMMay 20
to exce...@googlegroups.com

Hi Hagai,

 

For RTD topics you don’t need to keep track of the caller to clean up.

There will be an RTD DisconnectTopic call to tell you when the RTD topic is no longer used, and that is the place to do the clean-up.

If you have an IObservable stream, the IDisposable.Dispose of the object you return from the Subscribe call will allow you to clean up.

 

Tracking the calling cells for RTD can be a problem if you want to do this kind of clean up, since an RTD topic can be linked from more than one cell, and after a formula is deleted the function does not get called again.

Hagai David

unread,
May 24, 2022, 12:18:30 PMMay 24
to Excel-DNA
Thanks  Kedar and Govert for your answers! 

Firstly I relate to Kedar answer, it a good direction go with  in other mission I want to do ! I'll be glad  to know where did you see a basic example which I can see how to get the column and row from the worksheet 

Secodnly, Goevrt-  it significant info thanks for sharing! 
I be glad if you could relate the next issues I'll point on:

DisconnectTopic Function you'v mentioned will automatically track after the all Topics which had been called?  I assume I should define some stuff  but how do I do it ?  and what if I got multiples Topics how do I do it?
How does it know it shouldn't delete the cache yet because one or more of this topic is still on ? might you have an example ? 


About what you wrote here :  
"Tracking the calling cells for RTD can be a problem if you want to do this kind of clean up, since an RTD topic can be linked from more than one cell, and after a formula is deleted the function does not get called again."

-I was planing track after each Topic that called and sign it in kind of array and while the user delete the formula I see what cell been deleted and figure out which formula wrote there, then count down the "mentions" of this Topic and when it come to 0 I delete its cache. if the user ask this topic again I will start counting all over again for this topic. (This idea for every selection in the worksheet and checking the user didn't delete anything) 
Of course your suggestion is much more easier ! 


Thanks you !!!






"
ב-יום שישי, 20 במאי 2022 בשעה 10:20:33 UTC+3, Govert van Drimmelen כתב/ה:

Govert van Drimmelen

unread,
May 24, 2022, 3:02:36 PMMay 24
to exce...@googlegroups.com

Hi Hagai,

 

It’s hard to answer your questions without more context about the code you are writing.

We’ve discussed various ways of implementing RTD servers, and you might have to show exactly what you’ve done.

I’m answering here under the assumption that you have implemented your own RTD server, hence implemented the IRtdServer interface on some class.

 

DisconnectTopic Function you'v mentioned will automatically track after the all Topics which had been called? 

 

 

I’m sorry, the correct name for the function is “DisconnectData”. See IRtdServer.DisconnectData method (Excel) | Microsoft Docs and various links for RTD information from our previous discussions.

 

I assume I should define some stuff  but how do I do it ? 

 

As you see, it gets called for a specific topic, when there are no more cells with RTD calls with the topic strings that defined the particular topic (as passed to the ConnectData function IRtdServer.ConnectData method (Excel) | Microsoft Docs

 

Your RTD server must already implement DisconnectData (it’s part of the IRtdServer interface). You can look at the examples I’ve previously linked to for details.

 

and what if I got multiples Topics how do I do it?

 

Every topic is given a TopicId by Excel, which is passed to your server in the ConnectData call. This same TopicID is passed to the DisconnectData call, allowing you to track the topic lifetime.

 

How does it know it shouldn't delete the cache yet because one or more of this topic is still on ?

 

Excel internally keeps track of whether there are still cells with RTD calls to a particular topic.

Reply all
Reply to author
Forward
0 new messages