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

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 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 speeddown 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 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

Download

Please let me know your doubts via comments

Recommended Excel VBA books

Excel 2016 Power Programming with VBA

Excel VBA 24-Hour Trainer

Excel VBA: A Beginners’ Guide

One Comment

Leave a Reply