VBA Tip – DoEvents

Say you’re running a macro that’s hogging your PC and you’re unable to do anything else while it’s running. You can’t even break into the code to check it’s running OK. Throw in “DoEvents” every now and then and the code will briefly pause while Excel checks what else is going on, e.g. you pressing the Escape key! These pauses are barely noticeable in real time but can spare the user much aggravation.

E.g. change this:
For i = 1 To 1000
…do stuff
Next

to this:
For i = 1 To 1000
…do stuff
If i Mod 10 = 0 Then DoEvents
Next

In this example the code will briefly pause on every 10th iteration, while Excel checks for things like key presses. The “Mod” condition allows you to control how often it checks. If each iteration is slow then you may want to check more frequently, in which case you would replace 10 with a smaller number and vice versa.