Forum Discussion
Excel Macro Runs *PAINFULLY* Slow
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.
- What if the files are stored on SharePoint?
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.
All suggestions about trusted locations and sharepoint are basically valid however the bottom line is that MS did something that slowed VBA and we, the users, shouldn't have to make changes to our VBA code because of changes MS make to Windows. I really get fed up with MS in many areas where users are expected to change things to suite MS when in reality the users should be allowed to change if they want, not be forced to make changes.
- This is very helpful!
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.
- What if the files are stored on SharePoint?
