VBA optimization for .PageSetup code

[Dr. Demento]

I've got some code that to my inexperienced mind should run quickly, but it bogs the machine down for 1-2 minutes and I'm hoping for some pointers on how it can be adapted to run more efficiently. I've usually got less than five worksheets in a file and cells used < 1000, so it shouldn't be that challenging (but hey, what do I know?) .

I'm using Excel 2010.

Thanks so much.

Option Explicit
Sub HeaderFooter()
'
' Force_Header_Footer Macro
' Forces custom Header & Footer to all Excel workbooks/sheets
'
Dim SheetNumber As Integer
Dim NumberOfSheets As Integer
NumberOfSheets = Worksheets.Count
  For SheetNumber = 1 To NumberOfSheets
  With Worksheets(SheetNumber).PageSetup
  .LeftMargin = Application.InchesToPoints(0.5)
  .RightMargin = Application.InchesToPoints(0.5)
  .TopMargin = Application.InchesToPoints(0.75)
  .BottomMargin = Application.InchesToPoints(0.75)
  .HeaderMargin = Application.InchesToPoints(0.25)
  .FooterMargin = Application.InchesToPoints(0.25)
  .LeftHeader = ""
  .CenterHeader = "File:  &F" & Chr(10) & "Sheet: &A"
  .RightHeader = ""
  .LeftFooter = ""
  .CenterFooter = "Page &P of &N"
  .RightFooter = "&D || &T"
  .ScaleWithDocHeaderFooter = False
  .DifferentFirstPageHeaderFooter = False
  .PrintQuality = 600
  .CenterHorizontally = True
  .CenterVertically = False
  If ActiveSheet.UsedRange.Width / ActiveSheet.UsedRange.Height > 0.85 Then  '~~~> ActiveSheet will determine Orientation | http://stackoverflow.com/questions/24733215/excel-vba-how-to-detect-that-the-printed-width-is-greater-than-the-printed-height
  .Orientation = xlLandscape
  End If
  .Draft = False
  .PaperSize = xlPaperLetter
  .FirstPageNumber = xlAutomatic
  .Order = xlDownThenOver
  .BlackAndWhite = False
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = False
  End With
  Next SheetNumber
End Sub

 

[NARAYANK991]

Hi ,

I don't see why the code you have posted should take minutes to execute.

One improvement that can be done is to put the following statement outside the loop :

Margin1 = Application.InchesToPoints(0.25)

Within the loop , change the first few statements as follows :

  .LeftMargin = Margin1 * 2
  .RightMargin = Margin1 * 2
  .TopMargin = Margin1 * 3
  .BottomMargin = Margin1 * 3
  .HeaderMargin = Margin1
  .FooterMargin = Margin1

If you wish , even the multiplication by 2 and 3 can be stored in intermediate variables outside the loop , and within the loop , just assign those variable values to the margins.

Narayan

 

[Dr. Demento]

Narayan -- thank you; that's slick. I forget about doing it like that.

I just thought of this -- as I'm on a work machine and macros are by default disabled, perhaps there are other imposed restrictions that I'm not aware of that could be causing the sludge-like behavior.

One other question - using the Excel switches (&P, &N, . . . ) wouldn't be causing this, would it? Should I be using native VBA parameters instead?

 

[Speedy]

Hi, add

Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True

 

[Dr. Demento]

Speedy,

Well, that helped quite a bit on my home machine. I'll have to give it a try at work.

Thanks much.

 

[NARAYANK991]

Hi ,

More information is available here :

https://msdn.microsoft.com/en-us/library/office/ff835544.aspx

Narayan

 

[Dr. Demento]

Speedy / Narayan - I turned off the PrintCommunication for the duration of the macro and it's significantly faster; however, I don't get the outcome I used to -- The filename prints but not the sheetname, the page number prints but not the total number of pages, and the date prints but not the time. When I take out the PrintCommunication lines, those "missing fields" come back but then I'm stuck with the long latency again.

A similar issue was reported here and their solution was to delete the PrintCommunication flags :-| I'm running Excel 2010, so it shouldn't be a version issue.

Just curious if you have any additional insights. Thanks much.

 

[NARAYANK991]

Hi ,

If you can upload a workbook with enough data in it , as well as the code , it will probably easier to debug the problem.

Narayan

 

[Dr. Demento]

My apologies - I'm still picking gravel out of my roadrash from work so I didn't have time to respond.

I've attached an example of the issue that highlights the issue that including PrintCommunication is causing with the Header/Footer macro.

When I comment out Application.PrintCommunication, it takes a long while, but the print output is what I would expect (worksheet: Initial).

When I include Application.PrintCommunication, the speed of execution is markedly better, but there's only 1/2 of the header/footer info (worksheet: Final).

I'm hoping to achieve a happy melding -- quick and complete.

Thanks for your help.

 

[Speedy]

Hi,

Activate the first sheet to the left that contains the information required setting. This is the paper "source".
Press Ctrl and click the sheet tabs you want to update the settings on the source worksheet.
Select File, Page Setup, and click OK.
Page Setup settings of the source sheet will be transferred all the sheets of destination.

Rif: http://www.cpearson.com/...

 

[Dr. Demento]

Slick. Thanks!