In defense of ScreenUpdating = True

Most people who write a lot of Excel VBA code settle into a pattern of always including this line of code at the beginning of every macro of any significance.

Application.ScreenUpdating = False

This will cause Excel to stop painting the screen, which means that the Excel window will not repaint for a few seconds or minutes while the macro runs.

If you were to ask a VBA programmer why she or he does this, the response you would get would likely be along the lines of "it makes the macro run faster", or possibly "the screen jumps around too much if you let it paint". I don't think either of these reasons holds water, and to get to the real reason, you have to think back to what things were like in the early days of VBA.

VBA has been around for a very, very long time. The first version of Excel to have something resembling modern VBA was Excel 5.0, in 1993. 20 years ago, computer hardware was very different than what it is now. Even with the best optimization, most computers could not play sophisticated games or video in a window larger than a postage stamp. Windows was different back then, too: the video code prioritized flexibility and abstraction at the expense of performance. Combine these two factors, and it's easy to see why ScreenUpdating = False came into vogue: in 1993, repainting the screen was indeed an enormous performance hit, even if your macro wasn't making changes that caused big repaints.

Today, on the other hand, is very different. And I think there are good reasons to let the screen repaint: your macro won't look like it has crashed, your users will be able to see the macro doing its work, and, as a programmer, watching progress of your code on the screen can be an invaluable tool for finding out if something is going wrong or if an improvement can be made.

If you actually are hitting a performance bottleneck by leaving screen repaints on, 99% of the time, it's good indication that your macro is doing something inefficiently. Every action you take in Excel should affect as many cells as possible. You should avoid doing little changes "one at a time" and concentrate on making just a few big changes all at once.

For example, instead of setting the fill color of a column one cell at a time in a loop, get a Range object for all the cells that you need to change and set the fill color for the range, just once.

Or, instead of deleting one row at a time when the cell in a certain column matches a certain value, sort the table so that the rows to be deleted are all together, and delete them all at once. That will convert hundreds or even thousands of steps into just two. You will notice a huge speed boost, even if you compare the old macro with screen updating off to the new macro with it on.

In my opinion, the only reason to use ScreenUpdating = False is when you are manipulating a sheet with a lot of embedded shapes and charts and your macro is causing a distracting flicker. In every other situation, if you are running into a performance problem, take a careful look at your code and make sure that you're not doing something in a stupidly inefficient way.