‘Disable the automatic calculation
Application.Calculation = xlCalculationManual
‘Disable the screen changes
Application.ScreenUpdating = False
‘Disable the status bar progress
Application.DisplayStatusBar = False
‘Disable all the events running at the back end
Application.EnableEvents = False
‘Enable the automatic calculation
Application.Calculation = xlCalculationAutomatic
‘Enable the screen changes
Application.ScreenUpdating = True
‘Enable the status bar progress
Application.DisplayStatusBar = True
‘Enable all the events running at the back end
Application.EnableEvents = True
How to use this function?
You just need to add the speedup function at the first row of your Sub Procedure and add the speeddown function at the last row of your subprocedure.
//Your code goes here
Your code will work at least 5 times faster than the code without having these two functions.
I wrote two sub procedures. First procedure doesn’t contain these two functions and second procedure does contain these two functions.
Below code will select the cell one by one and fill white color for 10000 Cells (100 Rows X 100 Columns) in the sheet 1 and let us determine how long will it take for this two cases using timer.
Macro without Speed Functions approximately took 14 seconds
Macro with speed Functions approximately took 2 Seconds
It speed up the macro by 7 times. It may vary from user. You can also download this Macro sheet by clicking the Download button below