Microsoft has done me a favor. I have almost 50 macros used in portfolio evaluation. They’ve been running for years and have a good execution track record (execution history is logged). A daily portfolio assessment run with 23 macros takes about five minutes. Until Feb 4th. Execution became bog slow. I added Application.Calculation = xlCalculationManual and Application.EnableEvents = False to the run-in on every macro. Application.ScreenUpdating = False was already in all of the macros. ScreenUpdating True, Calculation Automatic and EnableEvents True at macro close. Some improvement but! My 23 macro run takes 47 minutes to complete in Win 11. Office Home and Student 2016. Lenova Yoga C740. No sign of data corruption and the macro results are all spot on. Is there a solution?
Good running macros become BOG SLOW
- ConfusedBob
- Thread is marked as Resolved.
-
-
-
It's hard to say without seeing an example of your code.
-
yes I noticed a big time slow down when i upgraded from office 2010 to 2019
I had to readjust how how my macros do things.
I converted a lot of my macros that access the spreadsheet to arrays and that did the trick for me.
I also had to adjust where I put my application.screenupdating in my code.
-
If you are calling 22 of the 23 macros from just one macro (or calling others from a macro which itself had been called) then you need to set ScreenUpdating to False, Calculation to Manual and Enable Events to False at the start of the macro that calls the first one, and reverse the settings at the end of that macro. Remove all ScreenUpdating, Calculation and EnableEvents code from all the other macros, otherwise you are turning them on and off 23 times instead of just once! That should improve the speed.
You could also try running a VBA Code Cleaning programme, there are quite a number available on the internet, Ribbon Commander is very good as it works for both 32 and 64 Bit systems, whereas most only work on 32 Bit systems.
As you have already discovered, converting as much Object based code as possible to Array based code will make a huge speed improvement.
-
This is multiple year reliable code by me, a decades long coding professional. My code is not the question. Arrays are used extensively and where appropriate. And yes KjBox, the calling program has the Application... commands at the runin and close, not in the called application service modules. The slow down occurred in a single day and in every one of the 23 macros. Years long execution experience at a consistent performance level validates the code. Something changed in my Excel. I installed Win 11 thinking it was a Windows problem. I used The Microsoft Office Repair Tool, Online option to make sure Excel was not corrupted. All to no avail. From consistent 5 minutes execution time to 47 minutes is not coding. It may be Windows issue but I'm not a Windows guru. Converting to Win 11 was no help. I remain confused.
-
Do you have access to a system using Office 2010 or Office 365?
It may be worth copying your files to that system and running the code, if the speed is back to what it used to be, then the problem must be to do with Office 2019. In that case I suggest you reach out to microsoft for help.
-
OK This is strange. Occaisional workbook close gets hung up on "waiting for other OLE action to be completed. Excel Options Advanced General at the bottom of the page uncheck Ignore other applications that use DDE. Also disabled infrequently used COMM addins. Some improvement.
-
KjBox is correct. There is an management userform that controls execution. I can select a single macro or a named list of macros such as the previously referenced of 23 macros. The management userform sets screen, calculation and events off during Initialization and back on at userform exit. Each application macro tests during runin whether it is running under the management userform or a standalone run and if standalone, the application macro does its own screen, calculation and event control. I've attached a representative macro. Typical execution time (logged by the management userform) for this macro is 4 seconds. It now requires 30 seconds to complete. I'll say it again, this is not a code problem. Something else is going on!
-
As you say code you sent looks good. Assuming other macros are too, then, as I said, the issue must lie in using Excel 2019, hopefully Microsoft Help will be able to sort you out.
-
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:
At the end of each function:
Hope that helps!
-
That assumes that Calculation is set to Automatic, if it is set to Manual for some reason then your code will change it to automatic. I think it's better to check Calculation settings first.
-
There appears to be resolution. Kudos to KjBox. Lots of time spent with Microsoft level 2. As mentioned before, Win 11 replaced Win 10. Replaced Home and Student with Office 365. Macros were previously coded with Screen updating and Calculation. Events are not managed. Much code scouring trying to find some kind of irregularity. TahDah! Back to normal execution. One coding adjustment was to move the Application screen and calc commands to immediately before and after Application.Run in my execution management userform. As best as I can determine, it's a combination of Office 365 and the execution management userform coding change. I'm calling this resolved.
-
Pleased you got it resolved.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!