I have a reporting system that is generating a 5000 line report on a worksheet. It runs in two modes. The first mode fetches the data from an Access database into memory, and then loops through the memory collections to draw the output. The second mode simply draws the output, assuming the data is already in memory. Much to my surprise, mode 2 was running a lot slower than mode 1, which is unexpected because mode 1 is fetching and looping through a recordset, hitting twice as many lines of code as mode 2.
To figure out what was going on, I added a progress counter to both parts of the code. This allows me to watch the macro's percentage of completion as it chugs along. For no obvious reason, the macro running in mode 1 finished in about 6 seconds, whereas running it in mode 2 took a whopping 32 seconds. No obvious bottleneck or data differences could be discovered.
I then had a look at how the macros were being called. Mode 1 was being fired by a change event on a drop-down list control. Mode 2 is called using a macro button. I thought that might be the difference, so I tried calling both modes from a temporary macro, and suddenly I'm seeing slow performance in mode 1! I tried a few variations, but found that it wasn't how the macro is being called, but a subtle difference in the code between the two routines.
My drop-down control's event code had the line: Application.Cursor = xlWait at the top, followed by the call to the report macro, and finally: Application.Cursor = xlDefault. I put this in originally because I thought the extra step of fetching data might cause a delay in the macro, and showing an hourglass reassures my users that something is happening.
When I added these lines to the button click event for mode 2 of the macro, I observed the dramatic speed improvement I was looking for. I'm already suppressing calculation and screen updating, which makes a big difference on any code that draws to a spreadsheet, but I had no idea that changing the mouse cursor to an hourglass could give a further 400% speed boost! I will now go back and use this technique on all the models I've created that have lengthy macro processes.
Let me know if you have any other obscure tricks like this - I'm always on the lookout for more macro speed, and I know Excel has many more undiscovered secrets in store for us all.
Posts
