Speeding up Excel macros

By default macros are run with Excel being fully responsive and displaying 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 in 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 selected 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 main routine 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 28, 2018 at 7:01 am EEST

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.