When writing VBA code that does calculations based on worksheet data and then writes data back to the worksheet you might be waiting longer for a result than needed. Even more so if there are formulas in that worksheet.
There are or course more ways to optimize the calculation time. Obviously the alghoritm of your code has to be as efficiënt as possible. The functions you choose, the order in which things are processed and how they are processed etc.
But apart from good coding practices this method shows a very easy and fast way to cut down on the processing time by implementing 4 simple lines of code.
How it works
Most worksheets will have the setting automatic calculation enabled. This means that whenever data on the worksheet is changing Excel will automatically recalculate all formulas and conditional formatting. This behaviour can be set in the Excel configuration but as said, usually it will be set on automatic. In VBA we can easily disable this behaviour temporarily by setting it to manual.
When your VBA code writes to the Excel sheet it will take extra time for the writing itself. The VBA code waits until writing has been finished and the display has been refreshed before it moves on the next instruction. We can switch this behaviour temporarily off in VBA.
Code
Sub CalcAndWriteToWorksheet() 'Set worksheet calculations to manual and switch off screen updating Application.Calculation = xlCalculationManual Application.ScreenUpdating = False '... your code goes here ... 'Set worksheet calculations back to automatic and switch on screen updating Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub
Remarks
As you can see in the example above you should not forget the turn back on automatic calculation and screen updating again.
Courtesy: vba & excel