CODEWITHSUNDEEP
performanceexcelvba
Aditya Guru
Aditya Guru

Reputation: 712

Excel vba Application.screenupdating vs Application.visible

I have a vba code that copies certain cells from another excel sheet to active excel sheet. I want the work to complete faster.

So does setting Application.ScreenUpdating to false speed up the task better than changing Application.screenupdating/Application.visible to hidden or do both?

Upvotes: 2

Views: 6818

Answers (3)

Eric Phiri
Eric Phiri

Reputation: 41

I ran a timer on various combinations of the settings. Here's what I found: Note: All measurements are in milliseconds, but absolute numbers not important*. It's the relative changes I'd like to point out. Feel free to turn into percentages or ratios. (*I favour GetTickCount/GetTickFrequency Win API functions, but it doesn't matter what you choose to use)

Individual Settings:

  • Baseline w/ Everything On: 62,182.910
  • Everything On (but Visible=False): 60,366.117
  • Everything On (but DisplayAlerts=False): 59,639.975
  • Everything On (but ScreenUpdating=False): 58,257.545
  • Everything On (but Calculation=Manual): 42,605.444
  • Everything On (but EnableEvents=False): 17,127.370

Combination of Settings:

  • Baseline w/ Everything Off: 8,118.695
  • Everything Off (but DisplayAlerts=True): 8,802.461
  • Everything Off (but Visible=True): 8,965.122
  • Everything Off (but ScreenUpdating=True): 9,912.257
  • Everything Off (but Calculation=Automatic): 15,931.297
  • Everything Off (but EnableEvents=True): 41,896.063

In short:

  • Biggest individual speed-boost comes from EnableEvents=False
  • Second biggest individual speed-boost comes from Calculation=Manual
  • Third biggest individual speed-boost comes from ScreenUpdating=False
  • Even bigger boost comes from combining these three settings

Notable Mentions:

  • Application.Visible=False alone has no significant effect (assuming, you're not chasing sub-1 second optimisations... If you are, are you sure VBA is the best tool for the job?). (Personally, I use Application.Visible where Excel is executing a long-running/fragile process and I don't want the user to interfere by touching the sheet)
  • DisplayAlerts=False alone has no significant effect, but it's job is to reduce the chance of your process being interrupted. (I use this for the same reason as hiding the sheet - it helps avoid interference. If nothing happens that's likely to trigger an alert, this setting will not contribute anything significant. That's unless you're chasing millisecond optimisations.)

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149325

Which one(/both) of Application.screenupdating/Application.visible should I set as false?

Please provide reasons for your answers.

It really depends on what you want to do. Let me explain.

Purpose

Application.visible is used when you do not want the user to see the application. Whether the application is updating or not is immaterial. For example you may have a Login Userform displayed and you do not want to show the application till the user enters correct information..

Application.screenupdating on the other hand has nothing to do with the visibility of the Application per se. It is there to (in simple words) prevent the flicker that you see when the application is being updated.

Which one should I use?

  1. When the Screenupdating is False, and Application.visible is True then the flicker doesn't happen
  2. When the Screenupdating is True, and Application.visible is False then the flicker happens but you cannot see it as the application is hidden.
  3. When the Screenupdating is True, and Application.visible is True then the flicker happens and you can see it.
  4. When the Screenupdating is False, and Application.visible is False then the flicker doesn't happen.

So if you are worried about performance(speed of execution of code) then it is always advisable to switch Screenupdating to False.

Upvotes: 3

houssam
houssam

Reputation: 1873

I have no direct answer to your question; but I think that setting Application.visible to false will not enhance the performance; I prefer to use the following code:

Public Sub YK_Start()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub

Public Sub YK_End()
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

usage example:

Sub LoopExample()
    Dim Cell As Range
    Call YK_Start
        Columns("B:F").ClearContents
        For Each Cell In Range("A1:A100000")
            Cell.Offset(, 1) = Cell.Value + 1
            Cell.Offset(, 2) = Cell.Value + 2
            Cell.Offset(, 3) = Cell.Value + 3
            Cell.Offset(, 4) = Cell.Value + 4
            Cell.Offset(, 5) = Cell.Value + 5
        Next Cell
    Call YK_End
End Sub

This source code will be executed within18 seconds without using Call YK_Start and Call YK_End; and It will be executed within 10 seconds using these procedures.

Reference: www.officena.net : An Arabic Office Forum.
EDIT #1
There are many ways to measure the execution time of your code; I don't know the most accurate one; I only need approximate value; See:
How do you test running time of VBA code?
I am using the simplest one:

Sub my_test()
Dim t As Single
t = Timer
'code
Call LoopExample
MsgBox Timer - t
End Sub

Upvotes: 5

Related Questions