listi.net

Listi.net

             


Optimizing Excel with Recalculation

One way to optimize or speed up a spreadsheet is to turn off and turn back on calculations.

The best way to turn them off and on is to

dim iRecalc as long

iRecalc = Application.Calculation 'Save current calc setting

Application.Calculation = xlCalculationManual ' turns off recalc
....code

Application.Calculation = iRecalc ' restore it


Often this must be done carefully as you can easily introduce bugs. That is why it is very important to have some way to test if your changes are causing any new bugs.

You must weigh the time saved versus the problem of making development more difficult.

The first thing to look for in your VBA code are "=  and [=.  This 2 strings is where most of the issues can happen. After finding these, anywhere there is a copy or cut of this data when calcs are off will cause a problem. Insert:

Application.Calculate

right before the copy to make sure you are copying the values after the calcuation. Note that Sort is another place where you should include Application.Calculate, as you'll probably want to sort after recalculating.

Selection.AutoFill ... is another place you may want to add a recalculate.