Macro's in Excel from Microsoft 365 EXTREMELY SLOW

[René Meijers]

My setup:

• Windows 11 Pro 64 bits Versie: 22621.1105
• PC with Intel(R) Core(TM) i7-10700 CPU @ 2.90GH and 16 Gb of Memory
• Video NVIDIA GeForce GTX 1650
• Microsoft 365 Family with latest update

I struggle with the following problem (and even more problems i will post in a different message).

I made an excel-application I want to bring to the market soon. In Excel 2016 it runs like a charm, but in Microsoft 365 Excel macro's run extremely slow in Microsft 365. At the office we use Office 2016 and it runs all smooth. Does microsoft even look into this problem that exist since 2022? I don't see any progress!!

The most noticable slow downs for me when doing the following action through VBA:

• Insert, delete or move a line or column on a sheet
• Hide and unhide lines or columns on a sheet

An example from my application:

• I have a userform and underlying macro to create a pdf from a sheet
• It is a very big sheet with a lot of formulas and data
• With the userform I can select wich part, wich lines or wich Columns from the sheet wil be hidden before saving it to pdf
• Then when it hides the lines/columns it is slow, but when it unides the lines after saving the pdf it is even much slower
• In office 2016 it takes max 10 seconds to unhide the lines, save to pdf, show the lines again and close the macro; In Microsft 365 it takes more then 1 minute! 
• And Yes..... I use 'application.screenupdating = false' and 'application.calculation = xlcalculationmanual' at the start of the subroutine. So the screen stays stady until the macro stopt running.

When the problem stays like this i will go back to office 2019 of 2021, or do I even have to go to office 2016? Does Anyone know if the architecture of Microsoft 365 is differtent from the Office versions 2016,2019,2021?

PLEASE MICROSOFT SOLVE THIS!

I would be happy to share my file with Microsoft so they can find the problem. My application has 7 sheets and 58.000 VBA codelines, so I think this would be THE file to trace the problem(s)!

I hope to hear soon from you, microsoft!

[#2]

I do similar things in VBA and have not noticed any slow downs. (The only slowdown we see concerns Word mailings from an Excel table. Appalling. Beyond a joke.) I saw your other post too. I would recommend a Repair / Re-install of your complete O365.

[#3]

Thanks for your reply!
I will do a re-install. But I'm not the only one complaining about slowdowns.

[#4]

Step 1: I did a repair but that didn't make any difference...

Step 2: I removed and re-installed O365 (standard 64bit) but that didn't make any difference

Step 3: I removed and re-installed O365 32 bit but that didn't make any difference

Any more suggestions?

[#5]

Comment out the PDF generation. As I wrote, we observe disastrously slow data exchange with Word. I wonder whether your PDF generation suffers from similar problems. Does your ScreenUpdating=False now work after the re-installation? (That bit works here.)

[#6]

@René Meijers

I'm using MS Exel 365

I have a Blackjack simulator that uses VBA. A couple days ago it would take around 3 hours to simulate 100K hands.  Now for unknow reason it will take around 8 hours. 

I have no idea what could cause the slow down, I'm suspecting there might have been an update in the past few days.

[#7]

If you're looking to get Microsoft's attention, go to File | Feedback and Click the frown to send feedback. You may get a reply with a request to send the workbook. This forum is mostly volunteers that chip in to help others.

[#8]

WOW
The January 25th windows update fixed the bug created by the January 24 bug.
I'm back at 3 hours
:)

[#9]

@Patrick2788

Thanks for the tip Patrick! I will do that and hope they will reply! 

[#10]

After your reply I noticed something strange.

When I hit the 'create pdf' button on my userform it takes 38 seconds to finish creating the document and finish the macro'. But When I hit the 'create pdf' button and switch (alt-tab) to the folder in my explorer (and the macro runs in the background) it only takes 13 seconds to finish creating the pdf and finish the macro.... How is that possible?!

And this only happens in O365. In O2016 it works like a charm.

The screenupdating = false still doesn't work well in all routines. In some routines it works well but in others it doesn't. And this only happens in O365

[#11]

I do not know the answer but our suspicion must surely be that the two problems are related. Perhaps your "create pdf" button performs an operation that is fast when the screen does not require updating but slow when it does? I really cannot understand how screenupdating=FALSE cannot "work well" - it either works or it doesn't. There is no room for it working badly.

From poor memory, SceenUpdating is not scoped, ie will not reset to TRUE on its own accord. The error in VBA more often than not is to fail to reinstate ScreenUpdating. That said, I cannot recall how Excel deals with a VBA macro exiting while ScreenUpdate remains stuck at FALSE. Surely, Excel cannot tolerate such an exit.

So my suspicion might be that your macro is somehow transitioning between in-macro and out-of-macro state, implicitly setting ScreenUpdating=TRUE whenever transitioning out-of-macro and failing to reinstate ScreenUpdating=FALSE because you do not recognize the transition back to in-macro.

That might explain your language "not well".

Are you using Excel's built-in PDF creator?  I would focus my attention at that PDF creator.  It seems to interact with your Excel in an unhealthy fashion, differently on O365 than your other version.

Once more, I would comment out the one-line call to the delegated PDF creation to localize the problem.  If performance improves, and if the problems with ScreenUpdating somehow go away, you know that both problems relate to that delegation.  (The actual PDF creation is unlikely of any relevance to the rest of your code.)

[#12]

Yes I use the built-in PDF creator from excel

[#13]

Hello. I'm a newbie just learning Excel but need the best version. Did you resolve the issue? Would you recommend I ditch 365?

[#14]

@mishaearle 

A didn't get any response, but have made some changes to my code that helps a bit. Although I would recomend 365 because it has a lot of new things that are very welcome. Like new simplifying formulas and more...

[#15]

@René Meijers 

Thanks!