7

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.

Improve this question
7
  • 2
    You could implement an event in your COM object and have it call back when done. See dailydoseofexcel.com/archives/2006/10/09/async-xmlhttp-calls for an example of how to run a COM object asynchronously. Commented Apr 21, 2011 at 20:09
  • @Tim: Great link (and great dirty trick, the real explanation is down in the comments). Why don't you post this as an answer ? Commented Apr 21, 2011 at 20:20
  • Re: UPDATE, I would think twice (or more!) about using DoEvents. It can cause more problems than it solves, especially when you don't own the message pump. Commented Apr 22, 2011 at 0:15
  • @jdigital: actually DoEvents seems exactly what I want here, I just have to make sure that there is no way another call to DoEvents can be processed in between (hence the cheap computeActive lock). Commented Apr 22, 2011 at 0:18
  • 1
    @AlexandreC: you must be reading the documentation for DoEvents, in which case your comment is true. I'm trying to provide you a heads up that in the real world, DoEvents can often cause mysterious problems (even in scenarios much simpler than this one). Go ahead and give it a try, but if you run into mysterious hangs or strange behavior, then you might want to rethink this solution. Commented Apr 22, 2011 at 0:23

3 Answers 3

Reset to default
2

If you want to do this well you need to give up on VBA and write a COM add-in.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I love how all VBA question threads have a "don't use VBA" answer!
@David: We tried VSTO, but we found it difficult to deploy, we did not like the shared add ins handling in office 2007 (no load-on-demand ? And wtf is this "auto disabling" feature when the thing goes wrong once ?) and we must also be Office 2003 compatible. Add in express is expensive, and probably overkill. Just one COM object and some VBA plumbing is fine, only we want some UI responsiveness.
Have you looked at XL-DNA or XLL++?
To achieve backward capability in .NET you can reference the 2003 XL library and then change everything to objects after you're done writing it. You can also do this method devcity.net/Articles/163/1/… coupled with xl-dna it works pretty well.
1

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.

Improve this answer

Comments

0

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
Improve this answer

1 Comment

The second option does. Not the first one.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.