VBA statements for pagesetup only executed in debu

问题:

The following sub is supposed to prepare pagesetup for a PDF output. For instance, if due to other attached printers the pagebrakes are messed up, the sub should fix it back to 1 page wide and 3 pages tall.

Sub adjustPB(ws As Variant, ps As XlPaperSize)
'On Error Resume Next
Application.DisplayAlerts = False
Application.PrintCommunication = False
With ws.PageSetup
    .LeftMargin = Application.InchesToPoints(0)
    .RightMargin = Application.InchesToPoints(0)
    .TopMargin = Application.InchesToPoints(0)
    .BottomMargin = Application.InchesToPoints(0)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    .Orientation = xlLandscape
    '.Orientation = xlPortrait
    .PaperSize = ps
    .Zoom = 100
    .Zoom = False
    Debug.Print .Zoom
    .FitToPagesWide = 1
    Debug.Print .Zoom
    Debug.Print .FitToPagesWide
    .FitToPagesTall = 3
End With
Application.DisplayAlerts = True
Application.PrintCommunication = True
End Sub

The sub actually works as expected in single step (F8), when I add a breakpoint at 'With ws.PateSetup'. If I run it using F5, however, it ignores the statements. The debug prints show, that the values for the properties didn't change.

Things tried so far: add delays before .zoom and .FitPagesWide with DoEvents for up to 1 sec. No change. Zoom for instance remains 55. In singlestep, Zoom reads FALSE in the end. Any explanations / hints what's going wrong here?

回答1:

.PrintCommunication may be the key. The documentation is rather obscure at this point but it looks like Excel caches all commands when .PrintCommunication is OFF and dumps them to page setup engine when you turn .PrintCommunication ON. That may be the reason for not seeing any changes when running with F5. (The services of the debugger are even more obscure for me.)

Try applying

With ActiveSheet.PageSetup
     ' .... 
    .Parent.Application.PrintCommunication = True
    .Zoom = 100
    .Zoom = False
    Debug.Print .Zoom
    .FitToPagesWide = 1
    Debug.Print .Zoom
    Debug.Print .FitToPagesWide
   .FitToPagesTall = 3
   .Parent.Application.PrintCommunication = False
   ' .... 
End With
Application.PrintCommunication = True

I'am also curious to see the result :)