Whenever you have a task to automate, you can usually go down the
And usually when you write your code, you’re just trying to get things to work.
You know how it goes. Write some code, test, then fix the bugs. Maybe later you
add some features here-and-there, making your VBA code more powerful. But after
a while you notice your code is beginning to get very slow when it runs. It’s
not as fast as it used to be and it can begin to get pretty frustrating waiting
a long time for your code to finish. If this sounds like you,
Application.ScreenUpdating might be your
Setting the Stage
Let’s start with an easy example. We’ll take the range
A1:P30 and loop through
the entire range a total of 30 times. There will be a counter to tell us how
many times we’ve “touched” a cell.
Every time we move to the next cell, we will:
- Put the value of the counter in the cell
Check the counter to see if it is even
- If it is even, we’ll highlight the cell white and make the text black
- If not, we’ll highlight the cell red and make the text white
- Once we reach the last cell in
A1:P30we will offset the counter by 1 to alternate which cells have even digits and which have odd ones. We do this because we want to cause as many screen updates as we can to showcase the difference turning off
We’ll also want to keep track of how long this takes, so we’ll need to add some code in for that as well.
Public Sub AddContentToSheet() Dim startTime As Double startTime = Timer Dim r As Excel.Range Set r = ActiveSheet.Range("A1:P30") Dim i As Long Dim repeat As Long Dim cell As Excel.Range For repeat = 1 To 30 For Each cell In r cell.Value = i If (cell.Value Mod 2 = 0) Then ' if i is even, color the cell white cell.Interior.Color = vbWhite cell.Font.Color = vbBlack Else ' else, color the cell light gray cell.Interior.Color = RGB(240, 240, 240) cell.Font.Color = vbWhite End If i = i + 1 Next cell ' offset i to alternate column coloring i = i + 1 Next repeat MsgBox "Total time was: " & (Timer - startTime) End Sub
The more we mess with the screen updates (like changing font size, bold and italics, etc.) the more work we do for the screen to process and show what it’s doing.
Go ahead and run this code. For my PC, it took about 7 seconds to run.
It felt like forever.
Before moving on, let’s get a better understanding of why this is taking so long. I mentioned that the issue is that the screen is constantly updating, which is causing the code to run slowly. If you think about it, if there were less of the screen to process, it should run faster, right?
Try this out: shrink your Excel workbook window to a smaller size and run the code again. Then go even smaller and run it again. Do this a few more times.
Notice a difference?
The code runs much faster when there’s less real estate to
update on your screen. Now it should make sense why it’s a good idea to turn off
Application.ScreenUpdating. So let’s see what happens when we do that.
Turn off Application.ScreenUpdating
Application.ScreenUpdating is a setting within Excel that - when turned on -
will visibly update the Excel worksheet on your screen any time there is a
change that happens within the worksheet.
Application.ScreenUpdating = True
(meaning, it’s turned on) is the default.
Let’s try the code again, except this time we will turn off
Application.ScreenUpdating so we won’t refresh the screen constantly until the
work is completed:
Public Sub AddContentToSheet() Application.ScreenUpdating = False Dim startTime As Double startTime = Timer Dim r As Excel.Range Set r = ActiveSheet.Range("A1:P30") Dim i As Long Dim repeat As Long Dim cell As Excel.Range For repeat = 1 To 30 For Each cell In r cell.Value = i If (cell.Value Mod 2 = 0) Then ' if i is even, color the cell white cell.Interior.Color = vbWhite cell.Font.Color = vbBlack Else ' else, color the cell light gray cell.Interior.Color = RGB(240, 240, 240) cell.Font.Color = vbWhite End If i = i + 1 Next cell ' offset i to alternate column coloring i = i + 1 Next repeat MsgBox "Total time was: " & (Timer - startTime) Application.ScreenUpdating = True End Sub
Now the code runs at 1.4 seconds for me, which is a huge improvement.
I realize this is a somewhat silly example, but all I’m trying to illustrate is
that when you have a macro that is constantly updating the screen when it’s not
necessary, you could save yourself a lot of time by turning off
Also, don’t forget to set
Application.ScreenUpdating = True at the end of your
macro. If you leave it set to
False, then your users might think that the
worksheet is broken. This is because when you have auto-calculated values (like
formulas), they won’t be updated unless you perform a “Calculate Sheet” by using
F9 (or in the Formulas group in the ribbon).
One last note, if you have a sub procedure that calls more sub procedures and
you have a deep chain of these calls, make sure to use
Application.ScreenUpdating in the outer-most sub procedure that’s running.
Otherwise you may inadvertently turn
Application.ScreenUpdating back on a
little too early.