Please forgive me if the following is blindingly obvious, or has been published here before, but I think it might be worth pointing out.
If you are using a worksheet with lots of UDFs, you may well find that it runs a bit slowly and decide to change to manual calculation so that it does not try to re-calculate between every change you make. Doing so allows you to make a whole bunch of changes then do one calculation, instead of calculating after each step.
OK - so far so obvious. I've done this for years. But I've just come across a couple of tips to make sure that when you do initiate the calculation, it runs VERY much faster.
Tip 1 - simply ensure that the VBA code window is closed before running the calculation. This makes a huge difference to calculation time.
Tip 2 - Do NOT recalculate using F9. Instead, add a button and include the code Application.calculate behind it. This also can have an enormous impact on calculation speed.
I just did a quick test with a UDF in every row of a single column and the difference in time between the case where I followed both of the above tips and where I ignored both was a factor of ~500. i.e. 1 second compared to 8 minutes!
I found these tips at
I'd recommend giving the site a quick visit.
Tony
If you are using a worksheet with lots of UDFs, you may well find that it runs a bit slowly and decide to change to manual calculation so that it does not try to re-calculate between every change you make. Doing so allows you to make a whole bunch of changes then do one calculation, instead of calculating after each step.
OK - so far so obvious. I've done this for years. But I've just come across a couple of tips to make sure that when you do initiate the calculation, it runs VERY much faster.
Tip 1 - simply ensure that the VBA code window is closed before running the calculation. This makes a huge difference to calculation time.
Tip 2 - Do NOT recalculate using F9. Instead, add a button and include the code Application.calculate behind it. This also can have an enormous impact on calculation speed.
I just did a quick test with a UDF in every row of a single column and the difference in time between the case where I followed both of the above tips and where I ignored both was a factor of ~500. i.e. 1 second compared to 8 minutes!
I found these tips at
I'd recommend giving the site a quick visit.
Tony
