Forum Discussion
Who are the Moderators? Anybody read this? VBA Macros are Broke, Formulas up 30% Slower.
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.
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.
