I have a set of numerically intensive routines (each takes up to 1 minute to complete) bundled in a COM object, implementing IDispatch cleanly.

I can therefore use them from an Excel worksheet, those routines will be called by VBA macros triggered by buttons.

Now, when one of these routines is called, the Excel user interface is frozen, which is quite uncomfortable for the end users of the sheet.

I'd like to find any mechanism to alleviate this problem.

This could be for instance launching the computation in another thread launched on the COM side, returning immediately, the spawned thread calling back a VBA procedure when results are computed.

Or something simpler, since I only need one computation to be performed at a time.

Now, there may be a lot of issues with calling VBA routines from other threads. I must confess that I am not that experienced with COM, that I only treat as a black box between my code and Excel (I use ATL).

So,

Is it possible to call back VBA routines from another thread ?

Is there a better way to do what I want to achieve ?

UPDATE

After weighing the options and reading a lot of stuff on the internet, I will do cooperative multithreading: in the COM object, instead of having one routine, I shall have three:

class CMyObject : ...
{
    ...

    STDMETHOD(ComputationLaunch)(...); // Spawn a thread and return immediately
    STDMETHOD(ComputationQuery)(DOUBLE* progress, BOOL* finished);
    STDMETHOD(ComputationResult)(VARIANT* out);

private:
    bool finished, progress;
    boost::mutex finished_lock, progress_lock;
    ResultObject result; // This will be marshaled to out
                         // when calling ComputationResult
};

And in the VBA:

Private computeActive as Boolean ' Poor man's lock

Public Sub Compute()

    OnError GoTo ErrHandler:

    If computeActive Then Exit Sub
    computeActive = True

    Dim o as MyObject
    call o.ComputationLaunch

    Dim finished as Boolean, progress as Double

    While Not o.ComputationQuery(progress)
        DoEvents
        ' Good place also to update a progress display
    End While

    Dim result as Variant
    result = o.ComputationResult

    ' Do Something with result (eg. display it somewhere)

    computeActive = False
    Exit Sub

ErrHandler:
    computeActive = False
    Call HandleErrors

End Sub

Indeed, by doing a depth-first-search on the internet for COM Add-Ins, I realized that VBA macros run in the same event loop as Excel's GUI, that you have the DoEvents facility, and that it is not safe (or at least very tricky) to call back VBA procedures from other threads. This would require eg. tricking the Accesibility facilities to obtain a synchronized handle to an Excel.Application object, and call the OnTime method to set up an asynchronous event handler. Not worth the trouble.

---

如果你想做得好，你需要放弃VBA并编写一个COM加载项。

---

Posting my comment as an answer...

You could implement an event in your COM object and have it call back when done. See http://www.dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls/ for an example of how to run a COM object asynchronously.

---

My dirty hack is: create a new instance of Excel, run the code there.

Another option is to schedule the run for later, have the user say when. (In the example below, I've just hard-coded 5 seconds.) This will still freeze the user interface, but at a scheduled, later time.

Sub ScheduleIt()
    Application.OnTime Now + TimeValue("00:00:05"), "DoStuff"
End Sub

Sub DoStuff()
    Dim d As Double
    Dim i As Long
    d = 1.23E+302
    For i = 1 To 10000000#
        ' This loop takes a long time (several seconds).
        d = Sqr(d)
    Next i
    MsgBox "done!"

End Sub

链接地址: http://www.djcxy.com/p/6480.html

上一篇: Node.js聊天

下一篇: 从VBA调用计算密集型例程而不拖延Excel GUI