通过 VBA 的 Excel 分页符

发布于 2024-07-24 08:55:57 字数 4072 浏览 5 评论 0原文

作为报告生成器大修的一部分，我看到了我认为效率低下的代码。这部分代码在生成主报告后运行，以在逻辑位置设置分页符。标准是这样的：

每个站点都从一个新页面开始。
不允许跨页面分解组。

代码遵循上述格式：2 个循环执行这些工作。

这是原始代码（抱歉太长了）：

Public Sub PageBreak(ByRef wstWorksheet As Excel.Worksheet, ByVal pctProgress As ProgressCtl.ProgressControl)
Dim breaksMoved As Integer
Dim p As HPageBreak
Dim i As Integer

'Used as a control value
breaksMoved = 1

' Marks that no rows/columns are to be repeated on each page
wstWorksheet.Activate
wstWorksheet.PageSetup.PrintTitleRows = ""
wstWorksheet.PageSetup.PrintTitleColumns = ""

'If this isn't performed beforehand, then the HPageBreaks object isn't available
Range("A3").Select
ActiveWindow.View = xlPageBreakPreview

'Defaults the print area to be the entire sheet
wstWorksheet.DisplayPageBreaks = False
wstWorksheet.PageSetup.PrintArea = ""

Range("$B$4").Select

' add breaks after each site
Do While ActiveCell.Row <= wstWorksheet.UsedRange.Rows.Count
    If ActiveCell.FormulaR1C1 = "Site ID" Then
        ActiveCell.PageBreak = xlPageBreakManual
    End If
    ActiveCell.Offset(1, 0).Activate
    pctProgress.ProgressText = "Row " & CStr(ActiveCell.Row) & " of " & CStr(wstWorksheet.UsedRange.Rows.Count)
Loop

Dim passes As Long
Do While breaksMoved = 1
    passes = passes + 1
    breaksMoved = 0
    For i = 1 To wstWorksheet.HPageBreaks.Count - 1
            Set p = wstWorksheet.HPageBreaks.Item(i)
            'Selects the first page break
            Range(p.Location.Address).Select
            'Sets the ActiveCell to 1 row above the page break
            ActiveCell.Offset(-1, 0).Activate

            'Move the intended break point up to the first blank section
            Do While Not ActiveCell.FormulaR1C1 = ""
                ActiveCell.Offset(-1, 0).Activate
                breaksMoved = 1
            Loop

            'Add the page break
            If ActiveCell.FormulaR1C1 <> "Site ID" Then
                ActiveCell.Offset(1, 0).Activate
                wstWorksheet.HPageBreaks.Add ActiveCell
            End If

            pctProgress.ProgressText = "Set break point " & CStr(passes) & "." & CStr(i)

    Next

Loop

'Reset the view to normal
wstWorksheet.DisplayPageBreaks = True
ActiveWindow.View = xlNormalView
Range("A3").Select
End Sub

看到改进的空间，我开始修改它。作为新要求之一，想要报告的人在打印之前手动删除页面。因此，我在另一页上添加了复选框并复制了选中的项目。为了方便起见，我使用了命名范围。我使用这些命名范围来满足第一个要求：

' add breaks after each site   
For Each RangeName In ActiveWorkbook.Names
    If Mid(RangeName.Name, 1, 1) = "P" Then
        Range(RangeName).Activate
        ActiveCell.Offset(Range(RangeName).Rows.Count - 1, 0).Select
        ActiveCell.PageBreak = xlPageBreakManual
    End If
Next RangeName

所有范围都以 P_（表示父级）为前缀。使用蹩脚的 Now() 风格的粗略计时，我的简短 4 个站点报告和更具挑战性的 15 个站点报告慢了 1 秒。它们分别有 606 行和 1600 行。

1秒也不算太糟糕。让我们看看下一个标准。每个逻辑组都由一个空白行分隔，因此最简单的方法是找到下一个分页符，后退一步，直到找到下一个空白行并插入新的分页符。冲洗并重复。

那么为什么原著会出现多次呢？我们也可以改进它（循环外的样板是相同的）。

Dim i As Long
Dim oPageBreak As HPageBreak
Do While i < shtDeliveryVariance.HPageBreaks.Count - 1
    i = i + 1
    pctProgress.ProgressText = "Setting Page Break " & CStr(i) & " of " & CStr(shtDeliveryVariance.HPageBreaks.Count)

    Set oPageBreak = shtDeliveryVariance.HPageBreaks.Item(i)

    ' select the page break
    Range(oPageBreak.Location.Address).Select
    ActiveCell.Offset(-1, 0).Activate

    ' move up to a free row
    Do While Not ActiveCell.FormulaR1C1 = ""
        ActiveCell.Offset(-1, 0).Activate
    Loop

    'Add the page break
    If ActiveCell.FormulaR1C1 <> "Site ID" Then
        ActiveCell.Offset(1, 0).Activate
        shtDeliveryVariance.HPageBreaks.Add ActiveCell
    End If

Loop

一通也更优雅。但快了多少呢？在小型测试中，与原来的 45 秒相比，需要 54 秒，而在大型测试中，我的代码再次变慢，为 153 到 130 秒。这也是 3 次运行的平均值。

所以我的问题是：为什么我的新代码比原来的代码慢得多，尽管我的代码看起来更快以及我能做些什么来加快代码的速度？

注意：Screen.Updating等已关闭，计算等也已关闭。

原文

As part of an overhaul of a report generator I saw what I believed to be inefficient code. This part of the code runs after the main report is generated to set the page breaks in logical positions. The criteria is this:

Each Site starts on a new page.
Group's aren't allowed to broken across pages.

The code follows the above format: 2 loops doing those jobs.

This is the original code (sorry for the length):

Public Sub PageBreak(ByRef wstWorksheet As Excel.Worksheet, ByVal pctProgress As ProgressCtl.ProgressControl)
Dim breaksMoved As Integer
Dim p As HPageBreak
Dim i As Integer

'Used as a control value
breaksMoved = 1

' Marks that no rows/columns are to be repeated on each page
wstWorksheet.Activate
wstWorksheet.PageSetup.PrintTitleRows = ""
wstWorksheet.PageSetup.PrintTitleColumns = ""

'If this isn't performed beforehand, then the HPageBreaks object isn't available
Range("A3").Select
ActiveWindow.View = xlPageBreakPreview

'Defaults the print area to be the entire sheet
wstWorksheet.DisplayPageBreaks = False
wstWorksheet.PageSetup.PrintArea = ""

Range("$B$4").Select

' add breaks after each site
Do While ActiveCell.Row <= wstWorksheet.UsedRange.Rows.Count
    If ActiveCell.FormulaR1C1 = "Site ID" Then
        ActiveCell.PageBreak = xlPageBreakManual
    End If
    ActiveCell.Offset(1, 0).Activate
    pctProgress.ProgressText = "Row " & CStr(ActiveCell.Row) & " of " & CStr(wstWorksheet.UsedRange.Rows.Count)
Loop

Dim passes As Long
Do While breaksMoved = 1
    passes = passes + 1
    breaksMoved = 0
    For i = 1 To wstWorksheet.HPageBreaks.Count - 1
            Set p = wstWorksheet.HPageBreaks.Item(i)
            'Selects the first page break
            Range(p.Location.Address).Select
            'Sets the ActiveCell to 1 row above the page break
            ActiveCell.Offset(-1, 0).Activate

            'Move the intended break point up to the first blank section
            Do While Not ActiveCell.FormulaR1C1 = ""
                ActiveCell.Offset(-1, 0).Activate
                breaksMoved = 1
            Loop

            'Add the page break
            If ActiveCell.FormulaR1C1 <> "Site ID" Then
                ActiveCell.Offset(1, 0).Activate
                wstWorksheet.HPageBreaks.Add ActiveCell
            End If

            pctProgress.ProgressText = "Set break point " & CStr(passes) & "." & CStr(i)

    Next

Loop

'Reset the view to normal
wstWorksheet.DisplayPageBreaks = True
ActiveWindow.View = xlNormalView
Range("A3").Select
End Sub

Seeing room for improvement I set about modifying this. As one of the new requirements the people wanting the report were manually removing pages prior to printing. So I added checkboxes on another page and copied the checked items across. To ease that I used named ranges. I used these named ranges to meet the first requirement:

' add breaks after each site   
For Each RangeName In ActiveWorkbook.Names
    If Mid(RangeName.Name, 1, 1) = "P" Then
        Range(RangeName).Activate
        ActiveCell.Offset(Range(RangeName).Rows.Count - 1, 0).Select
        ActiveCell.PageBreak = xlPageBreakManual
    End If
Next RangeName

All Ranges are prefixed with P_ (for parent). Using the lame Now() style of rough timing this is 1 second slower on my short 4 site report and the more challenging 15 site report. These have 606 and 1600 rows respectively.

1 second isn't so bad. Lets look at the next criteria.
Each logical group is split by a blank row, so the easiest way is to find the next page break, step back until you find the next blank line and insert the new break. Rinse and repeat.

So why does the original run through multiple times? We can improve that too (the boiler plate outside the loops is the same).

Dim i As Long
Dim oPageBreak As HPageBreak
Do While i < shtDeliveryVariance.HPageBreaks.Count - 1
    i = i + 1
    pctProgress.ProgressText = "Setting Page Break " & CStr(i) & " of " & CStr(shtDeliveryVariance.HPageBreaks.Count)

    Set oPageBreak = shtDeliveryVariance.HPageBreaks.Item(i)

    ' select the page break
    Range(oPageBreak.Location.Address).Select
    ActiveCell.Offset(-1, 0).Activate

    ' move up to a free row
    Do While Not ActiveCell.FormulaR1C1 = ""
        ActiveCell.Offset(-1, 0).Activate
    Loop

    'Add the page break
    If ActiveCell.FormulaR1C1 <> "Site ID" Then
        ActiveCell.Offset(1, 0).Activate
        shtDeliveryVariance.HPageBreaks.Add ActiveCell
    End If

Loop

One pass and more elegant too. But how much quicker is it? On the small test is takes 54 seconds compared to the original 45 seconds, and on the larger test my code is slower again at 153 to 130 seconds. And this is averaged over 3 runs too.

So my questions are: Why is my newer code so much slower than the original despite mine looking faster and what can I do to speed up the slowness of the code?

Note: Screen.Updating, etc. is already off as is Calculation etc.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嗫嚅 2024-07-31 08:55:57

我在代码中的几个地方看到了改进的空间：

不要访问缓慢实现的属性，例如多次使用usedrange.rows.count（特别是在循环内），除非您认为它们可能发生了变化。而是将它们存储在变量中。
如果可以避免，请不要进行文本比较（例如：.Value = ""），而是使用 LenB 函数来检查是否为空，它会执行得更快，因为它只是读取字符串标头的长度，而不是启动到逐字节字符串比较。（您可能会喜欢阅读本文。）
不要使用“激活”或“选择” " 要在 ActiveCell 中移动，只需直接访问范围即可。
循环时，构造循环以执行尽可能少的测试。如果循环必须始终执行一次，那么您需要一个测试后循环。
确保您已锁定 Excel 界面，因为运行事件和屏幕更新等可能会大大减慢您的代码速度。（特别是事件。）
最后，我注意到您正在对“站点 ID”的大小写做出假设，除非没有可能的方式来区分大小写，否则最好进行不区分大小写的比较。如果您知道事实是这样的，您当然可以删除我添加的对 LCase$ 的调用。

我重构了原始代码，为您提供其中一些想法的示例。在不知道你的数据布局的情况下，很难确定这段代码是否 100% 有效，所以我会仔细检查它是否有逻辑错误。但它应该让你开始。

Public Sub PageBreak(ByRef wstWorksheet As Excel.Worksheet, ByVal pctProgress As ProgressCtl.ProgressContro)
        Const lngColSiteID_c As Long = 2&
        Const lngColSiteIDSecondary_c As Long = 1&
        Const lngOffset_c As Long = 1&
        Dim breaksMoved As Boolean
        Dim lngRowBtm As Long
        Dim lngRow As Long
        Dim p As Excel.HPageBreak
        Dim i As Integer
        Dim passes As Long
        Dim lngHBrksUprBnd As Long
        LockInterface True
        ' Marks that no rows/columns are to be repeated on each page
        wstWorksheet.Activate
        wstWorksheet.PageSetup.PrintTitleRows = vbNullString
        wstWorksheet.PageSetup.PrintTitleColumns = vbNullString


        'If this isn't performed beforehand, then the HPageBreaks object isn't available
        '***Not true:)***

        'ActiveWindow.View = xlPageBreakPreview

        'Defaults the print area to be the entire sheet
        wstWorksheet.DisplayPageBreaks = False
        wstWorksheet.PageSetup.PrintArea = vbNullString

        ' add breaks after each site
        lngRowBtm = wstWorksheet.UsedRange.Rows.Count
        For lngRow = 4& To lngRowBtm
            'LCase is to make comparison case insensitive.
            If LCase$(wstWorksheet.Cells(lngRow, lngColSiteID_c).value) = "site id" Then
                wstWorksheet.Cells(lngRow, lngColSiteID_c).PageBreak = xlPageBreakManual
            End If
            pctProgress.ProgressText = ("Row " & CStr(lngRow)) & (" of " & CStr(lngRowBtm))
        Next

        lngHBrksUprBnd = wstWorksheet.HPageBreaks.Count - lngOffset_c
        Do  'Using post test.
            passes = passes + lngOffset_c
            breaksMoved = False
            For i = 1 To lngHBrksUprBnd
                Set p = wstWorksheet.HPageBreaks.Item(i)
                'Move the intended break point up to the first blank section
                lngRow = p.Location.Row - lngOffset_c
                For lngRow = p.Location.Row - lngOffset_c To 1& Step -1&
                    'Checking the LenB is faster than a string check.
                    If LenB(wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c).Formula) = 0& Then
                        lngRow = lngRow - lngOffset_c
                        If LCase$(wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c).value) = "site id" Then
                            breaksMoved = True
                            wstWorksheet.HPageBreaks.Add wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c)
                        End If
                        Exit For
                    End If
                Next
                pctProgress.ProgressText = "Set break point " & (CStr(passes) & "." & CStr(i))
            Next
        Loop While breaksMoved
        LockInterface False
    End Sub

    Private Sub LockInterface(ByVal interfaceOff As Boolean)
        With Excel.Application
            If interfaceOff Then
                .ScreenUpdating = False
                .EnableEvents = False
                .Cursor = xlWait
                .StatusBar = "Working..."
            Else
                .ScreenUpdating = True
                .EnableEvents = True
                .Cursor = xlDefault
                .StatusBar = False
            End If
        End With
    End Sub

I see room for improvement in a couple spots in your code:

Don't access properties that are implemented slowly, like usedrange.rows.count more than once(particularly inside a loop) unless you think they may have changes. Instead store them in a variable.
Don't do text comparisons if you can avoid it (Ex: .Value = ""), instead use the LenB function to check for emptiness, it will execute faster as it's just reading the length of the string header instead of launching into a byte by byte string comparison. (You might enjoy this for reading.)
Don't use "Activate" or "Select" to move around the ActiveCell, just access the range directly.
When looping, structure your loop to have to perform as few tests as possible. If the loop must always execute once, then you want a post-test loop.
Make sure you have the Excel interface locked, as running events and screen-updating etc, can slow your code down a lot. (Especially events.)
Finally, I noticed that you are making assumptions about the case of "Site ID", unless there is no possible way it could be cased otherwise, it's best to do a case insensitive comparison. If you know for a fact that it will be Cased that way you can of course remove the calls to LCase$ that I added.

I refactored the original code to give you an example of some of these ideas. Without knowing your data layout, it's hard to be sure if this code is 100% valid, so I would double check it for logic errors. But it should get you started.

Public Sub PageBreak(ByRef wstWorksheet As Excel.Worksheet, ByVal pctProgress As ProgressCtl.ProgressContro)
        Const lngColSiteID_c As Long = 2&
        Const lngColSiteIDSecondary_c As Long = 1&
        Const lngOffset_c As Long = 1&
        Dim breaksMoved As Boolean
        Dim lngRowBtm As Long
        Dim lngRow As Long
        Dim p As Excel.HPageBreak
        Dim i As Integer
        Dim passes As Long
        Dim lngHBrksUprBnd As Long
        LockInterface True
        ' Marks that no rows/columns are to be repeated on each page
        wstWorksheet.Activate
        wstWorksheet.PageSetup.PrintTitleRows = vbNullString
        wstWorksheet.PageSetup.PrintTitleColumns = vbNullString


        'If this isn't performed beforehand, then the HPageBreaks object isn't available
        '***Not true:)***

        'ActiveWindow.View = xlPageBreakPreview

        'Defaults the print area to be the entire sheet
        wstWorksheet.DisplayPageBreaks = False
        wstWorksheet.PageSetup.PrintArea = vbNullString

        ' add breaks after each site
        lngRowBtm = wstWorksheet.UsedRange.Rows.Count
        For lngRow = 4& To lngRowBtm
            'LCase is to make comparison case insensitive.
            If LCase$(wstWorksheet.Cells(lngRow, lngColSiteID_c).value) = "site id" Then
                wstWorksheet.Cells(lngRow, lngColSiteID_c).PageBreak = xlPageBreakManual
            End If
            pctProgress.ProgressText = ("Row " & CStr(lngRow)) & (" of " & CStr(lngRowBtm))
        Next

        lngHBrksUprBnd = wstWorksheet.HPageBreaks.Count - lngOffset_c
        Do  'Using post test.
            passes = passes + lngOffset_c
            breaksMoved = False
            For i = 1 To lngHBrksUprBnd
                Set p = wstWorksheet.HPageBreaks.Item(i)
                'Move the intended break point up to the first blank section
                lngRow = p.Location.Row - lngOffset_c
                For lngRow = p.Location.Row - lngOffset_c To 1& Step -1&
                    'Checking the LenB is faster than a string check.
                    If LenB(wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c).Formula) = 0& Then
                        lngRow = lngRow - lngOffset_c
                        If LCase$(wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c).value) = "site id" Then
                            breaksMoved = True
                            wstWorksheet.HPageBreaks.Add wstWorksheet.Cells(lngRow, lngColSiteIDSecondary_c)
                        End If
                        Exit For
                    End If
                Next
                pctProgress.ProgressText = "Set break point " & (CStr(passes) & "." & CStr(i))
            Next
        Loop While breaksMoved
        LockInterface False
    End Sub

    Private Sub LockInterface(ByVal interfaceOff As Boolean)
        With Excel.Application
            If interfaceOff Then
                .ScreenUpdating = False
                .EnableEvents = False
                .Cursor = xlWait
                .StatusBar = "Working..."
            Else
                .ScreenUpdating = True
                .EnableEvents = True
                .Cursor = xlDefault
                .StatusBar = False
            End If
        End With
    End Sub

回复收藏 0 原文

醉梦枕江山 2024-07-31 08:55:57

简单的答案是您使用 ActiveCell 和 Select 以及 Activate。 Excel 实际上会在代码运行时选择单元格，从而使代码运行速度变慢（正如您所注意到的）。

我建议使用 Range 作为参考，并在“内存中”进行所有测试。

调暗跟踪范围（dim rngCurrentCell as range）并使用它而不是选择单元格。

因此，对于 Range("A3").Select 代码中第一次出现 Select，您可以将其“设置”为 Set rngCurrentCell = Range(" A3”）。下一个 B4 线也是如此。

然后：

' add breaks after each site
Do While ActiveCell.Row <= wstWorksheet.UsedRange.Rows.Count 

If ActiveCell.FormulaR1C1 = "Site ID" Then
ActiveCell.PageBreak = xlPageBreakManual    
End If    
' Offset the row by one and set our new range
set rngCurrentCell = rngCurrentCell.Offset(1, 0)

pctProgress.ProgressText = "Row " & CStr(ActiveCell.Row) & " of " & CStr(wstWorksheet.UsedRange.Rows.Count)

Loop

等等。

现在要测试值，请使用与 ActiveCell 相同的语法。

如果您有任何疑问，请告诉我。

The easy answer is that you use ActiveCell and Select and Activate. Excel actually selects the cells as your code is running, making the code run slower (as you've noticed).

I would recommend using a Range as a reference and do all the tests "in memory".

Dim a range for tracking (dim rngCurrentCell as range) and use that instead of the selecting the cells.

So, for the first appearance of Select in your code Range("A3").Select, you would 'Set' it as Set rngCurrentCell = Range("A3"). The same for the Next B4 line.

Then:

' add breaks after each site
Do While ActiveCell.Row <= wstWorksheet.UsedRange.Rows.Count 

If ActiveCell.FormulaR1C1 = "Site ID" Then
ActiveCell.PageBreak = xlPageBreakManual    
End If    
' Offset the row by one and set our new range
set rngCurrentCell = rngCurrentCell.Offset(1, 0)

pctProgress.ProgressText = "Row " & CStr(ActiveCell.Row) & " of " & CStr(wstWorksheet.UsedRange.Rows.Count)

Loop

And so forth.

Now to test values use the same syntax as the ActiveCell.

If you have any questions, let me know.

回复收藏 0 原文

佞臣 2024-07-31 08:55:57

我快速查看了您的代码，我的第一个想法是这一行：

pctProgress.ProgressText = "Setting Page Break " & CStr(i) & “的”& CStr(shtDeliveryVariance.HPageBreaks.Count)

可能是造成某些延迟的原因。此代码的位置意味着系统必须重新计算 .Count 值，因为它出现在代码中循环的开头，但这种重新计算在原始代码中不会发生。

其他想法：

根据电子表格的大小，出去重新测量该值可能会减慢速度。为什么不在实际执行添加新中断时手动增加中断计数跟踪变量，而不是让系统对其进行计数，或者摆脱循环中的计数（因为在执行期间无论如何都不会更新显示）这个过程）并将分页符的计数放入其自己的代码段中，该代码段在整个格式化过程结束时贯穿内容，此时可以通过一次调用轻松确定最终的分页符数量？

回复收藏 0 原文

~没有更多了~