VBA: The Efficiency of a Piece of Code

[Domenic]

According to a client of mine, the following code takes about 20 seconds to run on his computer. However, it only takes about 1.6 seconds to run on my Windows based computer, and about 3 seconds on my old Mac computer. Even with what he says is a slow computer, I don't see why it would take so long. Here's the code...

Sub UpdateHeader()

    Dim i As Long
     
    ActiveSheet.PageSetup.CenterHeader = Range("q10").Value
    
    With Worksheets(1).PageSetup
         
        For i = 2 To 6
            Worksheets(i).PageSetup.LeftHeader = .LeftHeader
            Worksheets(i).PageSetup.CenterHeader = .CenterHeader
            Worksheets(i).PageSetup.RightHeader = .RightHeader
            Worksheets(i).PageSetup.LeftFooter = .LeftFooter
            Worksheets(i).PageSetup.CenterFooter = .CenterFooter
            Worksheets(i).PageSetup.RightFooter = .RightFooter
        Next i
        
    End With


End Sub

Does any one have any insight, comments, etc.?

 

[VoG]

This may be a printer driver issue. My understanding is that any code that changes printing properties interacts with the printer driver.

 

[VoG]

P.S. In Excel 2010 you can use

Application.PrintCommunication = False

 

[shg]

An alternative using xl4 macros at http://www.mcgimpsey.com/excel/udfs/pagesetup.html

 

[Firefly2012]

You may find using ExecueExcel4macro commands speeads things up too 9once you've got the settings you want to apply to each sheet). I haven't used this before but i know this is one common way to speed up page setup as it doesn#'t involve communicating with the printer. There seems to be a thread on this here:

http://www.mrexcel.com/forum/showthread.php?362900-Using-XLM4-Page-Setup

 

[Domenic]

VoG, shg, Firefly2012

Thank you very much for your help. I wasn't aware of this issue. So it's definitely good to know. I'll post back with the results.

Thanks again!

 

[Domenic]

For compatibility reasons, the XL4 macro was used. Apparently, it was "noticeably faster".

Thanks again for everyone's help!

Cheers!