Re: COMException (0x800AC472) thrown periodically (and/or randomly)

[Govert van Drimmelen]

Hi JC,

The VBA_E_IGNORE error indicates the Excel is busy and that the object
model has been suspended. It's a very tricky case to deal with, and
not documented well. I learn a bit more every year.

Your Windows.Forms.Timer handler probably always runs on the main
thread (you should confirm by tracing out the ManagedThreadId), but
that's not quite enough. In some circumstances Excel is servicing the
main thread message loop (hence your timer code runs), but the object
model is still suspended.

The latest release (v0.30) of Excel-DNA includes a safe handler that
should ensure that your COM automation code runs only when Excel is
ready. The mechanism is exposed as follows:
* You call ExcelDna.Integration.ExcelAsyncUtil.Initialize() from your
AutoOpen() handler.
* When you want to enqueue work to be run in a safe context, either
call ExcelAsyncUtil.QueueMacro("MyMacro") or
ExcelAscynUtil.QueueAsMacro(myDelegate).
The outcome should be that your code runs in a context that never
exhibits the VBA_E_IGNORE error.

So instead of the time tick event calling myTaskEvent directly, it
would call something like

Public Sub TimerHandler(....)
ExcelAsyncUtil.QueueAsMacro("myTaskEvent")
End Sub

With

Public *Shared* Sub myTaskEvent() ...
(It needs to be Shared if in a class, or in a public module, so that
Excel-DNA will register it as a macro)


As long as you are accessing the ExcelDnaUil.Application from the main
thread, only, it doesn't really matter whether you keep a reference in
a global variable, or just call ExcelDnaUtil.Application every time
(I'd not use the variable, myself).

ExcelDnaUtil.Application can get 'broken' if your code breaks it -
e.g. it calls Marshal.FinalReleaseComObject(...) on it. But then it
will be completely broken and stay that way, and certainly not give
you the VBA_E_IGNORE error (you'll get something about the Runtime
Callable Wrapper being detached.)


I suggest you try the ExcelAsyncUtil approach, and let us know whether
that resolves your issue.

Regards,
Govert


* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/

* * * * * * * * * * * * * * * * * * * * * * * * * * * *




On May 11, 5:43 am, JC <w2jc...@gmail.com> wrote:
> Hi Govert and the forum,
>
> This post seems lengthy, indeed it's asking only one question: how to get
> the root Excel Application object to avoid the following annoying
> exception. :)
>
> System.Runtime.InteropServices.COMException (0x800AC472): Exception from
> HRESULT: 0x800AC472
>    at
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateSet(Object
> o, Type& objType, String name, Object[] args, String[] paramnames, Boolean
> OptimisticSet, CallType UseCallType)
>    at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSet(Object
> Instance, Type Type, String MemberName, Object[] Arguments, String[]
> ArgumentNames, Type[] TypeArguments, Boolean OptimisticSet, Boolean
> RValueBase, CallType CallType)
>    at
> Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateSetComplex(Object
> Instance, Type Type, String MemberName, Object[] Arguments, String[]
> ArgumentNames, Type[] TypeArguments, Boolean OptimisticSet, Boolean
> RValueBase)
>
> where 0x800AC472 is defined as:
> *const uint VBA_E_IGNORE = 0x800AC472;*
>
> =====================================================
>
> My move of a VBA project to ExcelDna is almost done and currently is being
> tested. The COMException (0x800AC472) was thrown very often from various
> spots throughout the code, although some particular lines are more likely
> to throw this exception.
>
> I tried different ways to avoid it and researched the internet on the fix.
> One of the post I found is talking similar issue as mine, and answered by
> you. :)
> *
> *
> Excel interop COM exception while running in backgroundhttp://stackoverflow.com/questions/9515380/excel-interop-com-exceptio...
>
> It seems the causes is that the code was not getting the root Excel
> Application properly (from another thread?). However, I reviewed my code
> and not sure if it matches the case. My code basically run through the
> following steps, and it is a typical type of COM AddIn.
>
>     The following major objects are defined as global:
>
>     Public xlApp As Object = Nothing
>     Public MyWorkbook As Workbook = Nothing
>     Public goTimer As System.Windows.Forms.Timer
>
>     Within the AutoOpen() of my ComAddinLoder (implements IExcelAddIn):
>
>     xlApp = ExcelDnaUtil.Application
>     MyWorkbook = xlApp.Workbooks("myTest.xls")
>
>     Then start the timer to schedule a call periodically:
>
>     AddHandler goTimer.Tick, AddressOf myTaskEvent
>     goTimer.Interval = 120 * 1000       ' run myTaskEvent every 2 minutes
>     goTimer.Enabled = True
>
>    The myTaskEvent() calls the entry point myTask() to do all the basic
> things there, such as
>
>    ' read value from a cell, do something
>    sValue = MyWorkbook.Sheets(x).Cells(y, z).value     ' COMException thrown
>
>    ' set value to a cell after doing something
>    MyWorkbook.Sheets(x).Cells(y, z).value = sValue     ' COMException thrown
>
>    ' turn off/on screen updating
>    xlApp.ScreenUpdating = False   ' here the xlApp =
> ExcelDnaUtil.Application, since it's a global variable
>
>    ' check some VBA object status
>    sBuffer = xlApp.Run("checkVBAObject")     ' almost always throw
> COMException !
>    ' FIX: I created a wrapper for checkVBAObject on one sheet,
> which effectively avoid this COMException.
>    sBuffer = MyWorkbook.Sheets(1).checkVBAObject()      ' good, no
> COMException thrown, but why??
>
> Note these code are typical ones within myTask() scheduled by the goTimer
> (which is a Forms.Timer, from another thread?)
> All of them could randomly throw notorious COMException (0x800AC472), and
> then ruin the rest of entire myTask()
>
> It seems even Application.Run() could cause the Exception in my case. I'm
> really wondering where the root cause is. :(
>
> Here are something I came up:
> *#1* - *Govert*, you mentioned that using Application.run() ensure it's
> called from Excel's main thread, do you mean ExcelDna's xlApp.Run("mySub")
> where mySub is defined as a VBA macro, or Application.Run("myExcelDnaSub")
> from VBA, where it's calling a Sub exposed by ExcelDna?
> *#2* - although xlApp is global, and assigned with ExcelDnaUtil.Application
> at the beginning, will it somehow get out-of-dated?
>        should I always use ExcelDnaUtil.Application instead of global xlApp
> ?
> *#3* - my code references this global xlApp from one Sub invoked by
> Forms.Timer, is it the case that Excel is called from outside of its main
> thread?
> *#4* - is it possible to query Excel main thread to see if it's busy or not
> before my code requests Excel to do something?
>
> Thanks a lot!
>
> JC

[Naju Mancheril]

I'm not sure about the VB.NET code, since I'm more familiar with C#. It looks reasonable.

Just want to point that it may be slightly more robust to avoid a periodic timer and use one-shot scheduling per-iteration. That way, even if your Excel code takes longer than your period (more than two minutes, maybe due to some bug), you won't see an unbounded queue of work build up. The basic issue is that the timer has no way of telling whether it's work was "really done" before it schedules the next task event.

Here is a one-shot scheduling solution using a ThreadPool worker. You can also use a one-shot timer per iteration, which may be slightly more efficient (won't burn a thread stack while you wait two minutes).

    public static void StartTimer() {
      ScheduleNext();
    }

    private static void ScheduleNext() {
      ThreadPool.QueueUserWorkItem(_ => {
        Thread.Sleep(TimeSpan.FromMinutes(2));
        ExcelAsyncUtil.QueueAsMacro(() => MyTaskWrapper());
      });
    }

    private static void MyTaskWrapper() {
      MyTask();
      ScheduleNext();
    }

    private static void MyTask() {
      // crazy stuff
    }

This implementation will wait for each MyTask call to finish and schedule the next iteration two minutes *from then*.

[Govert van Drimmelen]

Hi JC,

I'm glad the ExcelAsyncUtil is working well for you!

* ExcelAsyncUtil.Run and ExcelAsyncUtil.QueueMacro are very different,
but share some low-level implementation. The Run(...) is about making
an asynchronous user-defined worksheet function, and uses Excel's real-
time data (RTD) feature and a thread-safe RTD Server component.
QueueMacro / QueueAsMacro is about getting code to run code in a safe
macro context, possibly initiated from another thread or from inside a
worksheet function. Both of these use a hidden message window and some
synchronization under the covers.

* Your VB.NET looks perfect to me with
ExcelAsyncUtil.QueueAsMacro(AddressOf myTask).

* Naju makes a very good point about being careful that your work does
not pile up if the individual tasks take longer then your two minute
interval (or if Excel is 'busy' for an extended period of time). You
might be able to test the latter case by entering formula edit mode
and walking away for a while. Your code won't run while during formula
editing, so many tasks will queue up until Excel is 'Ready', then all
of them will run one after the other. It might not be a big issue in
your case, but is worth at least giving a thought to.

Regards,
Govert

[w2j...@gmail.com]

Hi Govert and Naju,

Thanks for you guys reply and idea! I'm now pretty satisfied with QueueAsMacro().

What Naju mentioned is exactly what I was worried, saying the chance to pile up many myTask() in the queue. My quick and dirty solution is to use a global counter and trace how many myTask() is queued, while myTask() decreases the counter when it's done. The benefit is that I can control how many it can be piled up and not enqueue the next myTask().

Naju's suggestion is quite neat that it ensures the same interval between two myTask() calls. Thanks Naju.

I now noticed that COM interop seems noticeably slower than native VBA, and it even maximizes CPU (along with explorer.exe, not sure why explorer.exe always consumes over 60% CPU during the time), I'm planning to port some heavy interop code to C API. Maybe I shall start another post for this, and update you guys with any progress or issue.

Thank you!

JC

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/D8s6IwXVW-U/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.