How do I yield to the message pump in Excel VBA 2010?

I have an Excel macro that takes an exceedingly long time. It seems to crash Excel (I get a Not Responding message in the taskbar) when I leave it unattended, but if I put a breakpoint that gets hit every five or ten seconds, it's OK. Trouble is, I'll need to step on from the breakpoint about a quarter of a million times (we're looking at about 200 hours to execute this beast)

I'm guessing that the absence of message pump activity is antago开发者_开发技巧nising the kernel, so how might I flush the message queue? Or am I barking up the wrong tree entirely?

I'm very new to this, by the way, so documentation references will be very welcome.

Cheers, Guy

Seconding the opinion that 200h is a bit worrying, however you can try the DoEvents statement which "surrenders execution of the macro so that the operating system can process other events". (Note that calling DoEvents has an additional time penalty)

I have to agree with 0xA3, if you have an excel routine that is expected to take over 200 hours to run then you're either setting it up in a very inefficient manner or you're using the wrong tool for this job.

In any case, there are a few things you can do to optimize the routine. These will be especially helpful if you are constantly writing values to individual cells.

Sub MarathonCalc

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    ' Code to decode human genome goes here

    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

Again, if you post some of your code perhaps we can help track down why it's taking so long.

"Not responding" doesn't mean that Excel is crashing, it simply means that it does not actually respond to window messages.

Unless that is a problem there is nothing you need to worry about. I'm rather worried about your comment that you expect the task to take 200h (which is more than a week!). It seems to me that you are not using the right tool for this job. Try to find out what is taking so long and explain what you are actually doing. Then we can see how to better solve this task.

The previous answers have given you 'DoEvents' and that's the first-line fix for your problem.

However... A closer look at what's happening raises some worrying questions. Firstly, Excel's threading model is rather primitive: there's one process (according to the documentation) and no new threads are created by internally- or externally-generated events, callbacks and messages. One process, and one process ONLY, and everything else has to wait until the current VBA macro or spreadsheet calculation runs to completion.

Actually, some things can interrupt this: {esc} and {ctrl-break} (although these can be temporarily disabled), so there's at least one other thread running in Excel all the time, and you'd EXPECT the operating system to be allowed to break into a process... Wouldn't you?

The ugly reality is that the Excel application does not allow external events and messages to run to an 'event sink' while a macro or calculation is running, unless you call 'DoEvents' and some Windows Messages (note the capitalisation) still won't be dealt with.

If an undocumented number of messages and external events are left hanging around, the application will crash. Based on my own tests, I think the number is 50; there's documentation about a registry setting out there, but I'm not linking to material that I consider unreliable - I've tried the setting in question and found it to be ineffective.

So where does that leave you?

Firstly, look at your code again. Application.EnableEvents = False and Application.ScreenUpdating = False are useful settings: your next step is to find all and every read- or write operation to individual cells on the sheet and just grab arrays of cells - it's much faster to grab an array using myVBAarray=Range.Value, do your calculations in VBA, and write the VBA array variant back to Range.Value.

Secondly... You've also seen that the 'wait' state imposed by a breakpoint allows some things to sort themselves out. That sounds like an external process (a database query?) running asynchronously: could it be that the message or event which is crashing Excel is coming from that very process?