Implementing IMessageFilter in an Office add-in

Implementing IMessageFilter in an Office add-in | MS Tech News

Implementing IMessageFilter in an Office add-in | MS Tech News — Thu, 20 Nov 2008 08:39:27 GMT

PingBack from http://mstechnews.info/2008/11/implementing-imessagefilter-in-an-office-add-in/

re: Implementing IMessageFilter in an Office add-in

incre-d — Thu, 27 Nov 2008 12:40:40 GMT

I presumed I could use this technique in Excel 2003. I'm currently using VS2008 with Excel 2003.

I see that if I have pressed f5 i can get the "retry?" message.

From this article I was hoping I could replace the Application.OnTime event in VBA. I've experimented with various things mostly catching the exception and waiting till it's no longer fired. I was hoping this article would get around that technique.

My standard test is to block excel by holding down the mouse with a range selected, but the error is then thrown in the CallExcel method.

Additionally sometimes I get

{"COM object that has been separated from its underlying RCW cannot be used."}. I especially get this if I've blocked the thread with a form, or holding down the mouse. These error messages will continue. If I leave the workbook for a bit then it eventually works once, and then fails again. So the failures look sporadic.

I found 3 threads on the forums which deal with this message.

My Implementation:

I implemented your code then added code to fire workbooknewsheet event which then calls the InvokeAsyncCallToExcel()

Regards

code from my addin...

private void ThisAddIn_Startup(object sender, System.EventArgs e)

{

#region VSTO generated code

this.Application = (Excel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(Excel.Application), this.Application);

this.Application.WorkbookNewSheet += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookNewSheetEventHandler(Application_WorkbookNewSheet);

#endregion

}

void Application_WorkbookNewSheet(Microsoft.Office.Interop.Excel.Workbook Wb, object Sh)

{

InvokeAsyncCallToExcel();

}

re: Implementing IMessageFilter in an Office add-in

andreww — Tue, 02 Dec 2008 09:59:14 GMT

incre-d - I've tested the exact same code with a new solution built with VS2008 to target Excel 2003, and it behaves in exactly the same way.

Using the code I provided, I don't see a reason for the COM object/RCW separation error you're getting - although, of course, once you do get this error, there's really nothing you can do to recover - and you have to assume your application is in an indeterminate state.

The technique I describe is really only useful in the scenarios I mention. I don't really see how you could use it to model the Application.Time behavior - that method is used to schedule a VBA macro procedure to be run at a specified time in the future, which seems to be unrelated to the scenarios I describe.

re: Implementing IMessageFilter in an Office add-in

incre-d — Tue, 02 Dec 2008 13:42:52 GMT

Hi,

So I went and tested the original zip file on a fresh build of VS 2008 sp1 with excel 2007.

I have 3 scenarios, and the log I get below.

I see in your code you specifically comment that it is to handle a modal form being open, and waiting for user input. And in that scenario, it works as intended/expected.

The scenario I am trying to find an elegant solution for is where the user is holding down the mouse key with a range selected. Not exactly what your code was written for (the modal form) but to me a similar issue, how to handle when excel is locking the thread when the mouse key is pressed. Perhaps there is no need to elegantly handle this, and catching the exception and re-firing the failed event after a delay is sufficient. But I was hoping there was a native excel method of handling this type of issue.

Many thanks

My 3 scenarios

--------------

Press Asynch with no user interference.

----

The thread 0x19848 has exited with code 0 (0x0).

Press Asynch, immediately follow with F5, wait for retry message, press cancel and retry.

----

RetryRejectedCall

MessagePending

The thread 0x1a5f4 has exited with code 0 (0x0).

Press Asynch, highlight a range, and keep the mouse button down.

----

A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll

System.Runtime.InteropServices.COMException (0x800AC472): Exception from HRESULT: 0x800AC472

Server stack trace:

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at Microsoft.Office.Interop.Excel.Range.set_Value2(Object )

at ExcelAddInMessageFilter.ThisAddIn.CallExcel() in C:\development\ExcelAddInMessageFilter\ExcelAddInMessageFilter\ExcelAddInMessageFilter\ThisAddIn.cs:line 97

The thread 0x1a35c has exited with code 0 (0x0).

Exception rethrown at [0]:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)

at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)

at Microsoft.Office.Interop.Excel.Range.set_Value2(Object )

at ExcelAddInMessageFilter.ThisAddIn.CallExcel() in C:\development\ExcelAddInMessageFilter\ExcelAddInMessageFilter\ExcelAddInMessageFilter\ThisAddIn.cs:line 97

The thread 0x1a4b4 has exited with code 0 (0x0).

re: Implementing IMessageFilter in an Office add-in

andreww — Thu, 04 Dec 2008 04:53:28 GMT

incre-d - thanks for the clarification. I believe this specific scenario is standard Excel behavior, by design.

You'll notice that while the user has the mouse key down as part of a selection, the worksheet is not writeable, although calls to the OM that only attempt to read worksheet values will succeed.

So, in my example, if you replace this:

this.Application.ActiveCell.Value2 =

DateTime.Now.ToShortTimeString();

...with, say, this:

Debug.WriteLine(this.Application.ActiveCell.Value2.ToString());

then, the call succeeds.

Multiple Threads

Leor — Fri, 19 Dec 2008 00:30:52 GMT

Above, you state:

"If you don’t create any background threads in your add-in, and therefore make all OM calls on the same thread your add-in was created on, your call won’t fail, it simply won’t be invoked until the host is unblocked."

In my application, I am not explicitly creating new threads. I have a console application which manipulates an office application. The application is written in C#, but uses components written in VB. It appears as if my client is having it's calls rejected by the office application because it is too busy.

Question: Do you have any idea why this may be the case, if my application should hang and not issue any new calls until the previous one has returned? Either way, I will try implementing the message filter given above. Thanks!

re: Implementing IMessageFilter in an Office add-in

andreww — Sat, 27 Dec 2008 01:13:30 GMT

Leor - the scenario I describe is an in-process add-in. The scenario you are describing is an out-of-process automation client. Obviously, making calls cross-process in this way will have superficially similar results to making calls cross-thread in-process.

re: Implementing IMessageFilter in an Office add-in

Leor — Sat, 27 Dec 2008 01:38:22 GMT

Thanks. I had no idea. I thought they were both out of process.

For those of you interested, it turns out that I created a message filter, implemented it, and co-registered it. However, it appears as if it is not registering. Perhaps they need to be in the same process in order to register.

Overall, I'd say that my software solution is pretty complicated, and I wouldn't be surprised if I find out that the reason its not running properly is because it has something to do with this (http://blogs.msdn.com/vbteam/archive/2008/03/24/stathread-vs-mtathread-whorst.aspx).

re: Implementing IMessageFilter in an Office add-in

Richard (VSTO EE in Microsoft Support) — Tue, 17 Feb 2009 18:07:02 GMT

A little warning to those who attempt to implement this:

(1) Depending on your situation, it may not apply to your code in the manner you think it should. For example, since events raised by delegates in .NET can run from worker threads which don't use the message filter becasue (1) they are running in MTA and (2) their are multiple threads (pooled) in which the event might be raised, making it impossible to fully know what thread will handle the event in all cases.

(2) Implementing this in managed code is actually risky because the COM Interop layer actually allows for reentrancy during invocation of COM calls. This is a problem because this interfaces is expected to be input-synchronous (meaning it is not allowed to switch to another thread during the handling of the funtions in this callback). This caries the same risk of deadlock that running manauged code from loader lock has.

This solution can work, but it can also blow up in your face. So there is a risk/reward tradeoff here. Just FYI in case you find it didn't solve your problem or added some new ones.

re: Implementing IMessageFilter in an Office add-in

andreww — Tue, 17 Feb 2009 19:38:27 GMT

Richard is absolutely correct - this is a technique that can be useful in some scenarios, but that can easily go horribly wrong. That's why I started the post with a stern warning - and it is a warning that certainly bears re-iteration.

Message Hooks in Add-ins

Andrew Whitechapel — Wed, 25 Feb 2009 03:27:27 GMT

Just like my earlier post on message filters , this is an advanced scenario – so be warned: you almost

Implementing IMessageFilter in an Powerpoint add-in

mangesh — Tue, 05 May 2009 15:53:42 GMT

Hi Andrew,

I am implementing a Powerpoint 2007 VSTO 2005 SE addin and I wish to show a Progress dialog, which displays the messages related to the actions being performed in a Synchronous BackgroundWorker Thread. The background worker thread extracts information from the powerpoint application and does some processing.

However I get the exception quoted below.

"The message filter indicated that the application is busy. (Exception from HRESULT: 0x8001010A (RPC_E_SERVERCALL_RETRYLATER))"

The progress dialog ( win form) merely has a label to display the message from the background thread and a cancel button to cancel the processing in the background thread.

Do you think I should try out the approach mentioned here. Will it help me. What are the pitfalls.

re: Implementing IMessageFilter in an Office add-in

andreww — Tue, 05 May 2009 19:49:16 GMT

mangesh - the key issue is that you're doing things on another thread. Calls on the main thread simply get queued. Calls on another thread may get rejected - which is what you're seeing. If you're already catching the exception, you can simply retry the call. To re-iterate my comments about the use of message filters - I would avoid using this technique if you possibly can.