vba - 我怎样才能 "force"Excel正确计算分页符？

Question

我应该将一些大数据范围从 Excel 导出到 Powerpoint，每张幻灯片一页，当然我应该处理分页符以避免“孤立”行或列。

我试图通过读取 HPageBreaks.Count 和 VPageBreaks.Count 来检查在给定缩放下我将有多少页(垂直和水平)，然后手动定义每个中断的位置。这个想法是在每个页面上具有大致相同的宽度和高度。

当我一步步调试代码时，它运行得很好，逻辑看起来也不错，但如果我“自由”运行它，分页符就完全关闭了。添加一些 MsgBox 指令，我可以看到，当我读取 HPageBreaks.Count (或垂直)时，我得到了错误的值。 (如果我手动执行代码应该执行的操作，我可以检查正确的代码)。

在许多论坛上搜索，我看到一些丑陋的解决方法，例如强制重置 PaperSize (ws.PageSetup.PaperSize = ws.PageSetup.PaperSize)。在尝试了其中一些方法之后，似乎效果更好的方法是在更改 PageSetup 之前关闭 PrintCommunication，然后重新打开它。这在我的大多数工作表上都运行良好，但在非常大的工作表(约 750 行 x 80 列，几乎所有带有公式的单元格)上，它根本不起作用。

这里是代码摘录:

    '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

您知道为什么会发生这种情况吗？我该如何解决它？

谢谢

Answer 1

当在 Debug模式下按 F8 时 VBA 代码工作正常，但按 F5 运行整个宏后却无法工作时，我对这个问题提出一般建议。

提示 1. 尽可能使用 ThisWorkbook.ActiveSheet 而不是 ActiveWorkbook.ActiveSheet 来引用正确的工作表。使用 ThisWorkbook.Application 而不是仅使用 Application。 您可能有另一个 Addin 程序在后台运行，将 ActiveSheet 切换为其他程序你可能不知道。检查启用的其他宏并删除任何不使用的内容。因此，在代码中执行任何重要操作之前，请尝试使用 ThisWorkbook.Activate 获得工作表的焦点。请参阅该页面上的图表:http://analystcave.com/vba-tip-day-activeworkbook-vs-thisworkbook/

提示 2. 强制 Excel 以与 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

或者:

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

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

提示3.如果上述方法仍然无法刷新使用:

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

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