Скачать или смотреть How to Fix Your VBA Excel Macro That Takes Too Long to Run

Discover why your VBA Excel macro is slow and learn how to optimize it for better performance in this comprehensive guide.
---
This video is based on the question https://stackoverflow.com/q/64707224/ asked by the user 'Jason' ( https://stackoverflow.com/u/13112853/ ) and on the answer https://stackoverflow.com/a/64720453/ provided by the user 'Ricardo Diaz' ( https://stackoverflow.com/u/1521579/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: VBA Excel Macro takes VERY long time to run

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Troubleshooting Your VBA Excel Macro's Performance

Are you frustrated because your once-efficient VBA Excel macro has suddenly slowed down? Many users suffer the same issue – a macro that once completed in seconds now takes minutes, or even longer, to finish. Let’s explore the possible reasons behind this sluggishness and how you can speed it up, getting your productivity back on track.



Understanding the Problem

When a macro becomes slow, it could be due to several factors:

Data Size: As your data grows, your macro might take longer to process.

Complex Calculations: More complex calculations within the macro can hurt performance.

Screen Updating and Calculations: If Excel is constantly updating the screen or recalculating formulas, it slows down.

The macro you’ve initially recorded is designed for formatting a sheet, adjusting pivot tables, and printing. However, you may need to refine it to enhance performance.



Optimizing Your Macro

Here's a step-by-step guide on how to refactor your existing VBA code to make it run more efficiently.

1. Apply Basic Error Handling

Adding error handling to your code helps you identify issues and manage unexpected behaviors elegantly.

[[See Video to Reveal this Text or Code Snippet]]

2. Turn Off Unnecessary Features

Before running the main body of your macro, it is advantageous to turn off features like ScreenUpdating and Automatic Calculation:

[[See Video to Reveal this Text or Code Snippet]]

3. Define and Reference Objects

Instead of using ActiveSheet or other implicit references, define your target workbook, worksheet, and pivot table explicitly. This reduces confusion and enhances readability.

[[See Video to Reveal this Text or Code Snippet]]

4. Clear Filters Efficiently

Clearing slicers and filters should also be methodical to ensure all filters are removed efficiently.

[[See Video to Reveal this Text or Code Snippet]]

5. Optimize Pivot Table Adjustments

Pivot tables can be time-consuming to adjust. Instead of making repeated calls within your loop, consider batching operations together:

[[See Video to Reveal this Text or Code Snippet]]

6. Streamline Print Operations

Because you're printing frequently, consider how you can reduce the number of print calls. Loop through selections efficiently and clear slicers once the loop is completed:

[[See Video to Reveal this Text or Code Snippet]]

7. Restore Settings

To ensure your Excel returns to its normal operational mode, remember to re-enable screen updating and automatic calculation at the end of your macro:

[[See Video to Reveal this Text or Code Snippet]]

Complete Refactored Code Example

Below is how a complete, optimized macro looks following these suggestions:

[[See Video to Reveal this Text or Code Snippet]]



Conclusion

By taking these steps to optimize your VBA Excel macros, you can reduce their run time significantly and make them more manageable. Always remember to create a backup before running any critical changes and test your code for any potential issues. If formatting or printing is still problematic, consider checking printer communication settings to mitigate delays.

Now that your macro is lighter and faster, you'll have more time to focus on other essential tasks. Happy coding!