Pausing and resuming the recording of a macro is quite different to
pausing and resuming the macro while it is running, which is what you
want.
If you want your macro to pause at a specific point, the best way is
to structure it as two separate macros; you can make the second macro
activated by your "resume" keystroke.
If you want your macro to pause at an arbitrary point and resume at
some later time, you will need to work with event handlers, which gets
very complicated. It will also get extremely messy in terms of having
to make sure your macro can cope with arbitrary changes to its
environment in between any two lines. This means, for example, that
almost no variables will be of any use, since their values may be
correct when they are set but incorrect when they are used. (One
exception would be dummy variables for use with MsgBox; another would
be a variable pointing to the application or the document in which the
code is running.)
It is much better to allow pausing at a specific point or set of
points. If you only want it at one point, use two separate routines as
described above. If you want to support pausing at several points,
your best bet is to use a module-level mode variable to define the
current state. Your main routine can check the state at every
potential pause point, and you can have helper routines activated by
the "pause" and "resume" keystrokes that alter this state.
To make a macro run a specified number of times, you can call it from
a different macro with a loop. Alternatively, you can use a counter
declared as Static (this stops it being reset every time the macro is
run); to set it initially you can make the count an optional
parameter, like this:
Sub My_Macro (Optional RunTimes As Integer)
Static RunCount As Integer
If IsMissing(RunTimes) Then ' check if the macro should run
If RunCount = 0 Then Exit Sub
RunCount = RunCount - 1
Else
If RunTimes <= 0 Then
MsgBox "My_Macro called with invalid number of times to run: " & RunTimes
Exit Sub
Else
RunCount = RunTimes - 1
End If
End If |
