CODEWITHSUNDEEP
excelvbadoevents
Epiquin
Epiquin

Reputation: 23

Excel/VBA DoEvents and control return

I am trying to make a macro that returns at least some partial control to the user when it runs. The macro behavior cannot be changed dangerously if the user edits cells, as there is few dependent content.

I am using this method to allow the user to occupy the execution thread and commit their cell changes before the macro gains control back and proceeds to the next statement:

Sub retCtrl(Optional ms As Long = 350)
Dim l As Long: l = Timer
Do While Timer - l < (0# + ms / 1000)
    DoEvents
Loop
End Sub

I put this after some long statements and inside loops.

It's choppy, but functional, with only one problem: if the user takes to long to edit the cell, the macro silently stops.

Can anyone explain why this happens? Is it because the timer elapses the threshold? I thought all macro execution stops when the workbook is in edit mode?

Any advice on how to handle this otherwise?

Upvotes: 0

Views: 2320

Answers (1)

ashleedawg
ashleedawg

Reputation: 21619

You can't & shouldn't return control to the user while code is running, and your issue is an example of why.

Excel expects certain elements to be in certain states, and when the user starts changing things, unexpected things can happen. For example, what would happen if the user tries to change data the same time Excel needs it? There is no multi-processing built in, "this isn't that kind of programming..."

So Excel ceases running the macro when it notices something happening. If you need to run a different process simultaneously, do it in a separate instance of Excel.

More Information:

Upvotes: 1

Related Questions