Not sure if this is common sense or not, but considering my excel macro's have gotten rather monstrous lately I started using a trick to speed things up.
' put this at the top of your code
Application.Calculation = xlManual
' put this at the end of your code
Application.Calculation = xlAutomatic
Calculate
For all my routines that manipulate formulas in my worksheets, I have seen HUGE reductions in load time from doing this. In some cases, from a couple minutes down to a few seconds. I think this trick is pretty much mandatory for any complex spreadsheet manipulations.
Just thought I'd share.
' put this at the top of your code
Application.Calculation = xlManual
' put this at the end of your code
Application.Calculation = xlAutomatic
Calculate
For all my routines that manipulate formulas in my worksheets, I have seen HUGE reductions in load time from doing this. In some cases, from a couple minutes down to a few seconds. I think this trick is pretty much mandatory for any complex spreadsheet manipulations.
Just thought I'd share.