0

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?

Epiquin
  • 23
  • 7

1 Answers1

0

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:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • There is contradicting statements in the resources you cited, your explanation and the experience I've referred to in my OP. In the first link - "DoEvents takes notice and performs whatever tasks are required to handle what you want done. **When all user input has been service, the macro continues happily on its way."** Also as I mentioned the macro does not depend on data that can be changed by the user. – Epiquin Feb 26 '18 at 14:46