VBA Blogs: Stop Reticulating Splines - Part 2

Last week we discussed how our macro is “Reticulating Splines” on run-time. Let's discuss another tip we could use to mitigate it.

Stop Screen Flickering

You might be “Calibrating Fame Indicant” which may require a lot of movements between sheets whether in one work book or many.

Each time Excel copies data from another worksheet in VBA, it snaps to the worksheet to copy the selection on the screen and snaps to the destination worksheet to paste it. A macro with a lot of copying around despite no scroll movement on the screen appear to "flicker" a lot as it jumps around. It flashes as it moves to different parts as the macro runs and can be very distracting.

But even updating the screen to show this movement requires memory! Not only is Excel using memory to calculate the procedure, it's also reserving part of it to update the changes on the screen as it progresses through.

We can turn this off in order for Excel to concentrate purely on the calculations. This is done with the Application.ScreenUpdating property.

It's pretty simple - at the start of the routine, just set this to False and at the end set it back to True and we're golden!