vbaexcel

Using Excel while macros are running


There are a half-dozen answers to this. "Open a second instance" "Have a pause" Etc. I'm not looking for that.

I'm looking for the user of the workbook to be able to manipulate the workbook while the macro is running. I've seen this working before, where the user could scroll around, change tabs, even add and remove data, all while the macro was running. Unfortunately, I couldn't get permission to look at the code (And committing CFAA violations ins't my cup of tea), so I have no idea how they did it.

How can you enable a user to edit the workbook as macros are running? For a specific example, I have Conway's Game of Life running. Users select cells to flip live/dead, then can run a macro to run the entire thing. I think it'd be nice for users to be able to change cells as the macro is running. (which is a second on select macro)

Thank you


Solution

  • Sorry just reread the question. I wouldn't expect the permutation to run for very long - not long enough to interrupt really.

    But if it does, then the advice about using lots of DoEvents stands.

    The other option is that you can use the OnTime event to have a "heartbeat"

    VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds

    You can set the timer to say 3 seconds. Every time the OnTime event occurs you do one step of your permutation. In the three seconds in between they can edit.