application.screenupdating

Application.ScreenUpdating can dramatically speed up your code

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 off Application.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.

this is taking 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.

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!