If your macros are taking longer than they should, you’re not alone. We’ve seen processes that should complete in seconds, but instead, take minutes – and even up to 96 hours in extreme cases! With the right optimisations, we reduced those 96-hour routines to under a minute.
Why are your macros so slow?
Culprit 1: Excessive read/write operations
Every time you read from or write to an Excel sheet, it slows things down. While tricks like disabling calculations, screen updating and events help the issue, the real issue is frequent read/write operations for every single cell. This can grind your process to a halt.
Solution: Use arrays! Load your data into an array, process it in memory, and write everything back to the sheet in one go. This speeds up your process from minutes (or hours) to milliseconds.
Culprit 2: Inefficient code design
Poorly written code often involves unnecessary loops. The most common mistake is looping through one dataset for every item in another. While this might seem fine for small datasets, it becomes unmanageable with large ones, making processes much slower than they could be.
Solution: Use dictionaries! Store values that need to be accessed quickly in a dictionary, eliminating the need for multiple loops.
For more expert help with optimising your Excel macros and VBA routines, get in touch today.
