Excel VBA Optimized – Reduce Run Time
You can use this code in almost all of your macro code. This will improvise your code run time drastically.
Macro that run for minutes will get completed in seconds.
Before getting the code for this, lets see What are the options we touch upon safely?
- Switch off updates that is shown on screen: This is the major Time taking step. For every action or calculations done on Worksheets through Macro, Excel keeps displaying the changes. If we switch it off & display only the final result, it will reduce a lot of time.
- Stop Events triggers: Every time we change any cell value on a worksheet, it triggers corresponding function related to Worksheet.Change or conditional formatting etc., Switch it off.
- Stop Formula Calculations: For every change in worksheet cell value, Excel refresh all the formula calculations. if a workbook has many lookup formulas, then it will cost a lot of time. Even hours sometimes.
The above 3 are the basic & major options that is enough to optimize most of the code run time.
Here is the code snippet that is ready to use in your modules.Excel VBA code to Optimize - Speed up Run time - Reduce Execution timeVisual Basic12345678910111213141516171819202122'Increase SpeedSub Optimize_ON()'Switch of All options that takes most of the timeApplication.ScreenUpdating = FalseApplication.EnableEvents = FalseApplication.Calculation = xlCalculationManualEnd Sub'Switch off Speed improvement optionsSub Optimize_OFF()Dim sh As Worksheet'Enable Graphics, Events & AutocalculationsApplication.ScreenUpdating = TrueApplication.EnableEvents = TrueApplication.Calculation = xlCalculationAutomatic'Refresh Formula in each SheetFor Each sh In ThisWorkbook.Sheetssh.CalculateNextEnd Sub
There is a small point to be noted. If the program execution stops in middle of the code due to any run time error, then the Optimize_Off will not be called. It will result in your Excel workbook behaving weird.
In that case, add a shortcut key or a command button that invokes the Optimize_Off function. so that Excel switches back to its normal operation mode.
Previous Post: Convert Number to String in Excel VBA Macro Code
How To Speed Up Excel VBA Macro Execution Time – Optimize?
- Games VCF Countif Record Macro Timer in Excel Task Manager print screen HTML Table Import Data Thumbnails Text to Speech TTS Candy Crush Block Apps in FaceBook C++ Programing Machine learning AI bulk email Excel VBA vba color index vba color codes Android social media Twitter Google+ Google Adsense Tips bitcoin Calendar in Excel outlook vba mass email Data Mining Excel Macro Facebook WhatsApp python Wordpress