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:

Sub Worksheet_Footer()

Application.ScreenUpdating = False

For i = 1 To Worksheets.Count
doLayout (i)
Next i

Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name <> "Cover Sheet" Then
            With ws.PageSetup
                .RightHeader = ""
                .LeftHeader = ""
                .LeftFooter = ""
                .RightFooter = ""
            End With
        End If
    Next ws

Application.ScreenUpdating = True

End Sub
      

Function doLayout(Index As Integer)

Application.ScreenUpdating = False

With Worksheets(Index).PageSetup
.LeftHeader = "&""Century Gothic""&12" & Chr(13) & Sheet1.Range("$B$4") & Space(1) & Sheet1.Range("$B$5") & Space(1) & Sheet1.Range("$B$6") & Space(1) & ":" & Space(1) & Sheet1.Range("$B$7")
.LeftFooter = "&""Century Gothic""&8" & "&B Rev: &B" & Sheet1.Range("$B$8") & Space(1) & "-" & Space(1) & Sheet1.Range("$B$9") & Space(5) & "&B Designer: &B" & Space(1) & Sheet1.Range("$B$2").Value & Space(5) & "&B Phone: &B" & Space(1) & "(972) 392-3202" & Space(5) & "&B Security:&B" & Space(1) & "TX B14470 | TX ACR-1776026 | AR CMPY.0002197" & b & Space(5) & "&B Printed: &B" & Space(1) & DateValue(Now)
.RightFooter = "&""Century Gothic""&8" & "Page &P of &N"
End With

Application.ScreenUpdating = True

End Function

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?

[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.

[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:

Sub Worksheet_Footer()

    Dim ws          As Worksheet
    Dim i           As Long

    Application.ScreenUpdating = False
    Application.PrintCommunication = False

    For i = 1 To Worksheets.Count
        doLayout i
    Next i

    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name <> "Cover Sheet" Then
            With ws.PageSetup
                .RightHeader = ""
                .LeftHeader = ""
                .LeftFooter = ""
                .RightFooter = ""
            End With
        End If
    Next ws

    Application.PrintCommunication = True
    Application.ScreenUpdating = True

End Sub


Sub doLayout(Index As Integer)


    With Worksheets(Index).PageSetup
        .LeftHeader = "&""Century Gothic""&12" & Chr(13) & Sheet1.Range("$B$4") & Space(1) & Sheet1.Range("$B$5") & Space(1) & _
                      Sheet1.Range("$B$6") & Space(1) & ":" & Space(1) & Sheet1.Range("$B$7")

        .LeftFooter = "&""Century Gothic""&8" & "&B Rev: &B" & Sheet1.Range("$B$8") & Space(1) & "-" & Space(1) & _
                      Sheet1.Range("$B$9") & Space(5) & "&B Designer: &B" & Space(1) & Sheet1.Range("$B$2").Value & Space(5) & _
                      "&B Phone: &B" & Space(1) & "(972) 392-3202" & Space(5) & "&B Security:&B" & Space(1) & _
                      "TX B14470 | TX ACR-1776026 | AR CMPY.0002197" & b & Space(5) & "&B Printed: &B" & Space(1) & Format(Date)

        .RightFooter = "&""Century Gothic""&8" & "Page &P of &N"
    End With


End Sub

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:

Sub Worksheet_Footer()

    Dim ws          As Worksheet
    Dim i           As Long

    Application.ScreenUpdating = False
    Application.PrintCommunication = False

    For i = 1 To Worksheets.Count
        doLayout i
    Next i

    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name <> "Cover Sheet" Then
            With ws.PageSetup
                .RightHeader = ""
                .LeftHeader = ""
                .LeftFooter = ""
                .RightFooter = ""
            End With
        End If
    Next ws

    Application.PrintCommunication = True
    Application.ScreenUpdating = True

End Sub


Sub doLayout(Index As Integer)


    With Worksheets(Index).PageSetup
        .LeftHeader = "&""Century Gothic""&12" & Chr(13) & Sheet1.Range("$B$4") & Space(1) & Sheet1.Range("$B$5") & Space(1) & _
                      Sheet1.Range("$B$6") & Space(1) & ":" & Space(1) & Sheet1.Range("$B$7")

        .LeftFooter = "&""Century Gothic""&8" & "&B Rev: &B" & Sheet1.Range("$B$8") & Space(1) & "-" & Space(1) & _
                      Sheet1.Range("$B$9") & Space(5) & "&B Designer: &B" & Space(1) & Sheet1.Range("$B$2").Value & Space(5) & _
                      "&B Phone: &B" & Space(1) & "(972) 392-3202" & Space(5) & "&B Security:&B" & Space(1) & _
                      "TX B14470 | TX ACR-1776026 | AR CMPY.0002197" & b & Space(5) & "&B Printed: &B" & Space(1) & Format(Date)

        .RightFooter = "&""Century Gothic""&8" & "Page &P of &N"
    End With


End Sub

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!

[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

Sub doLayout(Index As Long)

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

Artik

[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.

[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!