One of my biggest annoyances using Excel VBA automation is all of the execution time spent waiting for Excel automatic formula calculation to complete.  I figured that if I could turn off the Excel VBA automatic calculation somehow, I could greatly speed up my script execution time.  I was able to achieve this using the Application.Calculation function to set it to manual and handle the calculations myself.  Wow, did it speed up my execution time.  And it only took 3 lines of Excel VBA code.

I often have multiple sheets that are referenced to each other using Excel formulas.  While executing VBA, excel decides to calculate, using the Excel automatic calculation feature.  This just gets in the way most of the time.  I know my script well enough to know when it should calculate and not, and avoid calculating a sheet a bunch of times just because Excel formulas in several sheets reference it.

So to handle the calculations yourself, here is a general principle to follow.

' Disable automatic calculation
Application.Calculation = xlCalculationManual
' do regular operation here
' Force a calculation
Application.Calculate
' Then remember to run automatic calculations back on
Application.Calculation = xlCalculationAutomatic

That's really all it takes.