Excel vba Application.screenupdating vs Application.visible

Question

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?

houssam houssam · Accepted Answer · 2015-05-24T10:19:18

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

Excel vba Application.screenupdating vs Application.visible

3 Answers