Excel Macro Runs *PAINFULLY* Slow

[Hershner04]

I have an issue running an Excel Macro. I have read a lot of threads and tried all of the suggestions but no change in performance. I have run the same file/macro on 2 machines, details below. No matter what change on Laptop 2 (problematic machine), iteration time was unchanged.

Laptop 1:
Dell Precision M4800
2015
Intel i7-4800MQ @2.70GHz, 32GB 64-bit
Windows 10 Enterprise
Version 1909
OS 18363.1734
Excel 16.0.13127.21734 64-bit

Laptop 2:
Macbook Pro 15" mid-2020
2021
Intel i9-9980HK @2.4GHz, 64GB 64-bit
Windows 10 Home
Version 20H2
OS 19042.1237
Excel 16.0.14326.20384 64-bit

Running the same file, same macro on both machines
1.5M+ calculations per iteration (cell formulas), 7 output cells
Laptop 1 = 11 seconds per iteration; Outlook, multiple Excel files open, Teams chat, Teams meeting, 3 sessions File Explorer, 2 sessions Chrome (multiple/many tabs each), has not rebooted in over a week (I know.. hard to believe); noticed slower operation of other programs, no change in performance of macro with other programs open
Laptop 2 = 24 seconds per iteration; no additional programs open or running, freezes system

Laptop 2:
Rebooted
Ran in Normal view (was not in Page Layout)
Disabled hardware acceleration
Ran with Wifi on and off
Ran clean boot
Ran diagnostic mode
Disabled all startup programs

Ran repair on 32-bit version
Uninstalled 32-bit/re-installed 64-bit Office 365

[#2]

@Olivierb61 

Thanks for your suggestions which are appreciated.

As my macros have been in existence and have been working fine and fast for 6 years until Excel 365's (/ Windows 10) recent updates, as others have concluded:

The evidence suggests that my Excel VBA code is not at fault but something in the current Excel 365 VBA handling is causing the problem with VBA speed and slow exits from Excel 365 once macros have been run.

My macros have a lot of code that I really don't want to adjust to work around this Microsoft Excel 365 problem, and I shall hope that Excel 365 can be fixed to run macros at a good pace once more in a future update, like they still do with Excel 2007 and Excel 2010. I have shifted to using these older versions of Excel on alternative Win10 PCs, meanwhile. Thanks goodness I wasn't in a rush to moving all PCs to the current Microsoft (365) offering!

[#3]

Hi,
I'd like to add that I have experiecned the same performance issue with a recent patch.
Macros that used to take a couple of mins to run are now taking hours.

Excel is eating up a lot of memory.
once the macros are complete, excel takes an age to close the file, or shut down.

setting application.calculation = xlManual has helped restore some of the performance, but the memory usage is still high and excel doesn't close down properly.

[#4]

I contacted Microsoft again, today, this time via via https://support.microsoft.com/home about this 'slow macros / slow to close Excel 365' issue. After outlining the issues (including the 4 x extra use of memory by Excel 365 for the same macros now, compared with Excel 2007), running an online 'Repair Office' remote session, the Excel 365/Macros issues persisted, no change, no fix.
However, I have now been assured that this issue will be passed to the relevant Microsoft Tech Team for analysis and a fix.

"The tech team releases updates every month, so I'd need you to be patient for the next update to be rolled out".

A fix in the next release of Office 365 should be the right solution to this current (slow) Excel365/Macros problem, I'm hoping!

[#5]

I am hoping you are right. But the fact that they do not seem to be putting VBA support into their online versions while simultaneously trying to push everyone into the online versions says to me that they want to kill macros.

I bet there is a PowerPoint somewhere at MS HQ where they have the number of people who use macros, our ages, and the rate we die on a graph.

They tried this before, and it was the birth of Libre Office.

I mean, if I am not running macros, why run the app version of office?  If I am running Office through a browser, why would I spend for Windows at all?

It will take people about 15 seconds to decide to go to Libre/Google on Ubuntu.  

[#6]

I have been experiencing the same issue with 2 machines which have been updated recently. However I think I may have found a fix, it certainly worked for me, making the macros (simple file read, extract and write to table tasks) run just like before.
I opened XL, went to the Developer tab ... Macro Security... Trusted Locations.
I then added the directory of the XL file with the macro.
Closed XL and then opened the file with the macro and it ran as fast as it used to "back in the day"
Hope that helps someone.

[#7]

This is very helpful!

[#8]

Brilliant! Thanks very much for this.

As you suggested - Add the directories of the Excel file(s) with the macro(s) within to Excel's Trusted Locations, and the macros then run at a good speed once more, AND exiting from Excel after running the macros is restored to being swift, too.

Presumably, before recent updates of Microsoft 365, listing Trusted Locations was not an essential requirement for running Excel Macros at a reasonable speed, and now it is -

Case closed for me. Thank you CliveW85 for this fix.

[#9]

If the solution is a simple as that, why haven't MS technicians not been able to fix it instead of blaming the problem on poor coding!!

[#10]

I had the intuition that is something about the macros security, but I didn't have the idea to include them in the Trusted Location, that's a brilliant solution. You really saved me @CliveW85, great idea. Thanks a lot for sharing!

[#11]

@Hershner04 

I found a clumsy work around the slow macro problem. 

First it appears that my macro runs like a champ on the first time, however if I try to run it again, it runs very slow. So what I did was created a small macro that opens my original macro and it runs beautifully.  then I added some code that actually saved and closed my real macro. Thereby throwing control back to the calling macro. When I touch the start button on the main macro, it re initializes everything and once again runs beautifully. Hope this helps.