0

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

Selkie
  • 1,215
  • 1
  • 17
  • 34
  • 8
    You _might_ be able to do something like this with a bunch of `DoEvents` everywhere in your coding, especially in loops. – K.Dᴀᴠɪs Jan 15 '18 at 03:43
  • agree with above but you want to increase the efficiency of your code so that this is not a problem. –  Jan 15 '18 at 03:48
  • 3
    that is Conway's game of life ... not Conan's – jsotola Jan 15 '18 at 04:43
  • Is your code polling and waiting for something or is it actually doing some work this whole time? You might want consider moving to a different development platform for this kind of thing – Nick.Mc Jan 15 '18 at 08:29
  • Code is waiting a good chunk of time. As I mentioned, I'm running Conway's game of life, so it steps, pauses, then steps again. I'd like to give users the option of changing cells in the middle. – Selkie Jan 15 '18 at 16:23
  • @Nick.McDermaid - What platform would you suggest? My knowledge of other languages is limited, so I'm open to whatever. – Selkie Jan 15 '18 at 17:44

3 Answers3

1

Refactor your macro to use Events. In which case, you would have a series of event handlers (instead of one monolithic macro) to respond to various triggers. This is assuming that the macro is influenced by what the user is doing in the worksheet.

AJD
  • 2,400
  • 2
  • 12
  • 22
1

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Not exactly what I was looking for, but the "heartbeat" idea is close enough to a working solution, and inventive enough, that I'll take it. I previously had a bunch of "wait one second between steps" in the execution loop, but this integrates nicely – Selkie Jan 15 '18 at 23:44
0

One way of (sort of) doing this is to use a Modeless Userform (UserForm.Show vbModeless)

The user form stays visible but the VBA stops running when the form is shown and the user can then interact with Excel. Then when the user clicks a button on the form the code behind the button starts running again.

So in reality the user is either interacting with Excel or interacting with the form ...

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • So basically creating a pause button? – Selkie Jan 15 '18 at 16:47
  • No ... basically creating a GO button - VBA is paused after SHOW then the user flips cells etc then go back to the form and start a task again. You could also program the task initiated by the form to be interruptible, – Charles Williams Jan 15 '18 at 21:16