Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Go Back  Xtreme Visual Basic Talk > > > Multi-threaded real-time data handling in Excel with COM


Reply
 
Thread Tools Display Modes
  #1  
Old 06-22-2010, 11:15 AM
Tony Tang Tony Tang is offline
Newcomer
 
Join Date: Jun 2010
Posts: 5
Default Multi-threaded real-time data handling in Excel with COM


Gurus,

I have an Excel based algo-trading app that needs to take in lots of real-time data, and make trading decisions based on the calculations driven by the data. It is not one of those "read-only" stock ticker kinda spreadsheets.

I created a COM object with VB.NET that runs in its own thread space which collects data from a socket, and when the data is read it raises an Event with data attached. In Excel VBA, the event handler parses the data, puts them to the right places on the spreadsheet to feed calculations (so far it sounds like a stock ticker app), and then, based on the results of the various calculations it does something.

The problem is that when the user starts to click around the spreadsheet while there is large amount of data coming, the event handler's cell updating breaks apart. If the app is left alone without user action, it stands well. My frustration comes from not understanding what happens to the main thread when a user clicks around. Either the event handler blocks the user action, or the user action blocks the event handler (under which case I will put in some queuing facility). But I just don't see how user action can break the code (like causing VBA to stop executing)

What is the nature of this disease? And what is the cure?

Thanks in advance...
Reply With Quote
  #2  
Old 06-22-2010, 12:31 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Tony,

Welcome to the forum.

Quote:
Originally Posted by Tony Tang
My frustration comes from not understanding what happens to the main thread when a user clicks around. Either the event handler blocks the user action, or the user action blocks the event handler (under which case I will put in some queuing facility).
Excel is single threaded when performing macro and/or user actions, so only one or the other can be occurring at a given time. If the user is performing an action, then your macro will be blocked, if the macro is running, then the user will be blocked. (Note that the user can also break a macro's execution via the 'Esc' key, but this can be prevented by setting the Application.EnableCancelKey property to xlDisabled or xlErrorHandler for the duration of the method.)

Quote:
Originally Posted by Tony Tang
But I just don't see how user action can break the code (like causing VBA to stop executing)
It shouldn't be able to do this, but I suspect that the problem is what you wrote earlier: "I created a COM object with VB.NET that runs in its own thread space..."

Since Excel is single threaded, I'm not sure what happens when you try to raise an event that is handled by VBA within Excel without synchronizing your thread to the UI thread. One way to synchronize it is to access the Excel object model using Excel's main window handle (a.k.a. "Hwnd") and the AccessibleObjectFromWindow API, which is explained in the the article Getting the Application Object in a Shimmed Automation Add-in by Andrew Whitechapel. This could be a bit hard to follow, because this article is written in C#, so I have discussion and a translation of it to VB.NET here.

Unfortunately, I'm not sure that this will help you... The reason is that you are not making calls into the Excel object model directly, but instead are raising an event that is *handled* by a VBA method that runs within Excel. It's not clear how to synch this directly...

The trick, then, would be to get *Excel* to call your VBA method for you. And better yet, to call it in such a way that Excel is guaranteed to be in a state that it can handle the request -- that is, is guaranteed to not be in a state where the user is currently editing a cell, or using a modal dialog box, or the like.

So how to do this? The Excel.Application.OnTime method. By calling this method, I'm 99.9% sure that you won't even have to synchronize your call into the Excel object model first -- so you can forget about all the AccessibleObjectFromWindow API complexities for the moment.

What you would do is, have your .NET add-in call the Excel.Application.OnTime method, passing in a string representing the name of the VBA method (or event handler) that is to receive the call. If you pass in a time of DateTime.Now, Excel will invoke the command at the soonest point that it is able -- which is usually instantly, unless Excel is currently occupied with modal user actions or another macro that might currently be running.

For example, if your VBA event handler resided in a standard module, were named "MyEventHandler", and resided in a workbook named "My Workbook.xls", then the call to OnTime using VB.NET could look as follows:

Code:
    excelApp.OnTime(DateTime.Now, "'My Workbook.xls'!MyEventHandler", Schedule:=True)
If you need to pass arguments to your event handler, these must be contained within the string. For example, to pass in the value of 99, you can use:

Code:
    excelApp.OnTime(DateTime.Now, "'My Workbook.xls'!'MyEventHandler 99'", Schedule:=True)
(Note the surrounding single-quote characters in the above.)

If you need to pass in a string, then surround the string with double quote characters TWICE, such as ""Hello"". Multiple arguments should be separated by a comma. For example, the following passes in the values of 99 and ""Hello"" into the method named MyEventHandler by using OnTime:

Code:
        
    excelApp.OnTime(DateTime.Now, "'My Workbook.xls'!'MyEventHandler 99, ""hello""'", Schedule:=True)
No guarantees, of course, but I strongly believe that this should straighten you out. Note that this call to Excel.Application.OnTime is asynchronous, so it will leave your thread free to continue processing. Excel will call the VBA method that you specify in the string on its own UI thread when Excel processing is freed up to do so.

If this doesn't work, come back, explain what's happening, and we can try to fix it or try something else. I have my fingers crossed for you, let us know how it goes...

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 06-23-2010, 12:13 AM
Tony Tang Tony Tang is offline
Newcomer
 
Join Date: Jun 2010
Posts: 5
Default

Thank you so much, Mike...

If I understand you correctly, you are suggesting that I bypass the EventHandling mechanism all together and let the .NET COM component calls OnTime method directly.

First, I cheated a bit: I kept my current VBA event handler. In it, I saved the incoming data (along with the event) to a variable that can be seen by the real processer process(), to avoid parameter passing. Then the event handler called the Excel.Application.OnTime method, the way you suggested.

It didn't work (cheatings usually don't). When user clicks around the sheet, or even move the scroll bar on the sheet, the cell setting VBA code in process() broke.

If I were forced to explain it, I guess I could say that by the time my event handler is executed, things are beyond rescue. Fine... Now I have to try calling this thing asynchronously from the .NET component, who is running in a different thread space.

Three questions:

1) How do I invoke this method? What do I have to Import for this method to be visible to the .NET component module, both at coding time and at runtime?

2) How do I pass a variable to my VBA process() routine? Now the VBA side doesn't have the data, which is not a trivial value. I have to supply it with the call. Will this kind of cross-thread parameter passing work? Once I release the data in the calling thread, will the memory associated with it be garbage-collected, although it is being used in another thread? Or should I design it in such a way that process() will access a depository to read the data and dispose it after usage?

3) Once the OnTime() method is called, is this timer-like thing be permanently insalled, or will it expire itself once the target routine is executed?

Thank you...
Reply With Quote
  #4  
Old 06-23-2010, 07:09 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Tony,

Ok, there are two separate issues going on here.

(1) The first is making sure that Excel's user IO and your code do not trample over each other. The use of Application.OnTime prevents this.

See the attached workbook as an example. Run the "MyMacro" routine, which uses OnTime to run a routine for 3 seconds, take a break for 3 seconds, then resume the routine for 3 seconds, etc. (Make sure you save and close all other workbooks when you do this because this method writes values to the active cell and cannot be terminated without exiting Excel. It's just a very crude demo.)

By running this, you'll see that if you type into a cell and keep typing, that the OnTime call will wait until you are done before resuming. So user actions will automatically cause the programmatic actions to be put on hold.

Similarly, if the macro is already running, the user cannot interrupt this action, as I've even suppressed the 'Esc' key by setting 'Application.EnableCancelKey = xlDisabled'.

This does not cover the threading issue, but it does show that this kind of simultaneous activity between the user and executing code can peacefully co-exist.

(2) Calling Excel from thread that did not create it usually causes very bizarre results and/or crashes unless you synchronize this call. I'm surprised that you've not been having catestrophically bad results already, to be honest. That is, if a thread that you create tries to directly access the Excel object that was created by another thread, it won't work.

One way to obtain the Excel object synchronized to your thread is to make use of Microsoft.VisualBasic.Interaction.GetObject or System.Runtime.InteropServices.Marshal.GetActiveObject. However, these are crude mechanisms that will pick up *any* Excel object from the running object table, without your ability to choose which one. (These approaches are fine only if you are certain that your Excel instance is the only one running at this time. This might be true "most of the time", but returning the wrong Excel application in those instances when it is not would have disastrous concequences.)

You can, however, receive an Excel application instance that is synchronized to your thread by making use of the AccessibleObjectFromWindow API, which is explained in the the article Getting the Application Object in a Shimmed Automation Add-in by Andrew Whitechapel. This article is written in C#, but I have discussion and a translation of it to VB.NET here.

I realize that this is a lot to implement, but it really works as advertised, I've used this myself many times. I think you need to implement this to ensure that this is not the cause of your troubles.

(Other approaches to synchronization would be through the use of SynchronizationContext or ISynchronizeInvoke. These approaches are actually more direct, but I have less experience with them with Excel. But you might want to learn about these techniques on your own, or ask and I can take a stab at it.)

Ok, now to address some of your particular comments and questions:

Quote:
Originally Posted by Tony Tang View Post
If I understand you correctly, you are suggesting that I bypass the EventHandling mechanism all together and let the .NET COM component calls OnTime method directly.
Pretty much yes. You don't have to change your code much though. Simply at the point that calls 'RaiseEvent', change it to (a) obtain a synchronized instance of the Excel application object via the AccessibleObjectFromWindow API, and then (b) call Excel.Application.OnTime, passing in the name of the VBA event handler.

Keep in mind, however, that by using Application.OnTime, the VBA event handler will now run on Excel's UI thread, which is NOT the same as your main thread. That is, you are raising your event asynchronously. So the conflict you had before on the Excel worksheet might now rear it's head within your .NET code. You now have to think about thread safety: can the VBA event handler safely run at the same time as your background code? You might need to use locks or other synchronization tools to make sure that they don't have race conditions that destroy the integrity of the running code.

Quote:
First, I cheated a bit: I kept my current VBA event handler. In it, I saved the incoming data (along with the event) to a variable that can be seen by the real processer process(), to avoid parameter passing. Then the event handler called the Excel.Application.OnTime method, the way you suggested.

It didn't work (cheatings usually don't). When user clicks around the sheet, or even move the scroll bar on the sheet, the cell setting VBA code in process() broke.
I'm not sure that I follow you. Avoiding parameter passes by allowing direct access to the same data should generally be fine -- but since you have two separate threads operating here, you definitely have to be aware of thread safety issues.

Quote:
If I were forced to explain it, I guess I could say that by the time my event handler is executed, things are beyond rescue.
Why? Nothing happens *until* your event handler is executed. However, at that moment, things can get hairy as one has to worry about thread synchronization with the Excel applications's UI thread as well as potential multi-threading issues within your own code.

Quote:
Fine... Now I have to try calling this thing asynchronously from the .NET component, who is running in a different thread space.
Yes, exactly.

Quote:
1) How do I invoke this method? What do I have to Import for this method to be visible to the .NET component module, both at coding time and at runtime?
Which method? Do you mean Excel.Application.OnTime? Or do you wish to invoke the VBA event handler directly from .NET without the use of OnTime? Both are possible, but because the user could be editing a cell, using a modal dialog box, or the like, I think it's definitely best to (a) synchronize your call to the Excel UI thread by making use of the AccessibleObjectFromWindow API and then (b) use Excel.Application.OnTime to call your VBA method.

Quote:
2) How do I pass a variable to my VBA process() routine? Now the VBA side doesn't have the data, which is not a trivial value. I have to supply it with the call. Will this kind of cross-thread parameter passing work? Once I release the data in the calling thread, will the memory associated with it be garbage-collected, although it is being used in another thread? Or should I design it in such a way that process() will access a depository to read the data and dispose it after usage?
I'm not sure that I follow. What kind of data is this, an array? How did you pass this value to VBA before? Simple values can be passed as part of the string to OnTime, as explained in my previous post. More complex values like an Array would be tough though. In this case, one approach could be to have a method in your .NET codebase be exposed to COM via a ComVisible attribute that the VBA event handler could call to get the data. But, again, here is where you really need to think about thread safety, as VBA could be pulling data on one thread at the same time that your main thread is writing data.

Quote:
3) Once the OnTime() method is called, is this timer-like thing be permanently insalled, or will it expire itself once the target routine is executed?
Each call to Excel.Application.OnTime schedules only one call. So it's not like a typical repeating timer in this case. For event raising, as you are doing, this setup is actually better because you can call it once for each time that you wish to raise the event without having to worry about "turning it off".

Good luck Tony, hope this helps...

Mike
Attached Files
File Type: zip OnTime Testing.zip (12.5 KB, 31 views)
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb

Last edited by Mike Rosenblum; 06-23-2010 at 08:15 AM.
Reply With Quote
  #5  
Old 06-23-2010, 10:27 AM
Tony Tang Tony Tang is offline
Newcomer
 
Join Date: Jun 2010
Posts: 5
Default

Hi, Mike,

I am at a stage where I have known more but are more confused. So please bear with me if you could...

1) My data collecting thread is wrapped by an object and is spawned off by the Excel VBA after the object is instantiated. In my view it is not a main thread, but Excel's UI and VBA is.

I intend to keep this collector thread totally parallel to VBA. So when you talk about "One way to obtain the Excel object synchronized to your thread ...", you seem to be saying that this collector thread should be synced up with Excel VBA execution. But that is precisely what I want to avoid.

I want to give the UI thread an event with data attached, so that it can do something with it in its thread space. I thought when the event handler is invoked, it was synced up inside of the UI thread. But according to your earlier explanation, and confirmed with the result, the event handling mechanism causes interference with user actions.

2) So we are now onto the OnTime() method. The sample you provided justifies my cheating: your OnTime is not called from a foreign thread either, it is called from the same thread as the UI. I did the same thing: I let the old event handler call OnTime. Because the event handler is triggered in the UI thread, it should work the same way as your sample, because from that point on it has nothing to do with the collector thread. I simply don't understand how the actual data handling invoked by OnTime method from my event handler still causes interference with user action.

3) It seems to be quite a hassle for the collector thread to get an Excel app instance. In my case, since this thread is spawn off by Excel, can I pass a handle of Excel itself to the collector thread, so that later it can use it to call the OnTime method?

And to be perfectly honest, I don't even know how to declare that application variable in VB.NET. In your previous posting, you showed excelApp.OnTime(...) as a line of VB.NET code. But prior to that line, how do I declare this excelApp variable, and how does the complier know that OnTime() is a legit method?

Thanks a lot...

Last edited by Tony Tang; 06-23-2010 at 11:07 AM.
Reply With Quote
  #6  
Old 06-24-2010, 01:03 PM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Tony,

Quote:
I am at a stage where I have known more but are more confused. So please bear with me if you could...
There are two basic issues to keep in mind:

(1) Excel is single-threaded, so you can only have one action operating at a time: either the user, or your code. But not both. The only way to "sort of" have both is to have your code yield via DoEvents calls, which is a kind of simulated threading. But if DoEvents is not involved, only the user or your can be operational at any time.

(2) Multi-threading with Excel should require synchronizing with the UI thread before making calls to it. And if you are juggling more than one thread, for example if Excel's UI thread can call back into your VB.NET dll while your DLL's thread is also calculating, then you need to be wary of thread safety via locks and/or other means.

However, some of the things you wrote in your most recent post are starting to confuse me, so let me ask about them:

Quote:
My data collecting thread is wrapped by an object and is spawned off by the Excel VBA after the object is instantiated. In my view it is not a main thread, but Excel's UI and VBA is.
I'm confused here... VBA cannot spawn off separate threads. In your first post, you wrote that you were using VB.NET in the background, so I assumed that it was VB.NET that was creating the second thread.

To be clear, does your VB.NET code actually start a new thread, something like this:

Code:
Dim myThread As Thread  = New Thread(MyMethod)
myThread.Start("MyMethod")
Or are you using simulated threading in VBA via the use of DoEvents?

Quote:
I intend to keep this collector thread totally parallel to VBA. So when you talk about "One way to obtain the Excel object synchronized to your thread ...", you seem to be saying that this collector thread should be synced up with Excel VBA execution. But that is precisely what I want to avoid.
If you are in fact operating a second thread behind the scenes, then it does not have to be synched up with Excel all the time. But when it calls into Excel it has to be, or Excel cannot process it (safely).

If you are worried about your background thread being temporarily paused while calling into Excel on a synchronized basis:

(1) You could always spawn off yet another thread to do the updating within Excel, which then synchronizes with Excel and makes the call to Excel, while the background thread remains in the background, doing what it needs to do.

(2) Even better, by using the Excel.Application.OnTime callback method, you would not need to spawn another thread for this, as this method is an asynchronous callback. Your background thread would never get occupied as the call to Excel.Application.OnTime would take but a milisecond and then the call back to the name of the VBA method specified would occur on Excel's UI thread. This VBA method could then call back into your VB.NET DLL, but beware that you now have two threads operating at the same time, so be careful of thread safety.

Quote:
I want to give the UI thread an event with data attached, so that it can do something with it in its thread space. I thought when the event handler is invoked, it was synced up inside of the UI thread. But according to your earlier explanation, and confirmed with the result, the event handling mechanism causes interference with user actions.
Actually, by showing that the event BLOCKS user actions shows that it IS SYNCHED UP with Excel's UI thread. Excel is single threaded, so if VBA code is running, the user is locked out, and vice versa.

Quote:
So we are now onto the OnTime() method. The sample you provided justifies my cheating: your OnTime is not called from a foreign thread either, it is called from the same thread as the UI.
I'm not sure what you mean by cheating. Using a VBA callback like this is not only legitimate, it's the best way to ensure that Excel is ready to process your code when Excel is best capable of handling the call.

As for calling from the same thread from the UI, yes, that's correct, I was only looking to demonstrate the lock-out effect that only the user or the VBA macro can be running at one time. Creating a VB.NET DLL and spawning a 2nd thread, etc. would have been a lot more work for me. But it might not be a bad idea for you to do, so that you can have a test model to experiment with.

Quote:
I did the same thing: I let the old event handler call OnTime. Because the event handler is triggered in the UI thread, it should work the same way as your sample, because from that point on it has nothing to do with the collector thread. I simply don't understand how the actual data handling invoked by OnTime method from my event handler still causes interference with user action.
Not sure. Are you synchronizing your thread with Excel before calling it?

Quote:
t seems to be quite a hassle for the collector thread to get an Excel app instance. In my case, since this thread is spawn off by Excel, can I pass a handle of Excel itself to the collector thread, so that later it can use it to call the OnTime method?
I'm not sure that I understand. A thread can only make use of the Excel object if it was created within that same thread. If not, then the thread will have to synchronize with Excel. See my previous posts above about how you can do this.

Quote:
And to be perfectly honest, I don't even know how to declare that application variable in VB.NET. In your previous posting, you showed excelApp.OnTime(...) as a line of VB.NET code. But prior to that line, how do I declare this excelApp variable, and how does the complier know that OnTime() is a legit method?
I most definitely can help you with this... But how are you doing this now?? You said that your VB.NET code is taking actions on the worksheet and having trouble doing so when the user is also taking actions. So how is your VB.NET code interacting with Excel right now?

I know that this is a lot, but if you clarify these points I am sure that we can get you through this...

,
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #7  
Old 06-25-2010, 01:20 AM
Tony Tang Tony Tang is offline
Newcomer
 
Join Date: Jun 2010
Posts: 5
Default

I have to stop commenting and just describe as acurately as I can... So let me try, from the very beginning.

1) I have a .NET COM object, let's call it Collector, in which a thread is spawn, just the way you described. In the thread there is an event loop that collects real-time data from various sources. The Collector class declares a MyDataEvent. After a Collector object collects data and verifies, it raises a MyDataEvent with the actual data attached.

2) In Excel VBA, I declare a Collector object variable so its MyDataEvent (along with other initialization methods) is exposed, and I setup MyEventHanler to handle the event.

3) I instantiate the Collector class from VBA, the thread is spawn, and the data arrives.

4) When MyEventHandler is triggered, all the interaction with Excel is done with VBA, the .NET code has no part of it. Raising events is the only thing Collector object does. In fact I wrote a console app to handle the same events, it works fine. So we are clear that the .NET code doesn't touch Excel and it doesn't know who catches the MyDataEvent.

5) MyEventHandler in VBA parses the data, and writes it to different parts of the spreadsheet, triggering various calculations. When left alone, the Excel works fine. It can take large amount of data and performs all sorts of real-time calc, and the the events are fully serialized: no matter how frequent the data comes and how hideous the calculations are, it won't drop anything, won't miscalculate, whatever.

6) When user starts to touch the spreadsheet (such as draging the scroll bar, randomly clicking around), the VBA code in MyEventHandler breaks, generating run-time error. It always breaks at the spot where it tries to write values to spreadsheet cells.

7) I tried calling OnTime inside MyEventHandler (which I deemed as "cheating", because you suggested that I should call OnTime from within the Collector background thread). It doesn't work, crashing exactly the same way.

That's pretty much it.

Mike, I really appreciate your help. I'm under tremendous pressure to deliver this non-trivial app and I am totally helpless. If it is ok, can I call you to discuss? You can send me your number via email: acttang@yahoo.com, and a convenient time for me to call. Thank you so much!

-Tony
Reply With Quote
  #8  
Old 06-25-2010, 07:08 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Tony,

That is a PERFECT description... and it clarifies that what I thought was going wrong is exactly what is going wrong: you are raising the event in VB.NET from a thread that is not the same as Excel's UI thread. So when the VBA handler is called via the 'Collector.MyDataEvent', it is actually running on the thread created by VB.NET, not on Excel's main UI thread. So whenever the VBA handler attempts to control the Excel object model, it crashes, because it is not calling Excel from the correct thread.

Similarly, calling Excel.Application.OnTime from the wrong thread will also fail, because this is also a call to the Excel object model from a non-Excel UI thread. The solution is to obtain a reference to the Excel object model that is synchronized to Excel's UI thread and *then* call Excel.Application.OnTime.

You can do this by Excel's main window handle (a.k.a. "Hwnd") and the AccessibleObjectFromWindow API, which is explained in the the article Getting the Application Object in a Shimmed Automation Add-in by Andrew Whitechapel. I have discussion and a translation of it to VB.NET here.

The key to this routine, is that your Collector class would need to know the main window handle for Excel. So I would create a method on your Collector class called 'Init', for example, with the following signature: 'Init(excelHwnd as Long)'. This method would need to be exposed to COM, so that it can be called by VBA. The VBA method that creates the Collector would then call myCollector.Init(Application.Hwnd). The Collector.Init method would then save this Hwnd value in a field somewhere. Then, when the spawned thread needs to raise its event, it would make use of the Hwnd that was previously saved along with the AccessibleObjectFromWindow API in order to get an instance of the Excel object model that can be called from the spawned thread. The spawned thread would then call Excel.Application.OnTime using the Excel instance that was created using the AccessibleObjectFromWindow API.

Note that the Excel instance created using the AccessibleObjectFromWindow API only has to be created once per thread, so you only have to do this once, and then you can save it in a global variable that this thread can use over and over. (You should also look to clean up this Excel.Application object reference when Excel exits, via a call to Marshal.FinalReleaseComObject or else Excel will likely hang in the Task Manager, but I would worry about only one problem at a time right now, and the potential for a hanging ghost reference is not a big deal compared to just getting this thing working right now. Come back again if you need help solving Excel instances piling up in the Task Manager.)

So to summarize:

(1) Create a COM-visible method on your Collector class that allows VBA to pass in the Hwnd for the Excel application.

(2) Have your background thread make use of the Hwnd and the AccessibleObjectFromWindow API in order to get a synchronized Excel.Application object reference that can be called from the background thread.

(3) Have the background thread call Excel.Application.OnTime using the synchronized Excel.Application object.

If you do all this, it really should work without any problems. The hard part is the AccessibleObjectFromWindow API, but it's not really that hard, it's just intimidating looking. Do read Andrew Whitechapel's article but then see my translation to VB.NET here.

Good luck Tony, and let us know how it goes...
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #9  
Old 06-27-2010, 10:45 PM
Tony Tang Tony Tang is offline
Newcomer
 
Join Date: Jun 2010
Posts: 5
Default

I am now convinced that the whole Event handling business is single threaded, and it perfectly explains why my old style of coding was not working.

With your suggestion of using OnTime(), I first tried an easier way of getting the Excel Application object from .NET, by using Marshall.GetActiveObject. I get it once at initializing time and use it repeatedly. When data comes, the Collector calls the OnTime() method associated with the Excel Application object, together with the target handler MyEventHandler.

Again, the user actions break the execution of MyEventHandler, although it is now invoked by Excel timer, which was previously installed by the .NET object in a different thread. The whole Excel just crashes without any error message. It proves that using a saved reference to the Excel Application obj is NOT safe.

It begs the following fundamental question: how different is your way of acquiring Excel object, as you adapted from the C# solution, from mine? You mentioned previously that even with your way of acquiring the object, you CAN save it once and use it later. So what is the difference after all?

Thank you...
Reply With Quote
  #10  
Old 06-28-2010, 05:21 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Tony,

Quote:
Originally Posted by Tony Tang View Post
I am now convinced that the whole Event handling business is single threaded, and it perfectly explains why my old style of coding was not working.
Yes, Excel is single threaded for user operations and executing macros. (It can be multi-threaded only for formula calculations in Excel 2007 and above.)

Quote:
With your suggestion of using OnTime(), I first tried an easier way of getting the Excel Application object from .NET, by using Marshall.GetActiveObject. I get it once at initializing time and use it repeatedly. When data comes, the Collector calls the OnTime() method associated with the Excel Application object, together with the target handler MyEventHandler.

Again, the user actions break the execution of MyEventHandler, although it is now invoked by Excel timer, which was previously installed by the .NET object in a different thread.
I surprised to hear this... and very disappointed.


Quote:
The whole Excel just crashes without any error message. It proves that using a saved reference to the Excel Application obj is NOT safe.
I'm sorry that you are having a problem, I really am, but not being able to use a saved reference really shouldn't be the problem. Just make sure that each thread uses it's own synchronized reference. You are correct in that a reference created by one thread *cannot* be utilized by another thread. You have a rather large and complex solution, so where is the weakness is not clear to me.

If you think that using the same, saved reference is somehow the issue, then you can test this by creating a new synchronized instance to use every time you call Application.OnTime. That is, use the AccessibleObjectFromWindow API (or GetObject) every single time you wish to call the Excel object model. My guess is that your program will continue to fail in exactly the same way. If it does note fail in exactly the same way, then you have not been careful that each thread only makes use of it's own, saved Excel application instance.

Quote:
It begs the following fundamental question: how different is your way of acquiring Excel object, as you adapted from the C# solution, from mine?
If you can guarantee that there is only one Excel application instance running at a time, then there should be no difference between them; if there is more than one Excel application instance running, then there is a big difference. Both of these approaches allow you to grab an Excel instance in a manner that synchronizes your thread to the Excel object model. (Out-of-process automation will handle the marshalling / thread-synchronizing for you.) The huge difference, however, is that if there is more than one Excel application running, then you have no control over which Excel application object that GetObject will return, whereas the AccessibleObjectFromWindow API will always return EXACTLY the Excel application instance that has the main window handle (hwnd) value that you specify.

For your current testing, there may be no difference at all here, since you probably have only one Excel instance running. But, if there are hanging instances of Excel running in the task manager, then GetObject could be retuning one of these instances, in which case you will definitely have disastrous results.

Quote:
You mentioned previously that even with your way of acquiring the object, you CAN save it once and use it later. So what is the difference after all?
Yes, absolutely should be able to. But, again, if you don't trust this, then I would simply not save the reference and get the reference fresh by using the AccessibleObjectFromWindow API, or GetObject, every time and see if this corrects the issue.

The other thing I would try, is to actually make use of the AccessibleObjectFromWindow API instead of GetObject. I know that it looks intimidating, but the article by Andrew Whitechapel is here and my translation of it to VB.NET can be found here. It really does work as advirtised.

For what it's worth, I have used the AccessibleObjectFromWindow API to enable various threads to safely call into the Excel object model, so I know first-hand that this really does work. GetObject does work as well, but, as I explained, it's not precise in terms of *which* Excel object it returns. If you are having trouble making use of a previously-saved Excel application instance, then test creating a new one each time to see if that helps, but I don't think that this is the issue. I would definitely try the AccessibleObjectFromWindow API, however, as it really should work. If it doesn't work, then there is something else going on here that we don't understand, but I think that this is the most important step for you to try.

I've got my fingers crossed for you Tony...
Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #11  
Old 03-14-2014, 08:37 PM
sadboy309 sadboy309 is offline
Newcomer
 
Join Date: Mar 2014
Posts: 1
Default

I think, "CloseHandle hThread" really not close completely CreatThread. You see:
Attached Files
File Type: zip timeFrom_testMultiThread.zip (26.6 KB, 13 views)
Reply With Quote
Reply

Tags
excel, multithreading, real-time, vb.net, vba


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
 
Multi-threaded real-time data handling in Excel with COM
Multi-threaded real-time data handling in Excel with COM
 
-->