Disabling Screen Updates in Excel VBA
You can give your VBA code a performance boost by simply turning off screen updating while your code runs. This blog explains how to achieve this and how to create a basic timer to test the results.

Posted by Andrew Gould on 26 November 2015

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Disabling Screen Updates in Excel VBA

One quick and easy technique to make your code run faster in Excel VBA is to simply prevent the screen from being redrawn each time something changes.  You can achieve this by using the ScreenUpdating property of the Application object.

Creating a Simple Example

To demonstrate the principle of this technique we'll need a small example procedure that makes lots of visible changes to the Excel workbook.  You can either download the example here, or create a new blank workbook, add around five worksheets to it and then copy the code shown below into a new module.

Sub ColourInABunchOfCells()

Dim ws As Worksheet

Dim r As Range

For Each ws In Worksheets

ws.Select

For Each r In Range("A1:Z50")

r.Select

r.Interior.Color = rgbRed

Next r

Next ws

End Sub

Running the Code with Screen Updates On

Before disabling screen updates it's worth running the code with the property switched on so that you can appreciate the difference.  Head back into Excel and either press ALT + F8 on the keyboard or, from the ribbon select Developer | Macros.  On the dialog box that appears select the ColourInABunchOfCells macro and then click the Run button.

Run macro

Select the macro and then click Run.

 

You should be able to see the Excel screen flickering in the background as the macro carries out its tasks until, eventually, it finishes.

Disabling Screen Updates

The next time we run the procedure we don't want to be able to see the screen flickering in the background.  To achieve this we need to add a single line of code to the subroutine, just below the variable declarations.  It's probably also worth changing the colour that we're using so that we can see when things have changed.

Sub ColourInABunchOfCells()

Dim ws As Worksheet

Dim r As Range

'disable screen updates

Application.ScreenUpdating = False

For Each ws In Worksheets

ws.Select

For Each r In Range("A1:Z50")

r.Select

r.Interior.Color = rgbBlue

Next r

Next ws

End Sub

Now switch back to Excel, display the list of macros by pressing ALT + F8 and then run the code again.  This time you won't be able to see the macro carrying out its tasks; once the subroutine has finished the screen will update once at the end to show you the final result.

The ScreenUpdating property resets at the end of a procedure.  This means that if you run a different subroutine after the one above and you haven't added the line of code to disable screen updates to it, you'll be able to see the screen updating in the background.

Adding a Basic Timer

You may have noticed that when running the procedure with screen updates disabled the subroutine appeared to run faster.  This should certainly be the case as Excel doesn't have to continually redraw the screen each time something changes.  If we want to quantify how much time we've saved we can add some code to create a basic timer system.  The general structure of our timer is shown below:

Sub BasicTimer()

Dim StartTime As Date, EndTime As Date

Dim TimeTaken As Double

'record the start time

StartTime = Time

'do something useful here

'record the end time

EndTime = Time

'take the difference between the start and end time

'this gives the difference in units of days

'multiply the result to get the difference in seconds

TimeTaken = (EndTime - StartTime) * 24 * 60 * 60

'display the result

MsgBox TimeTaken

End Sub

This isn't the most accurate timer system you can create in VBA but it's good enough for our example.  Should you need something a little more accurate you might want to investigate the Timer function instead.  Alternatively, if you're feeling ambitious, you might consider using some Windows API functions to create incredibly accurate timers.

Adding this timer system to our colour macro should result in the following code:

Sub ColourInABunchOfCells()

Dim ws As Worksheet

Dim r As Range

Dim StartTime As Date, EndTime As Date

Dim TimeTaken As Double

StartTime = Time

Application.ScreenUpdating = False

For Each ws In Worksheets

ws.Select

For Each r In Range("A1:Z50")

r.Select

r.Interior.Color = rgbGreen

Next r

Next ws

EndTime = Time

TimeTaken = (EndTime - StartTime) * 24 * 60 * 60

MsgBox TimeTaken

End Sub

When you run the subroutine again you should now see the time taken in seconds displayed at the end of the procedure.  You should test this both with and without screen updating to determine how much faster your code runs.

Running the code with screen updates turned on takes just over 8 seconds. Running the macro with screen updates disabled takes just less than 1 second.

Your results will vary depending on your computer's specifications and a range of other factors but you should certainly see an improvement in performance when screen updates are turned off.

A More Complex Example

Although the example above demonstrates the principle of the technique, it doesn't really reflect the type of code you're likely to write in the real world.  The example below is slightly more realistic.  The basic idea behind this example is to separate a list of films into separate worksheets based on each film's genre.

Films to separate

The idea is to create a separate worksheet for each genre and copy each film into the appropriate sheet.

You can download the completed example file here and the code is shown below for your reference.

Sub SeparateFilmsByGenre()

Dim Genre As String

Dim StartTime As Date, EndTime As Date

Dim TimeTaken As Double

Dim ws As Worksheet

Application.ScreenUpdating = False

StartTime = Time

wsMovies.Select

Range("A2").Select

Do Until ActiveCell.Value = ""

Genre = ActiveCell.Offset(0, 7).Value

If Not SheetExists(Genre) Then

Worksheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = Genre

wsMovies.Range("A1").EntireRow.Copy ActiveCell

Range("A2").Select

End If

wsMovies.Select

ActiveCell.EntireRow.Copy

Worksheets(Genre).Select

ActiveCell.PasteSpecial

ActiveCell.Offset(1, 0).Select

wsMovies.Select

ActiveCell.Offset(1, 0).Select

Loop

Application.CutCopyMode = False

For Each ws In Worksheets

ws.Select

Range("A1").Select

ActiveCell.CurrentRegion.EntireColumn.AutoFit

Next ws

wsMovies.Select

EndTime = Time

TimeTaken = (EndTime - StartTime) * 24 * 60 * 60

MsgBox TimeTaken & " seconds"

End Sub

Function SheetExists(SheetName As String) As Boolean

On Error GoTo NoSheet

Sheets(SheetName).Select

SheetExists = True

Exit Function

NoSheet:

SheetExists = False

End Function

There's also a simple routine that you can run to reset the workbook back to the original state by deleting all of the genre worksheets.

Sub DeleteAllButMovies()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In Worksheets

If Not ws Is wsMovies Then ws.Delete

Next ws

End Sub

Again, to test the performance gain you should run the code with screen updates turned on and then turned off.

With screen updates turned on the procedure takes almost 7 seconds. Without screen updates the code takes just less than 2 seconds.

An Alternative Approach: Avoiding Selecting Things

Although you can save a lot of time by turning screen updates off, you can also find performance gains by writing your code in such a way that Excel needs to update the screen as little as possible.  The easiest way to achieve this to avoid selecting or activating objects such as worksheets and ranges as much as possible.  In the example code shown below the only time an object is selected is when a new worksheet is added to the workbook.

Sub SeparateFilmsAvoidingSelectingThings()

Dim Genre As String

Dim StartTime As Date, EndTime As Date

Dim TimeTaken As Double

Dim ws As Worksheet

Dim r As Range, rs As Range

StartTime = Time

Set rs = _

wsMovies.Range("A2", wsMovies.Range("A1").End(xlDown))

For Each r In rs

Genre = r.Offset(0, 7).Value

If Not SheetExists(Genre) Then

Worksheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = Genre

wsMovies.Range("A1").EntireRow.Copy ActiveCell

End If

r.EntireRow.Copy _

Worksheets(Genre).Range("A1048576")­.End(xlUp).Offset(1, 0)

Next r

Application.CutCopyMode = False

For Each ws In Worksheets

ws.Range("A1").CurrentRegion.­EntireColumn.AutoFit

Next ws

EndTime = Time

TimeTaken = (EndTime - StartTime) * 24 * 60 * 60

MsgBox TimeTaken & " seconds"

End Sub

Running this code even with screen updates turned on results in the following time:

Avoiding selection

This code runs quickly even with screen updates turned on.

 

 

This blog has 0 threads Add post