19

I have been playing around measuring code execution times to gauge differences between executing my scripts locally and on my server. At one point I forgot to disable screen updating and was thankful I'm not sensitive to flashing lights before thinking about it in more detail:

When I first started using VBA I always assumed it was just used so that it didn't scare end users into thinking their PC was about to crash. When I started reading more into improving the efficiency of your code I understood what it was for but how much of an effect does screen updating really have on your codes execution time?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Alistair Weir
  • 1,809
  • 6
  • 26
  • 47
  • 4
    Depending on what your code is doing, setting `Application.Calculation` to "manual" will often also give you a big speed boost. You'd typically do both for a long-running macro. Don't forget to reset calculation though - it's a persistent setting. – Tim Williams Sep 12 '12 at 16:24
  • 1
    Aslo, with running `Screenupdating` turned off you can let the user know the progress via `Application.Statusbar = `. Then set to `False` to return it to normal. Some people need to see progress otherwise they think their computer has frozen and force close everything. – Rory Sep 13 '12 at 02:41
  • I have also read that the default data format in Excel is actually Currency, so it is best to use Currency as a data value instead of Double when possible to avoid data conversion. – Rick Henderson Feb 23 '16 at 03:06
  • 2
    Just a side note: you can put `DoEvents` in your code every so often to keep your excel from freezing up. I do something like `if n mod 5000 then DoEvents` in my loops to keep Excel responding, but also prevent slow down. It seems to work well... – bmende Apr 21 '16 at 03:01

5 Answers5

34

Turning off screen updating will only make a difference to execution time if the code interacts with Excel in a way that causes changes to the screen content. The greater the amount of screen changes the bigger the impact will be. The other posted answers aptly demonstrate this.

Other application settings that can make a difference to execution time are Calculation and Event handling. Use this code template as a starting point (the error handler ensures that these properties are turned back on at the end of the sub, even if it errors)

Sub YourSub()
    On Error GoTo EH

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    ' Code here

CleanUp:
    On Error Resume Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
Exit Sub
EH:
    ' Do error handling
    Resume CleanUp
End Sub

Other techniques exist that can provide even greater improvement in execution speed.

The most useful include

  1. Avoid Select, Activate and ActiveCell/Sheet/Workbook as much as possible. Instead declare and assign variables and reference those.
  2. When referencing large ranges, copy the Range data to a variant array for processing and copy the result back to the range after.
  3. Use Range.SpecialCells, Range.Find and Range.AutoFilter to limit the number of cells referenced.

There are plenty of examples of these techniques on SO.

brianpck
  • 8,084
  • 1
  • 22
  • 33
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • How does declaring workbook vs activeworkbook speed up code? – FreeSoftwareServers Jan 23 '20 at 18:59
  • @FreeSoftwareServers speed wise, primarily because code the uses `Active*` (or `Select` or `Selection`) involves extra unnecassary steps interacting with Excel, which all take time. For _your_ use case: run some tests! – chris neilsen Jan 23 '20 at 19:14
8

If you want to see a fairly drastic example of why ScreenUpdating is important, run the following code. It takes roughly 45 times longer in Excel 2011 for me to run this swap without ScreenUpdating = false! This is a huge difference in time.

Sub testScreenUpdating()

    Dim i As Integer
    Dim numbSwitches As Integer
    Dim results As String

    'swap between sheets this number of times
    numbSwitches = 1000

    'keep track of time
    Dim startTime As Double
    startTime = Time

    'swap between sheets 1/2 (need both sheets or this will crash)
    For i = 1 To numbSwitches
        Sheets(1 + (i Mod 2)).Select
    Next i

    'get results
    results = "Screen Updating not disabled: " & Format(Time - startTime, "hh:mm:ss") & " seconds"
    startTime = Time

    'scenario 2 - screenupdating disabled

    Application.ScreenUpdating = False

    'swap between sheets 1/2 (need both sheets or this will crash)
    For i = 1 To numbSwitches
        Sheets(1 + (i Mod 2)).Select
    Next i

    Application.ScreenUpdating = True

    'get results for part two
    results = results & vbCrLf & "Screen Updating IS disabled: " & Format(Time - startTime, "hh:mm:ss") & " seconds"

    'show results
    MsgBox results


End Sub

Also, while we're on the topic of ways to increase efficiency, another key point is that Select, Selection, and Activate are rarely (if ever) necessary. When you record macros it will always use these but there are very few situations when you need to actually use them in code. Likewise, anything with Active in title (such as ActiveCell) normally is an indication you will have slower code because you presumably are selecting cells.

You can almost always refer to cells/worksheets specifically and avoid select. For example:

msgbox (Worksheets(1).Range("A1").value)

will work regardless of whether you are currently on the first worksheet. A common new VBA mistake is to do something more like:

Worksheets(1).Select
msgbox (Range("A1").value)

which is an unneeded step.

This adds significant time to code runtimes.

enderland
  • 13,825
  • 17
  • 98
  • 152
  • Well said. Alistair, if you'd like some excellent examples of how to avoid selecting, see Chris Nielsen's answer: http://stackoverflow.com/a/10717999/138938 – Jon Crowell Sep 12 '12 at 19:27
3

Firstly I've been using the script written by Richie (UK) Post #7 Here

It simply iterates through a loop changing the value of i in one cell. I have changed it slightly so it loops 10,000 times and I execute it 10 times for sample size.

What is the effect of screen updating on the speed of my codes execution?

These are the lengths of execution when Screen Updating was disabled and enabled:

Disabled    Enabled
0.61909653  2.105066913
0.619555829 2.106865363
0.620805767 2.106866315
0.625528325 2.102403315
0.625319976 2.0991179
0.621287448 2.105103142
0.621540236 2.101392665
0.624537531 2.106866716
0.620401789 2.109004449

As you can see it takes almost 3.5 times as long to execute the code when Screen Updating is not disabled.

Both of these codes were exceuted using the Run button in the VB editor, as opposed to 'watching' the spreadsheet.

2 simple lines at the start and end of your code:

Application.ScreenUpdating = False
Application.ScreenUpdating = True

But, they can have a large effect on the efficiency of your execution!

Note: Obviously the advantages of Screen Updating will be well known to many here but this may be of benefit to beginners and I find it interesting to view the numbers!

Alistair Weir
  • 1,809
  • 6
  • 26
  • 47
2

There is one important thing to know about screen updating which I didn’t see in any previous answer. From my own test I find out that turning screen updating off and on takes about 15ms (tested in C# via Excel Interop). Keep that on mind if you will execute anything which would take less time. And after all don’t turn screen updating on/off many times in some loop. That would be real performance killer.

And one more note (which you probably don’t want to hear) if you want it quick use C++. It is typically 5 to 10 times quicker (don’t catch me here it depends on what you really do) than VBA.

Sekory
  • 143
  • 11
1

I know it's an old thread, but:

  1. Set ScreenUpdating = true, but remember to set it back to its old value.
  2. Changing Workbooks will also reset ScreenUpdating.
feetwet
  • 3,248
  • 7
  • 46
  • 84
Frank
  • 11
  • 1