HonedLogic.com
How to Speed Up Excel VBA Macros
If you have written Excel VBA macros that take longer to run than you would like then you can speed them up easily.By default, Excel updates the workbook automatically every time the contents of a cell is changed and refreshes the display. Therefore, if your code makes changes to lots of cells then this can significantly slow down execution.
Fortunately, you can programmatically change the workbook calculation option to Manual at the start of your code and then switch it back to Automatic at the end of your macro. In addition, you can turn off screen updating at the start of your code and then switch it back on again once your code has run.Here are two simple subroutines which can be called to do just that:
A point worth noting is that if your code crashes after calling speedUp() but before calling slowDown() then Excel will still be in manual calculation mode which will be problematic for end users. If they need to change it back to automatic, then this can be achieved by navigating to the Excel options for formulas and changing the Workbook Calculation setting.
