Who are the Moderators? Anybody read this? VBA Macros are Broke, Formulas up 30% Slower.

[RAINFIRE]

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 .

Real World CPU Performance Checker (Excel)

https://intensewebs.com/index.php/benchmark/real-world-cpu-performance-checker-excel

[#2]

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.

[#3]

@RAINFIRE 

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?

[#4]

@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."

[#5]

What precisely is your excel version? Build number, subscription type, are you on insider? Which update schedule,...

[#6]

@Jan Karel Pieterse 

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.

[#7]

@RAINFIREP.S. Full Licensed version - not O365.

[#8]

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.

[#9]

@Jan Karel PieterseYes, 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.

[#10]

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.

[#11]

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

[#12]

@RAINFIRE I doubt if it is your issue, but the API declaration in the #Else part is wrong. It reads:

    Private Declare Function getFrequency Lib "kernel32" Alias _                                            "QueryPerformanceFrequency" (cyFrequency As Currency) As Long

(all on a single line), it should read:

    Private Declare Function getFrequency Lib "kernel32" Alias _
        "QueryPerformanceFrequency" (cyFrequency As Currency) As Long

(on two lines).

Any references marked as "MISSING"? Tools, References in the VBE

[#13]

Same problem for me with Office 365 Excel version 1910 !

Previously the macro was taking 5s to complete now it starts, CPU usage get to 30-40% and Excel windows is "not responding" and it takes about 30s-45s to complete.

Had to explain to a user why his brand new laptop (i7 8Gb and SSD) was slower than his 4 years old previous one ...

[#14]

@Jan Karel Pieterse 

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?

[#15]

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.

[#16]

@RAINFIRE

I can't duplicate your VBA expulsion problem on any of my systems or Excel versions using your code. What error message do you get? (it sounds like an anti-virus/security/Trust Centre problem or Windows blocking a downloaded file?)

I can duplicate the COUNTIF slowdown in Office 365 Excel Insider Fast. Cutting down to 50K COUNTIFS I get 67 seconds with Excel 2013 and 705 seconds in Insider Fast Excel on my Surface Pro 3.

With your permission I will forward your benchmark problem to the Excel team responsible for Insider Fast.

[#17]

@Charles WilliamsI'll look into the trust center problem. I guess it could be, but nothing has changed except for the updates mentioned further. I don't use any other anti-virus other than Windows Defender and it has been getting it's updates on a regular basis on all the machines I use, along with the Windows and Office updates. The only error I get is mentioned in the earlier reply after the code is already removed. That code that is from that Microsoft link mentioned; only those two pieces of VBA code from that article, nothing else. Also, I don't use the laptop much and nothing has changed on it other the updates. Both are running separately licensed FULL copies of MS Office 2016 Pro Plus. It has the same two issues. I've completely rebuilt the sheet from scratch once and the new sheet still had the two new issues. Also, I'm only using the first two pieces of code for the CalcTImer from the MS link.

Btw, I believe you wrote the VBA code for the CalcTimer? I just want to say thank you very much. It was working beautifully and perfectly until the end of October, 2019.

As far as the the second, separate issue of formula speed . . .those numbers recorded inside the real world benchmark sheet are from when it was working correctly. Now on my AMD machine it's 30%-40% slower and Intel laptop 10%-20%slower. That is in a sheet with or without the VBA code.

Yes, you may use and distribute the sheet freely. Inside the Readme and on my webpage for it you should find free, MIT license, to do with as you wish.

[#18]

@RAINFIRE 

Thanks: I will forward it & let you know if there is any news.

Definitely sounds like something screwy with the disappearing VBA.

And on the COUNTIF formula speed I suspect your slowdown may be a separate issue to the one I saw with insider fast because I got a much bigger slowdown, and the slowdown difference between AMD and Intel makes me wonder if it its not actually Excel related but something to do with those weird microcode attacks that Intel & AMD were trying to disable (if so it will be hard to prove!)

[#19]

@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"

[#20]

@Jan Karel PieterseYes. 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