How can I "force" Excel to count page breaks correctly?

Question

I am supposed to export some large data ranges from Excel to Powerpoint, one page per slide, and of course I should treat the page breaks to avoid "orphan" rows or columns.

I am trying to check how many pages I would have, vertically and horizontally, with a given zoom, by reading HPageBreaks.Count and VPageBreaks.Count, and then manually define the position of each break. The idea is to have approximately the same width and height on each page.

When I debug my code step-by-step, it runs nicely, and the logic seems ok, but if I run it "freely", the page breaks are completely off. Adding some MsgBox instructions, I can see that when I read HPageBreaks.Count (or vertical) I get the wrong values. (I can check the correct ones if I do manually what the code should do).

Searching on many many forums, I see some ugly workarounds like forcing a reset of PaperSize (ws.PageSetup.PaperSize = ws.PageSetup.PaperSize). After trying some of them, what seemed to work a bit better was to turn off PrintCommunication before a change to PageSetup, and then turn it back on. This worked well on most of my sheets, but on the really large ones (~750 rows x 80 columns, almost all cells with formulas), it simply doesn't.

Here an extract of the code:

    'Reset page breaks
    .ResetAllPageBreaks

    'Set minimum acceptable zoom factor
    Application.PrintCommunication = False   'This is the ugly workaround
    .PageSetup.Zoom = 60
    Application.PrintCommunication = True

    MsgBox "Zoom = " & .PageSetup.Zoom    'Only for debugging

    'Calculate the number of pages in width
    Application.PrintCommunication = False
    NPagesWide = .VPageBreaks.Count + 1
    Application.PrintCommunication = True

    MsgBox "NPagesWide = " & NPagesWide

    'Find the higher zoom factor that can fit that number of pages
    Application.PrintCommunication = False
    .PageSetup.Zoom = 100
    Application.PrintCommunication = True
    Do While .VPageBreaks.Count > NPagesWide - 1
        Application.PrintCommunication = False
        .PageSetup.Zoom = .PageSetup.Zoom - 5
        Application.PrintCommunication = True
    Loop

    MsgBox "Zoom = " & .PageSetup.Zoom

    'Set average width per page and initialize column pointer
    If HasTitleColumns Then     'Defined earlier
        PageWidth = (PrintArea.Width + TitleColumns.Width * (NPagesWide - 1)) / NPagesWide
        j = TitleColumns.Columns(TitleColumns.Columns.Count).Column + 1
    Else
        PageWidth = PrintArea.Width / NPagesWide
        j = 1
    End If

    'Cycle vertical page breaks
    For i = 1 To NPagesWide - 1
        'Set width of TitleColumns
        If HasTitleColumns Then
            CumulWidth = TitleColumns.Width
        Else
            CumulWidth = 0
        End If
        'Cumulate columns width until the available page width
        Do While CumulWidth + .Columns(j).Width <= PageWidth
            CumulWidth = CumulWidth + .Columns(j).Width
            j = j + 1
        Loop
        'Add the break
        .VPageBreaks.Add .Columns(j + 1)
    Next i

Any ideas why this happens, and how can I solve it?

Thanks,

Answer 1

I propose general advice to the issue when VBA code works fine while hitting F8 in the debug mode, but it doesn't not work after hitting F5 to run the whole macro.

Hint 1. Use ThisWorkbook.ActiveSheet instead of ActiveWorkbook.ActiveSheet whenever possible to reference the proper sheet. Use ThisWorkbook.Application instead of just Application. It could be that you have another Addin program working on in background, switching ActiveSheet to something else that you may not be aware of. Check other macros enabled and get rid of anything that you do not use. So before anything important in your code, try to get the focus for your sheet with ThisWorkbook.Activate. See the graph on that page: http://analystcave.com/vba-tip-day-activeworkbook-vs-thisworkbook/

Hint 2. Force Excel to wait in different way then DoEvents.

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'Place this line of code at the very top of module
Sleep 1   'This will pause execution of your program for 1 ms

https://stackoverflow.com/a/3891017/1903793 https://www.fmsinc.com/microsoftaccess/modules/examples/AvoidDoEvents.asp

Or alternatively:

Application.Calculate 
If Not Application.CalculationState = xlDone Then
    DoEvents
End If

https://stackoverflow.com/a/11277152/1903793

Hint 3. If the above still does not work for refreshing use:

ThisWorkbook.Connections("ConectionName").Refresh
ThisWorkbook.Application.CalculateUntilAsyncQueriesDone 

https://stackoverflow.com/a/26780134/1903793

Answer 2

Another workaround for me was to set the PrintArea new (without .PrintCommunication = false)

wks.PageSetup.PrintArea = wks.PageSetup.PrintArea

This workaround does not affect any pagesetup settings (unlike the .Zoom property)

The problem only occurs if the PageView is set to xlPageLayoutView and there is data "outside" the printarea (UsedRange is larger than PrintArea) when running the macro.

So you can check if the sheet is in PageLayoutView before you do the workaround. If the sheet is in any other view the pagebreaks.count always works fine here.