Speeding up Excel macros

By default macros are run with Excel being fully responsive and displays everything as the process goes along. It also listens to events and recalculates everything if you have the default of calculation as automatic. Disabling all those things for the duration of the macro results in a truly huge improvement on performance. We are talking seconds versus minutes in execution time depending on what the macro does.

pStarter

Here is a small routine that switches off Excel interactivity. Call this in the beginning of your main code and see – or more to the point – don’t see but notice the drop in execution duration.

The optional arguments allow you to inform the user on the status bar and leave on select responsiveness. However in most cases you just call pStarter with no arguments.

pStopper

Then the “bad” news. Because you have now made Excel non-responsive you must reverse the situation at the end of your main code. Here is the procedure for that.

The optional bEnd argument set to False will allow the code to continue after restoring responsiveness. This is sometimes useful if your code is a combination of multiple individual routines that are called separately depending on circumstances.

Should your main code exit at any point before reaching the pStopper function then your Excel session will remain unresponsive. So write the payload in a way that calls pStopper regardless of when it terminates. Especially remember to do that for error trapping.

Also note that if your macro runs for more than a few seconds then Excel will show (Not responding) in the title bar. This can be easily avoided by adding the simple command DoEvents within the Do…Loop, For…Next or While…Wend that is taking its sweet time to finish. You can use the pSBar routine to tell the user what is happening and as a bonus it runs DoEvents to keep Excel responsive.

Updated on February 12, 2017 at 10:34 am EEST

Leave a Reply