Forum Discussion
Who are the Moderators? Anybody read this? VBA Macros are Broke, Formulas up 30% Slower.
Microsoft Professional Plus 2016
Microsoft Excel 2016 MSO (16.0.12026.20344) 64-bit
(Monthly Channel)
Same results of both AMD and Intel CPU's. Other users in this forum reporting same issues with VBA broken.
VBA isn't broken at all, it is Excel itself which handles a couple of things differently.
Quite impactful has been the switch from the Multiple Document Interface (MDI, Excel 2010 and older) to a Single Document Interface (SDI, Excel 2013 and later). This has caused mayor changes in the way Excel treats the ribbon and the workbook windows. If VBA code involves multiple workbooks and switching of windows and worksheets it tends to be slower than before. Rigorously scrutinizing your code for selects and activates should help there.
Another huge difference is the time it takes to unprotect and protect worksheets. If you use a password, protecting sheets is about 2000 times slower than before Excel 2013. This is because they have upgraded the hash algorithm that masks the password in the file. The way around that slowness is either not using a password (this is most effective as it makes 2013 and 2016 as fast as 2010 and sheet password protection is insecure anyway) or doing a Protect using UserInterfaceOnly when first needed and after that no longer touching protection in your VBA.
JKPieterseYes, it is broken, since the end of last month, on multiple systems, multiple brands of CPU's, for multiple people for which I can personally attest. There are others in this Excel forum with VBA issues within the last week. Also the same is true for slower formulas (again) now. I'm sorry you can't reproduce the results but happy you are not affected by whatever happened. Some change in Excel updates has caused issues with VBA.
Even without VBA code inserted at all, Excel is up to 30% slower on the same formulas. Excel IS broken (again). I'll eventually prove it outright. I use different formulas and vlookups a lot. A few months ago, this same type formula could take up to 30-45 minutes depending on number of rows. The fact that it went down to 74 seconds calc time several months ago, by some update, was marvelous, and now that Excel has increased calculation time again, it is, disappointing.
- Which types of VBA issues are you experiencing precisely? Is it with one particular file, multiple files, do the files have something in common? I'd like to learn more and report it to MSFT.
- This issue is with the with the calculation timer code. In the firs MS link below there are two pieces of code. The second link is my sheet, that is producing the error, where I was using them to 1st: Calculate Formula Time and 2nd: Refresh the average run times from multiple runs. It was working up until the near the end of October, 2019. Now when I load the sheet it expels the VBA code from the sheet and gives error. I've rebuilt the sheet re-attaching the VBA code into a module and it does the exact same thing. Now this error appears every time. Title: Microsoft Visual Basic for Applications: "Can't find project or library"
https://docs.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calcuation-performance
https://intensewebs.com/index.php/benchmark/real-world-cpu-performance-checker-excel