View Single Post
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

Hi Tony,

Welcome to the forum.

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.)

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:

    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:

    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:

    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