How To Stop Screen Flickering While Running VBA Macros in Excel
You may come across times where you write a beautiful Excel macro with lots of nifty VBA code and when you go to execute the code, your screen flickers/flashes rapidly. This is caused because you more than likely have parts of your code that is effectively changing results within Excel and with Excel being a visual representation of your data, it’s going to try to show all the changes your VBA macros are doing in real time.
There’s one problem though….VBA code execution moves at a much faster pace than what your computer graphics can show. Therefore they have to meet in the middle, meaning your VBA slows down and your graphics move as fast as they can. This ultimately causes your screen to flicker.
How To Fix The Screen Flickers
So how do you fix this? Luckily, the answer is very straight forward. You prevent your screen from changing until your VBA code has finished doing its thing!
All you need to add to the beginning of your code is the following line of VBA (I usually stick it right after all my Dim statements)
'Prevent Computer Screen from running
Application.ScreenUpdating = False
'Insert your macro code here......
'Allow Computer Screen to refresh (not necessary in most cases)
Application.ScreenUpdating = True
Most coders would suggest you turn your ScreenUpdating property back on at the end of your code (so-called “Best Practice”). But technically, the screen updating automatically turns back on after your macro code has finished running. So I’ll leave it up to you to decide if you want to include the extra line of code.
Hope that helped!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
VBA Code To Calculate How Long Your Macro Takes To Run
What This VBA Code Does There are various scenarios where you may want to calculate the time it takes for...
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.