VBA Coding Patterns: Improving VBA Performance

In this article, we will look at how to improve VBA performance.

In the previous article, we compared the VBA Range Loop method and confirmed that the method using Variant Array is the fastest.

VBA Coding Pattern: Range Loop - Read

---

1. Performance related VBA global variables

Here is a summary of all variables.

Examine the contents of each variable.

1.1. Application. ScreenUpdating

It is a variable that controls whether the screen is updated or not, and the default value is True (screen is updated).

• Performance improvement by setting not to refresh the screen (file conversion, sheet conversion, cell value setting, etc.) during VBA code execution
• Effective when there are many screen refreshes
• Set it to False at the entry of the code and set it back to True at the end of the code.

1.2. Application. DisplayAlerts

A variable that controls whether the warning window is displayed or not, and the default value is True (alert window is displayed).

• Prevents the code from stopping in the middle by not displaying warning windows (e.g., “Do you want to save” when trying to close a file that has been changed) during VBA code execution
• Set it to False at the entry of the code and set it back to True at the end of the code.

1.3. Application. Calculation

This is a variable that controls the formula calculation method, and the default value is xlCalculationAutomatic (automatic calculation).

• Prevent automatic calculation of formulas (COUNTIF, VLOOKUP, etc.) during VBA code execution
• Effective for many formulas
• Set it to xlCalculationManual at the entry of the code and set it back to xlCalculationAutomatic at the end of the code

1.4. Application. EnableEvents

It is a variable that controls whether to process events, and the default value is True (events are processed).

• Prevent execution of code that handles events such as Worksheet_Change when they occur
• This is effective if you have code that handles events.
• Set it to False at the entry of the code and set it back to True at the end of the code.

2. Example code

2.1. When is it appropriate to apply

If the VBA code contains the following cases, it is appropriate to apply the performance improvement method described in this article.

• When creating a new file/sheet
• When switching the current sheet to another sheet
• In case of continuously changing the currently selected cell within one sheet
• When performing complex calculations such as long calculations with many formulas

In this case, the execution of the VBA code is temporarily suspended, and events that require Excel, such as updating the screen or calculating formulas, are processed. The most typical example of slow VBA code is screen refresh. You can improve performance by temporarily suspending other event processing while the VBA code is running, and processing other events after the VBA code finishes executing.

2.2. example code

Before entering the code to be processed, the global variable is set to a performance improvement value, and after the processing code is finished, it is changed to the original value. Please refer to the following code.

'코드 진입 부분
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
 
'실행할 코드가 들어 갈 부분
...
 
'코드 종료 부분
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True

---

Above, we looked at VBA coding patterns for performance improvement. If you have any questions, please leave a comment.