How to Speed Up a Slow Excel 2021 VBA Macro
Question details
The user needs to improve the execution speed of a VBA macro that opens workbooks and extracts data.
- Product
- Excel 2021
- Device & OS
- not provided
- Scenario
- Running a VBA process to systematically open multiple workbooks and extract required data.
- Observed behavior
- The macro runs significantly slower in Excel 2021 compared to older versions due to background processes like screen refreshing, automatic calculations, events, and alerts.
Ensure you have a backup copy of your Excel workbook before modifying the VBA code, and familiarize yourself with the VBA Editor (ALT + F11) where you will insert the optimization script.
Optimize VBA Code Settings to Enhance Macro Speed
Use this solution to drastically reduce macro execution time by temporarily turning off resource-heavy application settings like screen updating and automatic calculations.
Excel 2021 manages screen rendering and automatic calculations differently than older versions, which can heavily impact VBA performance when opening files or scraping data. Disabling these background processes while the macro runs is the most effective way to speed it up.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor, and locate the module containing your slow macro.
At the very beginning of your macro subroutine, declare a variable to store the current calculation state by typing: Dim calcState As XlCalculation, followed on the next line by calcState = Application.Calculation.
Right after storing the calculation state, add the following lines to disable background processes: Application.ScreenUpdating = False, Application.Calculation = xlCalculationManual, Application.EnableEvents = False, and Application.DisplayAlerts = False.
At the very end of your macro, before the End Sub statement, restore the original settings by adding: Application.ScreenUpdating = True, Application.Calculation = calcState, Application.EnableEvents = True, and Application.DisplayAlerts = True.
Try WPS Office for Fast and Seamless Spreadsheet Processing
If you are experiencing performance issues or heavy resource usage with Excel 2021, consider switching to WPS Office. WPS is a free, lightweight alternative that offers an intuitive interface and excellent performance for complex data processing tasks.
- 1. Visit the Official Website: Navigate to the official WPS Office website to access the latest version.
- 2. Download the Software: Click the Free Download button to get the lightweight installation package.
- 3. Install and Launch: Follow the on-screen instructions to install WPS Office, then open your existing spreadsheets to enjoy faster processing.
Frequently Asked Questions
Why do macros run slower in newer versions of Excel?
Newer versions of Excel, like Excel 2021, introduce enhanced graphical interfaces, stricter security checks, and more complex calculation engines. These background processes demand more memory and CPU usage, causing macros that frequently interact with the UI or recalculate cells to run slower than in older versions.
What happens if my macro crashes before restoring the Application settings?
If the macro crashes and does not execute the lines that turn screen updating and automatic calculation back on, Excel will appear frozen and formulas will not update automatically. To fix this manually, you must open the Immediate Window (CTRL + G) in the VBA Editor and type Application.ScreenUpdating = True and Application.Calculation = xlCalculationAutomatic.
Does disabling display alerts automatically click 'Yes' on prompts?
Yes, when you set Application.DisplayAlerts = False, Excel automatically chooses the default response for any prompt that would normally appear. For most destructive actions, like closing a workbook without saving or deleting a sheet, the default response allows the macro to proceed without user interruption.
