Forum Discussion
Hershner04
Sep 22, 2021Copper Contributor
Excel Macro Runs *PAINFULLY* Slow
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
- Butch716Copper Contributor
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.
- CliveW85Copper Contributor
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.- Mabandl1Copper ContributorWhat if the files are stored on SharePoint?
- BeenCodingVBATooLongCopper Contributor
Once I added the the file locations as Trusted Locations, I saw a large improvement. I did not see any changes moving them to SharePoint.
Sometimes they still run incredibly slow, and I don't know what the issue is. However, my monster programs have hit a level of maturity where I finally feel comfortable shutting off screen updating and that helps quite a bit as well.
- BeenCodingVBATooLongCopper ContributorThis is very helpful!
- Steve_PigsoftCopper Contributor
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.
- Riny_van_EekelenPlatinum Contributor
Hershner04 You mention that you run W10 on a Mac. That means you are running a virtual machine, either via Bootcamp or Parallels. Correct? Did you look at memory allocation to the virtual Windows machine? Am not a VBA person myself and know little about coding, but am running W10 on an almost 10 years old Macbook Pro (via Parallels) with far inferior specs than yours and notice no severe performance issues when comparing to more modern PCs/Laptops. Just a thought.
- Adriaan1Copper Contributor
I fully agree. The code is not the problem. I have code that I run 10 years ago on the Excel and hardware of the time. It executed in 20s. The same code on 2021 hardware and excel now takes 8 minutes to run.
This is a one sides Excel problem with their newer versions. I would really like to know why? The hardware is much better but the performance is 24 times worse...
- BeenCodingVBATooLongCopper ContributorI have the same issue. Simple code that is at least 15 years old.
It just started taking forever to run.
If I wasn't so completely trusting in the motives of the product managers, I might think it was to try to force us out of VBA like they tried to before.
- Juliano-PetrukioBronze Contributor
There are many approaches to make a VBA code faster.
Consider inittially
Sub YourRoutine() BeforeYourCodeStarts '.... '.... AfterYourCodeFinishes End Sub Sub BeforeYourCodeStarts() 'Turn off Screen Updating Application.ScreenUpdating = False 'Turn off ‘Automatic Calculations’ Application.Calculation = xlCalculationManual 'Disable Events Application.EnableEvents = False End Sub Sub AfterYourCodeFinishes() 'Turn on Screen Updating Application.ScreenUpdating = True 'Turn on ‘Automatic Calculations’ Application.Calculation = xlCalculationAutomatic 'Enable Events Application.EnableEvents = True End Sub
I also recommend you read some articles
- Hershner04Copper ContributorThank you for responding to my problem. The code is not the issue. Same file, same code on two different machines. Original machine has been consistently running 11 seconds per iteration. New machine, which by the specs is far superior, runs 24 seconds per iteration. Once I address the performance of the file, I might look to optimize the code based on my needs, but I’m not there quite yet.
- Juliano-PetrukioBronze ContributorWell, optmization means take into consideration the code as well.
Refactoring a code its painfull sometimes, but its important reassess the approach and variables, etc. to achieve the same result considering different environments.
Also consider the machine settings (Memory, processor, OS versions, etc.)
I had problems in the past where my codes was running very slow, but after refactoring the code it was improved a lot.
As I told you, it depends of a lot of things consider review the code is a part of improvement too.