Application.ScreenUpdating can dramatically speed up your code
February 08, 2015
Whenever you have a task to automate, you can usually go down the
VBA route.
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,
then Application.ScreenUpdating
might be your
answer.
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:P30
we 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 offApplication.ScreenUpdating
can make.
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
Application.ScreenUpdating
.
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.