Stopping Excel popup box about OLE

Feb 23, 2012 at 9:56 AM

Hi,

I've written some functions in c# which I call via exceldna. They can take quite a while to calculate and return (30-60mins). I'm not sure whether it is related, but within C# I am using the Task Parallel Library to improve performance.

I regularly get a popup box in excel which says "Microsoft Excel is waiting for another application to complete an OLE action".

How can I stop this? I've tried setting Application.DisplayAlerts=false, but this seems to only apply in the vba code it is called from (ie reverts to true once vba finishes, and I still get this problem).

 

thanks for your help,

Ben

Coordinator
Feb 23, 2012 at 11:23 AM

Hi Ben,

The message box you are seeing is from the default "COM Message Filter". It probably arises because you are calling the .NET code from VBA (you say "in the vba code it is called from"), which is a COM call that is taking long, raising that message box. 

You can override the COM Message Filter (some VBA code here: http://www.vbafin.com/vba-financial-api-coregistermessagefilter.php). But you are going down a rabbit hole if you start customizing this kind of thing.

If your calculations are taking that long, it sounds like you should not be calling them from within Excel and blocking, awaiting the result. I suspect having such long outstanding calls will cause you endless headaches. Maybe Excel should just start en external process (like a .NET console app) that does the calculations and writes the results to a file. Your Excel can then read that file later to process the results further. Or something like that.

Regards,

Govert

Feb 23, 2012 at 8:58 PM
hi Govert,
thanks for the reply.

Sorry I realise my original email was a bit misleading. I am NOT calling from VBA, I'm using a function call from excel cells to exceldna. I've tried to write a separate function in VBA to set Application.DisplayAlerts=false (and nothing else, it then returns).

No doubt at some point I will move this all to another app, but it would be useful in the interim to be able to call from excel (that's what exceldna is for right?).

I'll try the response you've posted, but as that's in VBA it sounds like it will be separate from my call to exceldna as well?
thanks
Ben


On 23 February 2012 22:23, govert <notifications@codeplex.com> wrote:

From: govert

Hi Ben,

The message box you are seeing is from the default "COM Message Filter". It probably arises because you are calling the .NET code from VBA (you say "in the vba code it is called from"), which is a COM call that is taking long, raising that message box.

You can override the COM Message Filter (some VBA code here: http://www.vbafin.com/vba-financial-api-coregistermessagefilter.php). But you are going down a rabbit hole if you start customizing this kind of thing.

If your calculations are taking that long, it sounds like you should not be calling them from within Excel and blocking, awaiting the result. I suspect having such long outstanding calls will cause you endless headaches. Maybe Excel should just start en external process (like a .NET console app) that does the calculations and writes the results to a file. Your Excel can then read that file later to process the results further. Or something like that.

Regards,

Govert

Read the full discussion online.

To add a post to this discussion, reply to this email (exceldna@discussions.codeplex.com)

To start a new discussion for this project, email exceldna@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Coordinator
Feb 23, 2012 at 9:11 PM
Edited Feb 23, 2012 at 9:12 PM

Hi Ben,

Thanks for the clarification.

Suppose you made a single UDF function in C# that just sleeps for 30 minutes, (maybe in a loop, sleeping for a minute at a time), then return a string or somethine. Such a function, exported via Excel-DNA and called from a worksheet would not show that messagebox, I think. Could you check this? If I'm wrong here, it means Excel is monitoring it's main thread even when calculations are ongoing.

So the MessageBox might arise from some code in your C# side that calls something else via COM, thus getting the COM Message Filter involved. Once you are able to isolate the calls that end up going via COM and taking a long time, you might be able to wrap the lengthy with some calls from C# to CoRegisterMessageFilter to replace the default COM Message Filter.

I guess you could completely replace the default COM Message Filter for the main Excel thread by calling CoRegisterMessageFilter in your AutoOpen, but that would probably be a very intrusive thing to do to Excel.

The right way to implement your slow calculations would be in terms of an Excel RTD server, which you call from your UDF and which will tell Excel when the new result is available so the cells can be updated.

Regards,

Govert 

Feb 23, 2012 at 9:17 PM
Hi Govert,

I think I am just calling a function in C# via ExcelDNA. I'm not aware of anything else I'm calling via COM.

Having said that I have used ExcelDNA extensively now, and I haven't seen this popup before. This is the first time I'm using the Task Parrallel Library, which makes me suspicious that it is involved somehow.

For now I'm trying adding these lines of code to the start of my c# function:

Microsoft.Office.Interop.Excel._Application xlApp = (Microsoft.Office.Interop.Excel._Application)ExcelDna.Integration.ExcelDnaUtil.Application;
xlApp.DisplayAlerts=false;

I'll let you know how successful that is.
I'll look into the RTD for future development too.
thanks
Ben


On 24 February 2012 08:11, govert <notifications@codeplex.com> wrote:

From: govert

Hi Ben,

Thanks for the clarification.

Suppose you made a single UDF function in C# that just sleeps for 30 minutes, (maybe in a loop, sleeping for a minute at a time), then return a string or somethine. Such a function, exported via Excel-DNA and called from a worksheet would not show that messagebox, I think. Could you check this? If I'm wrong here, it means Excel is monitoring it's main thread even when calculations are ongoing.

So the MessageBox might arise from some code in your C# side that calls something else via COM, thus getting the COM Message Filter involved. Once you are able to isolate the calls that end up going via COM and taking a long time, you might be able to wrap the lengthy with some calls from C# to CoRegisterMessageFilter to replace the default COM Message Filter.

I guess you could completely replace the default COM Message Filter for the main Excel thread by calling CoRegisterMessageFilter in your AutoOpen, but that would probably be a very intrusive thing to do to Excel.

The right way to implement this would be in terms of an Excel RTD server, which you call from your UDF and which will tell Excel when the new result is available so the cells can be updated.

Regards,

Govert

Read the full discussion online.

To add a post to this discussion, reply to this email (exceldna@discussions.codeplex.com)

To start a new discussion for this project, email exceldna@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com


Feb 27, 2012 at 9:37 PM

hi mcmillab

you can't stop the OLE message with DisplayAlerts.
create a message filter in c# sample (taken from NetOffice):


private static bool _messageFilterEnabled;
        public static bool EnableMessageFilter
        {
            get
            {
                return _messageFilterEnabled;
            }
            set
            {
                if ((value == true) && (IntPtr.Zero == _messageFilter))
                {
                    CoRegisterMessageFilter((IntPtr)0, ref _messageFilter);
                }
                else if ((value == false) && (IntPtr.Zero != _messageFilter))
                {
                    IntPtr filter = IntPtr.Zero;
                    CoRegisterMessageFilter(_messageFilter, ref filter);
                }
                _messageFilterEnabled = value;
            }
        }

Enable them with one call and supress the OLE message.

Sebastian
Jan 31, 2014 at 3:39 PM
Hi Sebastian,

It looks like, your solution will fix my problem. Do you've complete example?

Thanks,