Multiple sessions (not just windows) of Excel

[starl]

Session A is using RTD formulas via an Addin to get stock market data every 2 seconds (I noticed Calculate event runs every 2 seconds).
My code (not the addin) needs to watch for changes in the RTD event. Problem is - when the addin is running, the Calculate event can't run and vice versa.

My solution would be to have 2 separate sessions of Excel. Session A lets the addin do its thing while Session B would watch Session A for changes and run its code.

Can anyone think of potential issues with this solution? I've tested by created the two sessions, running (and pausing) code in 1 session while triggering an event in the other - and seems to work fine. Now, obviously, I won't be able to use an event in session 2 to capture the updating in session 1.. unless I use a class module to capture application level events in session 1.... ok, a few things to think about but - back to the question - anyone experienced and know of issues I need to be aware of?

 

[Jon Peltier]

How is your code watching for changes?

 

[starl]

I don't actually have anything - I'm still in the concept/thinking it through phase.

I'm rather turned off by the use of a class module to watch the Events in the other session. I'd have to use the Calculate event, which appears to be triggered every 2 seconds.
The other option I'm considering is using OnTime to check every minute (because that's actually when the market change is considered completed).
All the computer that this will be running on does is the market info.

so, thinking the concept through a bit more:
User opens my workbook (session A) and it opens the RTD workbook as a new session (B). Session A will hold a session variable connection to session B.
Session A runs an OnTime. The sub called by the OnTime (every minute) will read certain cells in session B and run some logic checks.
If at any time the connection is lost, program will try to re-establish it, else inform the user.

 

[Jon Peltier]

The problem with two instances is that communication between them will be slow.

If you use the Worksheet_Calculate event, does it interfere with the RTD acquisition? You could have a test in the procedure that exits if the data hasn't changed.

 

[starl]

There some other stuff going on in the RTD workbook that may be interfering with code - I don't know. For example, there's a chart in a userform that the user can configure. And since it keeps up to date with changes in the data, I'm assuming it also runs often. And then the peculiarity of Excel crashing on a simple .value = .value line of code. It wasn't part of the event. I was just creating a small sub to explain a few concepts to the user. All the that sub did was copy a value from one cell to another.. and Excel would crash. Open the workbook, enable macros - which would run the Calculate Event that just had a few lines of code comparing values - and boom. crash again. So I wanted my code to be in a different session entirely. I'm not reading very much data.. maybe 6 - 9 cells? Would the communication be that slow?
Also, at this time, I don't know how much code I'll have running when the conditions are met (client doesn't know what he wants). Let's assume my code is in the same workbook. So, conditions are met (test procedure) and now my code is doing its logic. Events are disabled, my code takes 5 seconds to run, can the RTD update while my code is still running. If not, then the workbook will update late, possibly reading the wrong information (I'm not impressed with the addin's coders... eg they've made one sub case sensitive, but another sub that uses the exact same data is not. In this case, there's no reason for it to be case sensitive and if they're willing to allow the "wrong" case through once, why not again? anyway....)

 

[Jon Peltier]

There some other stuff going on in the RTD workbook that may be interfering with code - I don't know. For example, there's a chart in a userform that the user can configure. And since it keeps up to date with changes in the data, I'm assuming it also runs often. And then the peculiarity of Excel crashing on a simple .value = .value line of code. It wasn't part of the event. I was just creating a small sub to explain a few concepts to the user. All the that sub did was copy a value from one cell to another.. and Excel would crash. Open the workbook, enable macros - which would run the Calculate Event that just had a few lines of code comparing values - and boom. crash again. So I wanted my code to be in a different session entirely. I'm not reading very much data.. maybe 6 - 9 cells? Would the communication be that slow?
Also, at this time, I don't know how much code I'll have running when the conditions are met (client doesn't know what he wants). Let's assume my code is in the same workbook. So, conditions are met (test procedure) and now my code is doing its logic. Events are disabled, my code takes 5 seconds to run, can the RTD update while my code is still running. If not, then the workbook will update late, possibly reading the wrong information (I'm not impressed with the addin's coders... eg they've made one sub case sensitive, but another sub that uses the exact same data is not. In this case, there's no reason for it to be case sensitive and if they're willing to allow the "wrong" case through once, why not again? anyway....)

Some random reactions to your post.

A lot of things that are up to date with data changes are just formulas, but these trigger the calc event.

Why is the chart in a userform? That requires at least a copy and paste if not an export and load picture.

The crashing is a problem, of course.

The time VBA takes to read cells in another instance is at least as slow and probably slower than in the same instance. Speed it up by reading a contiguous range in one .value step instead of 6 to 9 individual cells. But you know that (now I recognize your name, sorry).

Using other coders' code is always an adventure. So is RTD.

Finally, you'll have to start putting it together, maybe both ways while you compare the performance.

 

[starl]

(omg! Jon Peltier recognized me!! I *love* your site and refer/reference it whenever clients need charts) /clears throat, returns to composure

I didn't really get a close look at that userform chart. I suppose it's possible that it's not IN Excel.. I don't have a license for the addin and it was only something he quickly showed me. I didn't fully inspect it.

I'll create a test workbook and see what the results are.

thanks for sharing your experience, Jon - much appreciated. If I see anything enlightening, I'll update here.