Code Runs Very Slow

[xTyD23x]

I have an Excel Workbook that someone added some coding to a long time ago. This code runs extremely slow and hangs every machine up for 5 seconds or so when it runs. When auto-save is enabled in Excel, the file becomes almost unusable because of this. The only thing I have modified is adding the ScreenUpdating stuff to the beginning and end of each sub, but that had little to no effect. Here is the code:

Please Login or Register  to view this content.

Any ideas for how we can get this to run a little smoother/faster?? Thanks in advance for the help!

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

[jolivanes]

If you initially set your printer to PDF and just before printing, set it back to whichever printer you use.
Or leave it at PDF and save it as a PDF type file.
Does that make a difference?

[Artik]

Setting multiple print page properties is a lengthy process, because immediately after changing one property, a message is sent to the printer driver and the program waits for a response. Only then is the next property set. Therefore, in earlier versions of Excel, macro 4.0 was used. I do not remember from which version (maybe 2007) the possibility of disconnecting the connection with the printer was introduced for the time of changing the print page settings.
Try this modified code:

Please Login or Register  to view this content.

Artik

[xTyD23x]

Setting multiple print page properties is a lengthy process, because immediately after changing one property, a message is sent to the printer driver and the program waits for a response. Only then is the next property set. Therefore, in earlier versions of Excel, macro 4.0 was used. I do not remember from which version (maybe 2007) the possibility of disconnecting the connection with the printer was introduced for the time of changing the print page settings.
Try this modified code:

Please Login or Register  to view this content.

Artik

Thank you! Unfortunately, when I replace my code with the code you provided, I get the following error:

Screenshot.png

I tried just using the PrintCommunication part (set to False, then back to True) and it seemed to slightly improve, but still hangs for a few seconds.

Thanks again!

[xTyD23x]

If you initially set your printer to PDF and just before printing, set it back to whichever printer you use.
Or leave it at PDF and save it as a PDF type file.
Does that make a difference?

Thank you for the suggestion. Unfortunately, I'm not sure how to do what you're saying...

[xTyD23x]

I have attached the workbook. Had to zip it to get it under the max file size.

[jolivanes]

This is for Windows 10. I don't have 365.
https://support.microsoft.com/en-us/...4-c41022b5036f
Select the "Microsoft Print To PDF" and then run your macro. It might or might not make a difference but when using PageSetup, it continuously communicates with the printer which slows thing down. Try it.

[Artik]

Thank you! Unfortunately, when I replace my code with the code you provided, I get the following error:

It's just a type mismatch. Change the procedure declaration to

Please Login or Register  to view this content.

But your further statement says that this is unlikely to help in a significant acceleration of the action.

Artik

[xTyD23x]

This is for Windows 10. I don't have 365.
https://support.microsoft.com/en-us/...4-c41022b5036f
Select the "Microsoft Print To PDF" and then run your macro. It might or might not make a difference but when using PageSetup, it continuously communicates with the printer which slows thing down. Try it.

Thanks for the suggestion, but I'm looking for a solution that will fix this issue on multiple user's machines so I'd like to get this code optimized. Your solution could be a nice work-around for the time being though, so thank you!

[xTyD23x]

I went back and forth between using the PrintCommunication False/True statements, and it definitely makes a big difference to have those in there. It isn't perfect right now, but it's already improved a bunch, so thanks again! If anyone else has any suggestion as to how it could be even better I'm all ears. Thank you!

[TMS]

Historically, Page Setup has always been horrendously slow. So much so that, typically, it has often been recommended to use Excel4 macros.

See: https://msgroups.net/excel.programmi...xcel4-ma/21423

Google: excel4 macro for printer settings for more information/examples

[xTyD23x]

Historically, Page Setup has always been horrendously slow. So much so that, typically, it has often been recommended to use Excel4 macros.

See: https://msgroups.net/excel.programmi...xcel4-ma/21423

Google: excel4 macro for printer settings for more information/examples

Thank you, I'll check this out. Really appreciate the help guys!