Why Macros run so slowly in Excel 2016? (Solved!)

So, I think this is one of the major, and desperate problem that Excel Lovers could face when implementing and using Macros / VBA in Excel 2016:

"Why MACROS RUN SO SLOWLY in Excel 2016?"

Well, I myself experienced this situation when I have an .xlsm file (Excel file with Macro in it), where the Macro actually performs capturing data from dozens of sheets, then compile them into one sheet to make a beautiful shiny looking dashboard.

I normally run this file in Excel 2010 since a year ago with a 2012 HP Laptop (i7 processor), with no problem and no hassle at all. Then I tried to run it in Excel 2016 with a 2017 DELL Laptop (i7 processor, Kabylake Generation), with OF COURSE a great expectation that the DELL Laptop would just definitely kill it, run the macro in perhaps 5x faster than the old HP laptop.

So I recorded the speed of the same Macro Run on both Excel versions (2010 & 2016) as below:

1. Excel 2010 (with HP Laptop i7 Processor, year 2012 model) - 13 to 14 seconds.

2. Excel 2016 (with DELL Laptop i7 Processor Kabylake Generation, year 2017 model) - 6++ MINUTES!!!

Oh My Goodness!!! I was shocked.

Why the Macro Runs VERY SLOW in Excel 2016 with the same, exact Code???

So I searched through, asked my knowledge master a.k.a Google Search Engine, and some of these articles came up:

1. https://answers.microsoft.com/en-us/office/forum/office_2016-excel/excel-2016-macros-running-slow/de5c8071-af0a-49fe-b09e-1883fd693a85

2. https://social.technet.microsoft.com/Forums/azure/en-US/597456e2-9e15-47f5-98c7-df785e6a9479/excel-2016-vba-is-very-slow-unusable?forum=Office2016ITPro

3. https://stackoverflow.com/questions/44066135/excel-macro-sometimes-incredibly-slow-in-excel-2016-but-not-in-excel-2010-high

But I see none of this works, well at least for my current situation. At this moment, I still don't understand and don't know why Macros run so slowly in Excel 2016!!

So I did further researched, and found this solution. A solution that is simple enough by just adding two lines of codes and it works for me:

Application.Calculation = xlCalculationManual 

Application.Calculation = xlCalculationAutomatic

These are codes to actually toggle on / off the Excel Calculation Processing.

So I put this line of code Application.Calculation = xlCalculationManual, at the beginning of my procedure, just a line after the sub YourProcedureName(). This is to deactivate any calculation process when the Macro Runs.

Then, I put this line of code Application.Calculation = xlCalculationAutomatic, at the end of my procedure, just a line before the end sub. This is to reactivate the calculation process when the Macro has completely run or done its job.

So generally, the codes would look like this (in red rounded circle):

And here are the stats after implementing these two lines of codes:

1. Excel 2010 (with HP Laptop i7 Processor, year 2012 model) - 13 to 14 seconds.

2. Excel 2016 (with DELL Laptop i7 Processor Kabylake Generation, year 2017 model) - 3 seconds!!!

WOW, what an amazing result! And YES, this is what I actually expected initially.

WELL, I guess one of the differences of how Macros run in Excel 2010 and Excel 2016 is when a macro writes something to a cell, then Excel 2016 would just do the recalculation process as that is how Excel normally reacts after a user type in anything within a cell.

So Guys, let me know what you think in the comments below, hope this solution can also help you in fixing and solving the problem of "Why Macros run so slowly in Excel 2016!!".