Forum Discussion
Who are the Moderators? Anybody read this? VBA Macros are Broke, Formulas up 30% Slower.
I'm just wondering does anybody of importance read this? VBA Macros are Broke in Excel since last updates a month or less ago, Formulas are up 30%-40% Slower on AMD, 10%-20% slower on Intel. Multiple reports in this forum by different people. Basically appears to just be ignored.
Does anyone even care? Aren't you so happy all your Office Apps are in One App on the Android? Not me when you break Excel many of us really use on multiple CPU hardware. There are multiple posts in this forum about this by different people including myself and one yesterday from BillN33 .
https://intensewebs.com/index.php/benchmark/real-world-cpu-performance-checker-excel
https://intensewebs.com/index.php/benchmark/real-world-cpu-performance-checker-excel
34 Replies
Hi RAINFIRE, I am a Supportability PM at Microsoft. The details of your issue seem interesting and we would like to take a closer deeper look into it. We'd want to collect some additional information for review. However, to track this appropriately we should do this through a support incident. Have you opened a support incident with us for this issue yet? If so, can you let me know what the support incident number is? If you have not opened a support incident yet would you be willing to do so?
Thanks
Roosevelt
rsheriff Here is the open ticket number: 1000645995 . Wow, that was a fun hour just to get a ticket opened and do all the troubleshooting steps of which none worked. Please keep me advised. I think she left the ticket open?
Here is where you can download the file in question:
https://intensewebs.com/index.php/benchmark/real-world-cpu-performance-checker-excel
or directly from my OneDrive:
https://1drv.ms/u/s!Ak-VvojobmnPh7UyclYNWEKvy7TMaQ?e=owGwQa
rsheriffNo I haven't opened a ticket but yes I would very much like this to get fixed. I've done the frown face feedback option twice (that's silly). Please tell me the proper way to open a ticket so you can review it? I have a 100% legit system. Gosh I hope this is fixed before these updates reach Office365 at work which should be a few months behind. I use Excel a lot.
RAINFIRE I've exchanged some emails with people from the Excel team and they requested this:
"can you please ask the original customer to send us a frown (as I described earlier) after using the workbook and include their email? This will provide us with the necessary telemetry info. Please also ask to include their email so we can directly communicate with them too on follow-ups"
JKPieterseYes. I've done the feedback with a frown and included my email as requested. I used same message from above.
Contact: derek moore ***@ hotmail.com
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-calcuat...
https://intensewebs.com/index.php/benchmark/real-world-cpu-performance-checker-excel
- What happens if you add a new user to your machine and logon as that user, does it still fail then?
- What precisely is your excel version? Build number, subscription type, are you on insider? Which update schedule,...
I run in Office365, Excel for Office 365 MSO 16.0.12130.20232.
Created a macro in VB in 1999 (Excel 1997) that has run perfectly since, in a spreadsheet processing through 126 VLOOKUP tables in micro seconds. Until the last month or so when the macro slowed to a crawl and in a spreadsheet with a .xlsm file extension, will not run when initiated with keyboard shortcut keys.
In the August 30, 2019 KB4512941 update (OS Build 18362.329) for Windows 10 Version 1903, under "Improvements & Fixes", Non-Security update, is this statement:
"Addresses an issue that may cause the following to stop responding:
Applications that were made using Visual Basic 6 (VB6).
Macros that use Visual Basic for Applications (VBA).
Scripts or apps that use Visual Basic Scripting Edition (VBScript).
You may also receive an "Invalid procedure call” error."For some reason, this update did not install and will not install today, but issues the statement that "The update is not applicable to your computer"
What I know is that something in a Windows update messed with the operation of VB and there appears to be a fix in KB4512941 that can't be downloaded. If this update is not applicable to my computer, is there another way to get the fix?
Tip to anyone reading this thread.
Microsoft does "listen" to feedback through the File, Feedback, send a frown button in any Office app.
So if you're experiencing slow calculation on a workbook which has not been changed and worked perfectly fine some time ago, take these steps:
- Create a screen-shot of your Windows version information (hit the windows button, type "version" without the quotes and run System Information):- Insert that screenshot into a blank spreadsheet and make sure it fits on-screen and is still readable
- Click File, Feedback, Send a frown
- Enter your concern in the top box
- Check the include screenshot box (hence pasting the screenshot of system info on Excel so it is included!)
- Decide whether you want to include contact information and diagnostics and usage data
- Submit.
I do encourage everyone with any Excel issues to use this feature.
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.
RAINFIREReceived the info below from a friend. Microsoft Updates Catalog shows KB4512941 2019-08 **bleep** Update for 1903 x64 8/29/19 that purportedly contains the fix but when attempting to download it from the Catalog, generates the msg "the update is not applicable to your computer"
"No doubt, you recall the first wave of pain inflicted by the August 2019 patching regimen. Microsoft somehow managed to mess up Visual Basic (an old custom programming language), Visual Basic for Applications (for Office macros) and VBScript (a largely forgotten language primarily used inside Internet Explorer). Folks running applications in any of those languages would, on occasion, receive “invalid procedure call error” messages when using apps that had been working for decades.
Some companies’ commercial applications stopped working intermittently. More importantly, many large corporations’ internal custom programs turned belly-up.The bug affects every single version of Windows – all the way from Win7 to Win10 version 1903. I think of it as Patching as a Keystone Kops Service.
If you’ve been following the details, you know that on Aug. 16, three days after Patch Tuesday, Microsoft released fixes for the bug in:Win10 version 1709
Win7
Server 2008
Server 2008 R2
Server 2012
Server 2012 R2
Then on Saturday Aug. 17, we got fixes for:Win10 1809
Win10 1703
Win10 1607
Win10 1507
Server 2016
Server 2019
And on Monday, Aug. 19, Microsoft released a fix for:Win10 1803
As of today, Aug. 30, we still don’t have a fix for Win10 1903, the latest version of the last version of Windows. It’s not clear why, but I have a guess that Microsoft’s so wrapped up in beta testing Win10 1903 that it somehow fell through the cracks. We still don’t have the second August cumulative update for Win10 1903 – the one that’s common called “optional non-security,” with varying degrees of accuracy. And therein lies a tale."
looking at your benchmark I don't think it is the VBA that is broken (even though I wrote it over 10 years ago <vbg>), looks like you are just bench-marking the calculation speed of COUNTIFS?
Are you using Office 365 insiders or Excel 2016 MSI, and what is your Excel build number?
- I haven't heard of any problems yet, but I'll ask around. I at least have to thank you guys as this thread alarmed me to the fact that my Excel 2016 was set to only use a single processor core :-).
Apparently I've been doing some testing ages ago and forgot to restore my own settings.
