Over the years, besides developing new bespoke Excel applications, I have been asked to speed-up existing VBA programs written in-house by various clients. I recall a request to reduce execution time of an Excel report with approx 70 PivotTables that took over 30 mins to complete!

It wasn't long before I noticed that screen updating hasn't been turned-off. The analyst was aware of the application setting, but opted not to disable it, as he wanted to observe what his code was doing while Excel was tied-up for half-an-hour. Obviously, letting Excel update the screen only slows-down macro execution and worsens the problem. A progress bar is the ideal method of giving users an indication that a macro is progressing correctly.

---

Application.ScreenUpdating Property

Turn screen updating off to speed up your macro code and avoid screen-flicker. You won't be able to see what the macro is doing, but it will run much faster! There is no need to set the property back to True when your program ends, as Excel will automatically do so. The only time this property should not be disabled is when the user needs to observe screen activity, for example, during a game. Please feel free to review the code of the 2048 game in Excel.

Application.ScreenUpdating = False 

---

Application.Calculation Property

When your VBA code changes cell values, Excel by default will recalculate all dependent cells. This behaviour can cause a workbook to recalculate several times during macro execution, which will degrade performance significantly. I have witnessed large models hang-up hopelessly or even crash Excel when macros are run with the xlCalculationAutomatic setting. Excel's automatic recalculation can be disabled by setting it to manual. Macros may execute in seconds or a few minutes, instead of hours.

Application.Calculation = xlCalculationManual 

A word of caution: Excel won't update values in cells while calculation is manual. If you code relies on updated cell values, a Calculate event must be forced with the range, workbook or application  .Calculate or Application.CalculateFull method.

If the Worksheet.EnableCalculation property is set to False, Excel will exclude the worksheet in any recalculation request by Sheet.Calculate, Application.Calculate, Application.CalculateFull and Application.CalculateFullRebuild. The .EnableCalculation property has no effect on Range.Calculate.

The Application.Calculation property must be reset by the program to its previous state before macro execution, otherwise incorrect values and conditional formats may be shown in Excel cells.

---

Application.EnableEvents Property

Events are actions performed by users which trigger Excel VBA to execute code or by actions of other VBA code. Events can be suppressed, if not required. However, performance benefits are usually marginal.

In some circumstances, it may not be desirable to disable all event handling as your application may rely on various events to run properly. So, toggle this property with caution!

Application.EnableEvents = False 

---

Avoid Selecting Objects In VBA

Unfortunately, Macro Recorder doesn't create code in an efficient format. To write code that will execute fast, work directly with fully qualified objects without using .Select method.

Option Explicit

Sub DoNotDoThis()

    Dim oSheet As Worksheet

    For Each oSheet In Worksheets
        oSheet.Activate
        Range("A1").Select
        Selection.Value = "Report"
        Selection.NumberFormat = "@"
    Next

End Sub

Sub DoThis()
    
    Dim oSheet As Worksheet
    
    Application.ScreenUpdating = False

    For Each oSheet In ActiveWorkbook.Worksheets
        
        With oSheet.Range("A1")
            .Value = "Report"
            .NumberFormat = "@"
        End With
        
    Next oSheet
    
End Sub

---

comments powered by Disqus