How to Speed up Excel VBA macro just by adding two functions?

I’m going to explain how to speed up your Excel macro just by adding two functions. I just named it as SpeedUp and SpeedDown. When you call the function SpeedUp it’ll improve the macro execution speed and when you call the function SpeedDown it’ll turn back your system to the normal state.

Below are those two function

Function speedup()

‘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

End Function

Function speeddown()

‘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

End Function

How to use this function?

You just need to add the speedup function at the first row of your Sub Procedure and add the speed down function at the last row of your subprocedure.

Sub yourprocedurename()

Call speedup

//Your code goes here

Call speeddown

End Sub

 Your code will work at least 5 times faster than the code without having these two functions.

Example

I wrote two subprocedures. The first procedure doesn’t contain these two functions and the 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 the timer.