Forum Discussion
Macros suddenly run much slower
I noticed that excel 365 macros suddenly take much longer to run. Seems to be correlated with the latest update of excel done on february 3rd on my machine
34 Replies
It's only taken 2 years but I finally found the issue for us. We have a very large workbook for production planning that we've been using for 15 years, and around early 2022 it slowed way down, and got worse and worse. It turns out in one of the worksheets, the conditional formatting rules had been duplicated 17,000+ times.
What's strange is that no macros run to change anything on that worksheet, although they do reference data from it. I looked at a Dec 2021 copy of the workbook and the worksheet only had the original dozen or so conditional formatting rules. I found a reference on another forum that the duplication of conditional formatting rules is a recent bug in Excel - it happens when deleting/copying worksheets within a workbook (even though for us the worksheet with the problem is never deleted or copied, although others are every week).
I was unable to delete the conditional formats from the worksheet using the GUI...Excel would just crash. I was able to do it with the following code. I adjusted the loop parameters to delete 5000 conditional formats each time I ran it, took about 30 minutes to do each 5000. But workbook macros are back to normal speed and don't seem to be duplicating now.
Sub Remove_New_Formatting() Dim i As Long Sheets("<worksheet name>").Activate With ActiveSheet.Cells.FormatConditions For i = .Count To 3 Step -1 .Item(i).Delete Next i End With End SubOlivierb61 I fixed my slow macros by unchecking both "Enable multi-threaded calculation" and "Enable multi-threaded processing" in the ExcelOptions/Advanced.
My setup:
- Windows 11 Pro 64 bits Versie: 22621.1105
- PC with Intel(R) Core(TM) i7-10700 CPU @ 2.90GH and 16 Gb of Memory
- Video NVIDIA GeForce GTX 1650
- Microsoft 365 Family with latest update
I struggle with the following problem in Excel 365 (and even more problems i will post in a different message).
I made an excel-application I want to bring to the market soon. In Excel 2016 it runs like a charm, but in Microsoft 365 Excel macro's run sooooow slooowwww in Microsft 365. At the office we use Office 2016 and it runs all smooth. Does microsoft even look into this problem that exist since 2022? I don't see any progress!!
The most noticable slow downs for me when doing the following action through VBA:
- Insert, delete or move a line or column on a sheet
- Hide and unhide lines or columns on a sheet
An example from my application:
- I have a userform and underlying macro to create a pdf from a sheet
- It is a very big sheet with a lot of formulas and data
- With the userform I can select wich part, wich lines or wich Columns from the sheet wil be hidden before saving it to pdf
- Then when it hides the lines/columns it is slow, but when it unides the lines after saving the pdf it is even much slower
- In office 2016 it takes max 10 seconds to unhide the lines, save to pdf, show the lines again and close the macro; In Microsft 365 it takes more then 1 minute!
- And Yes..... I use 'application.screenupdating = false' and 'application.calculation = xlcalculationmanual' at the start of the subroutine. So the screen stays stady until the macro stopt running.
The suggestions in this Thread doent work for me!
When the problem stays like this i will go back to office 2019 of 2021, or do I even have to go to office 2016? Does Anyone know if the architecture of Microsoft 365 is differtent from the Office versions 2016,2019,2021?
PLEASE MICROSOFT SOLVE THIS!
For such issues I would typically collect the information lines on one sheet or area, then have another sheet or range where I copy cells as needed, using e.g. 'unique' or a separate numbered column to sort the information and remove empty lines, rather than actually hide lines.
I had this very problem, where my 4-year-old Windows 10 laptop would process a macro I wrote in 2.5 minutes, vs. my new Windows 11 laptop taking 40+ minutes. The specs on my new laptop are much better, which left me baffled.
After not finding a solution online, I started to compare my Windows 10 Laptop settings with the Windows 11 and what I found was the Enable VBA macros (Not recommended; potentially dangerous code can run) were selected for my Windows 10 system but not 11, so after making my change, I went from a 40+ minute run time down to 5 or so minutes. Still not as good as my old Windows 10 laptop, but a lot better than 40+ minutes.
For those that are unsure where to find the setting navigate to the following:
File >> Options >> Trust Center Settings... >> Macro Settings >> Select the Enable VBA Macros (not recommended; potentially dangerous code can run) radio button >> OK >> OK
All Done!
Sounds good. I hope this works.
Would enable this option put the security at risk?
Patrick
If you're running your own macros, not really. It can be if you're running someone else's off the internet and don't understand what is running in your Excel. Also, scan attachments before downloading via email. Some of these can contain VBA.
- The VBA codes have been pretty much optimized. We try very much to optimize the macros for many users in the company. It suddenly became several times slower than before. I timed the macros to compare the speed. When I repeated running the macro several times, it took longer and longer time every time repeating the macro. 50sec, 60sec, 70sec...2 mins
- We have the same issue in my company, Waiting for a solution from MS Excel team
Have you put the folder in the Trusted Location (Developper Tab -> Macro security) ?
Olivierb61 I have experienced in similar situation. Prior to running Macro in Excel (2021 & Office 365), my 16G DDR4 RAM would more than suffice. After I have started running Macro (simple ones too), RAM usage suddenly spiked to 100%, and that caused my PC to run extremely slowly, which I was not expecting with a Ryzen 9 CPU & RTX306 GPU. So, I simply upgraded to 32G DDR4 RAM, and it did not help much, which is ridiculous. My MB can support up to 128G DDR4 RAM, but I am not going to spend a few hundred dollars to upgrade to 128G RAM, just so that I can run Excel Macro. I doubt the 128G RAM would help the situation anyways. MS should make it clear the size of RAM is needed to run Macro in Excel without slowing down PC speed.
I found that disabling the Hardware Graphic Acceleration solved this problem for me. Olivierb61
I've run into the same issue. Macros that used to take ~5 minutes to complete now take hours. I've instrumented my code and it appears that the slowdown is primarily related to copying data between cells. Turning off autocalculation has not yielded improvement.
- Please check the comment from CliveW85 in the post below, this has solved the problem for many users:
https://techcommunity.microsoft.com/t5/excel/excel-macro-runs-painfully-slow/m-p/3180940/emcs_t/S2h8ZW1haWx8dG9waWNfc3Vic2NyaXB0aW9ufEtaUkRaNzhDWjVIRTZEfDMxODA5NDB8U1VCU0NSSVBUSU9OU3xoSw#M134966Thanks: To confirm, the fix for Slow macros/ Slow exit from Excel once slow macros with Excel 365 have run suggested...
(by @CliveW85 in Post: https://techcommunity.microsoft.com/t5/excel/excel-macro-runs-painfully-slow/m-p/3180940/emcs_t/S2h8... )
....did work for me 100% (as per my previous entry under that Topic, posted: Feb 17 2022 01:33 PM)
CliveW85’s fix that works is as follows:
To restore normal ‘at-speed’ Excel 365 macro running again, even with most current Windows 365 versions of Excel – ‘Add the directories of the Excel file(s) with the macro(s) within as Excel Trusted Locations’.
To do this:
From the Excel ribbon, follow this path:
Developer > Macro Security >Trusted Location(s) and then add those new trusted locations that contain your Excel files - e.g., for me: I added “V:\Datatree\ExcelData\WORK\” and its subdirectories.
((*) If the Developer function is not available in the top ribbon of your Excel 365, add it via Excel>Options>Customise Ribbon> then select Developer, OK.)
(To be certain of a fix, not only did I add the directories which contained the Excel files(s) that contained the macro(s) within, but also the directories for any locations where my macros accessed data from. e.g., for me - F:\Datatree\CLP Project\ and its subdirectories.).)
After these actions, macros then run at a good speed once more, and exiting from Excel after running the macros is restored to being swift, too.
It would seem that up until recent (2022) updates to Microsoft 365 (Excel 365), listing Trusted Locations within Excel>Developer was not an essential requirement for running Macros at a reasonable speed, and now it is.
I had this same problem. Something has clearly changed on MS's side. Some macros which used to take around 30 secs were taking anywhere from 20 mins to 2.5 hours. This was a sudden change early last week. However, we've found a fix. Once we disabled automatic formula recalculation during execution of the macro, everything went back to normal. If anything, faster than before.
At the beginning of each function:
Application.Calculation = xlCalculationManualAt the end of each function:
Application.Calculation = xlCalculationAutomaticOr, a better way, if you want to preserve the user settings of automatic vs manual calculation following completion of the macro:
Sub x() lCalc = Application.Calculation With Application .Calculation = xlCalculationManual .ScreenUpdating = False .DisplayAlerts = False ''///your code here .Calculation = lCalc .ScreenUpdating = True .DisplayAlerts = True End With End Sub(Credit to user royUK on https://www.ozgrid.com/ forum for that addition and code snippet)
timfrewin I have zero idea how whoever figured this out but I just got a brand new computer and my macros were running soooo slow. Put in these two code snippets and boom, fixed! Thank you so much!
i read this post and applied the solution to my macro, as result took less than 1 sec to execute despite 15-20 sec without the command described.
this is the place vhere i applied it:
at the beginning
Public Sub name_of_my_macro()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual...all the code
and at the end
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End SubThank you so much, you saved me
