Slow header modification (interaction with printer drivers?)

[Kimed]

Hi,

I have an Excel workbook with VBA modules. At some point it programmatically sets pages' headers with a command like

Worksheets("My Page").PageSetup.CenterHeader = (expression based on various values)

The app runs on several computers, and while on some of them it works normally, on few others this particular operation works terribly slow (moreso that the program has to setup the whole batch of pages). I came to conclusion that it lags on those comps that have a network printer attached and runs well on those where the printer is local. Is it possible to somehow bypass Excel calling for a printer and just do the replacement?

Thanks.

 

[WalkerEvans]

You make it sound as if Excel is doing a print call all by iself, with no input from code at all; this seems unlikely. If you can post the code around the area of the header call we might be able to make more sense of this.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[combo]

MS suggests that removing page breaks will speed up the code:
ActiveSheet.DisplayPageBreaks = False

You could downgrade the code to excel 4 macros:

http://www.pcreview.co.uk/forums/thread-947330.php

combo

 

[Kimed]

I'm not sure what do you mean by "code around the area of the header". The code around the described operator does not cause a slowdown - I've walked it all step by step. If you mean that something in the calculated expression might cause it - it's the same when I replace it with a simple "" literal. There's no other "code" I might think of.

(Then again, it might somehow be connected that on the same computers - not on every one where the app runs - a lesser but noticeable slowdown is caused by operations like

Range("E4").Columns.Hidden = (True/False)
Range("E4").Rows.Hidden = (True/False)

 

[combo]

Seems that tt does not like competitors, the proper address of the link above: [ignore]

http://www.pcreview.co.uk/forums/thread-947330.php

[/ignore]

combo

 

[WalkerEvans]

By "code around the area of the header" I meant that section of whichever module actually calls the header setup routine.

Is it possible to somehow bypass Excel calling for a printer and just do the replacement?

Based on this, I presumed that there might be a printer call somewhere close to the header call; from the way you worded your inquiry this certainly seemed as if it might be the case. From your last post it now appears that the connection to a network printer may be coincidental as other routines also cause a noticable processing degradation; in any case, it certainly doesn't appear that Excel is "calling for a printer" or that that is the root of your problem.

I'd try Combo's suggestion about the page breaks as a first step. There may be code problems that are causing the slowdown, but without seeing any of your modules it's impossible to know.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[HughLerwill]

I too have found (for years) that setting properties within PageSetup. is/ can be slow (although I've never investigated the cause). My preference is to base Sheets on Templates which have preset PageSetup. properties and avoid setting them from code unless absolutely necessary.
Not something that makes much diffence when producing one WorkBook/ Sheet but when doing a bunch...

 

[Kimed]

DisplayPageBreaks = False : didn't help.

Excel 4 macro: as much as I figured from the downloaded help file, PAGE.SETUP only allows to set the whole pletora of page's parameters at once, which is not convenient. I also got the impression that its gain in speed is caused merely by merging access many variables' into a single transaction. If so, this is not the case if I need only a single access to each page's header anyway. But I'll keep this method in mind if things get to be really dire.

There may be code problems that are causing the slowdown, but without seeing any of your modules it's impossible to know.

*shrug* Amongst the times where this function is called, there's one really simple:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 And Target.Column = 1 Then
    Call FormHeader
End If
End Sub

Private Sub FormHeader()
    Worksheets("Sànaudø suvestinë").PageSetup.CenterHeader = "&""Arial,Bold""&16" _
        + ChoosePadal.Value + " sànaudos" _
        + Chr(13) + "&""Arial,Bold""&12 " + Cells(5, 1).Value
    
    Worksheets("DUF suvestinë").PageSetup.CenterHeader = "&""Arial,Bold""&14" + Chr(13) _
        + Worksheets("DUF suvestinë").ChoosePadal.Value + " darbo apmokëjimo iðlaidos" _
        + Chr(13) + "&""Arial,Bold""&12 " + Cells(5, 1).Value
    
    Worksheets("Nusidëvëjimo suvestinë").PageSetup.CenterHeader = "&""Arial,Bold""&14" _
        + Worksheets("Nusidëvëjimo suvestinë").ChoosePadal.Value + " nusidëvëjimo" _
        + Chr(13) + "(amortizacijos) sànaudos" _
        + Chr(13) + "&""Arial,Bold""&12 " + Cells(5, 1).Value
    
    Worksheets("Kuro sanaudø suvestinë").PageSetup.CenterHeader = "&""Arial,Bold""&14" _
        + Worksheets("Kuro sanaudø suvestinë").ChoosePadal.Value + " kuro sànaudos" _
        + Chr(13) + "&""Arial,Bold""&12 " + Cells(5, 1).Value
End Sub

, where ChoosePadal is a combobox that's present on each of pages in question. The header must reflect both the contents of a cell that can be set manually (or programmatically in another module, but that's a different story) and affects all pages at once, and a combobox switch that is individual for each page.

Due to various reasons I'll not bother you with, I can't handle that information as merely contents of "rows to repeat at top of each page" and have to work with page headers.