Jaa

Implementing IMessageFilter in an Office add-in

  • Artikkeli

First a warning: this is an advanced scenario, and you should not attempt to use this technique unless you’re sure you know what you’re doing. The reason for this warning is that while the technique described here is pretty simple, it’s also easy to get wrong in ways that could interfere significantly with the host application.

Problem description: you build an Office add-in that periodically makes calls back into the host object model. Sometimes the calls will fail, because the host is busy doing other things. Perhaps it is recalculating the worksheet; or (most commonly), perhaps it is showing a modal dialog and waiting for user input before it can continue.

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. Then, it will be processed in sequence. This is the normal case, and it is recommended that this is how you design your Office solutions in most scenarios – that is, without creating any new threads.

However, if you do create additional threads, and attempt to make OM calls on any of those threads, then the calls will simply fail if the host is blocked. You’ll get a COMException, typically something like this: System.Runtime.InteropServices.COMException, Exception from HRESULT: 0x800AC472.

To fix this, you could implement IMessageFilter in your add-in, and register the message filter on your additional thread. If you do this, and Excel is busy when you make a call on that thread, then COM will call back on your implementation of IMessageFilter.RetryRejectedCall. This gives you an opportunity to handle the failed call – either by retrying it, and/or by taking some other mitigating action, such as displaying a message box to tell the user to close any open dialogs if they want your operation to continue.

Note that there are 2 IMessageFilter interfaces commonly defined. One is in System.Windows.Forms – you don’t want that one. Instead, you want the one defined in objidl.h, which you’ll need to import like this:

[StructLayout(LayoutKind.Sequential, Pack = 4)]

public struct INTERFACEINFO

{

[MarshalAs(UnmanagedType.IUnknown)]

public object punk;

public Guid iid;

public ushort wMethod;

}

[ComImport, ComConversionLoss, InterfaceType((short)1),
Guid("00000016-0000-0000-C000-000000000046")]

public interface IMessageFilter

{

[PreserveSig, MethodImpl(MethodImplOptions.InternalCall,
MethodCodeType = MethodCodeType.Runtime)]

int HandleInComingCall([In] uint dwCallType, [In] IntPtr htaskCaller,
[In] uint dwTickCount,

[In, MarshalAs(UnmanagedType.LPArray)] INTERFACEINFO[]
lpInterfaceInfo);

[PreserveSig, MethodImpl(MethodImplOptions.InternalCall,
MethodCodeType = MethodCodeType.Runtime)]

int RetryRejectedCall([In] IntPtr htaskCallee, [In] uint dwTickCount,
[In] uint dwRejectType);

[PreserveSig, MethodImpl(MethodImplOptions.InternalCall,
MethodCodeType = MethodCodeType.Runtime)]

int MessagePending([In] IntPtr htaskCallee, [In] uint dwTickCount,
[In] uint dwPendingType);

}

Then, implement this interface in your ThisAddIn class. Note that IMessageFilter is also implemented on the server (that is, in Excel, in our example), and that the IMessageFilter.HandleInComingCall call is only made on the server. The other 2 methods will be called on the client (that is, our add-in, in this example). We’ll get MessagePending calls after an application has made a COM method call and a Windows message occurs before the call has returned. The important method is RetryRejectedCall. In the implementation below, we display a message box asking the user whether or not they want to retry the operation. If they say “Yes”, we return 1, otherwise -1. COM expects the following return values from this call:

· -1: the call should be canceled. COM then returns RPC_E_CALL_REJECTED from the original method call.

· Value >= 0 and <100: the call is to be retried immediately.

· Value >= 100: COM will wait for this many milliseconds and then retry the call.

public partial class ThisAddIn : ExcelAddInMessageFilter.IMessageFilter

{

#region IMessageFilter Members

public uint HandleInComingCall(

uint dwCallType, IntPtr htaskCaller, uint dwTickCount,

INTERFACEINFO[] lpInterfaceInfo)

{

return 1;

}

public uint RetryRejectedCall(
IntPtr htaskCallee, uint dwTickCount, uint dwRejectType)

{

int retVal = -1;

Debug.WriteLine("RetryRejectedCall");

if (MessageBox.Show("retry?", "Alert", MessageBoxButtons.YesNo)

== DialogResult.Yes)

{

retVal = 1;

}

return retVal;

}

public uint MessagePending(
IntPtr htaskCallee, uint dwTickCount, uint dwPendingType)

{

Debug.WriteLine("MessagePending");

return 1;

}

#endregion

}

Finally, register your message filter with COM, using CoRegisterMessageFilter. Message filters are per-thread, so you must register the filter on the background thread that you create to make the OM call. In the example below, the add-in provides a method InvokeAsyncCallToExcel, which will be invoked from a Ribbon Button. In this method, we create a new thread and make sure this is an STA thread. In my example, the thread procedure, RegisterFilter, does the work of registering the filter – and it then sleeps for 3 seconds to give the user a chance to do something that will block – such as pop up a dialog in Excel. This is clearly just for demo purposes, so that you can see what happens when Excel blocks just before a background thread call is made. The CallExcel method makes the call on Excel’s OM.

public partial class ThisAddIn : ExcelAddInMessageFilter.IMessageFilter

{

#region IMessageFilter Members

…

#endregion

[DllImport("ole32.dll")]

static extern int CoRegisterMessageFilter(

IMessageFilter lpMessageFilter,
out IMessageFilter lplpMessageFilter);

private IMessageFilter oldMessageFilter;

internal void InvokeAsyncCallToExcel()

{

Thread t = new Thread(this.RegisterFilter);

t.SetApartmentState(ApartmentState.STA);

t.Start();

}

private void RegisterFilter()

{

CoRegisterMessageFilter(this, out oldMessageFilter);

Thread.Sleep(3000);

CallExcel();

}

private void CallExcel()

{

try

{

this.Application.ActiveCell.Value2 =
DateTime.Now.ToShortTimeString();

}

catch (Exception ex)

{

Debug.WriteLine(ex.ToString());

}

}

}

 

ExcelAddInMessageFilter.zip

Comments

  • Anonymous
    November 19, 2008
    PingBack from http://mstechnews.info/2008/11/implementing-imessagefilter-in-an-office-add-in/
  • Anonymous
    November 26, 2008
    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()Regardscode 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();}
  • Anonymous
    December 01, 2008
    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.
  • Anonymous
    December 01, 2008
    The comment has been removed
  • Anonymous
    December 03, 2008
    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.
  • Anonymous
    December 18, 2008
    The comment has been removed
  • Anonymous
    December 26, 2008
    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.
  • Anonymous
    December 26, 2008
    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).
  • Anonymous
    February 17, 2009
    The comment has been removed
  • Anonymous
    February 17, 2009
    The comment has been removed
  • Anonymous
    February 24, 2009
    Just like my earlier post on message filters , this is an advanced scenario – so be warned: you almost
  • Anonymous
    May 05, 2009
    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.
  • Anonymous
    May 05, 2009
    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.