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.
Posts by ConfusedBob
-
-
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!
-
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.
-
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.
-
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?