vba Excel 宏可在 Debug 中运行，但不能完全运行

Excel macro works in Debug but not in full run

提问by Peage1475

So I have a macro that is designed to insert 4 header rows for each page break in the sheet's print area. It will insert the correct header rows for every page break when I run through it step by step in debug mode, however when it is running by itself it seems to be skipping parts. I have added Sleeps and Debug.Prints in order to figure out where it is going wrong and I still cannot figure it out.

所以我有一个宏，旨在为工作表的打印区域中的每个分页符插入 4 个标题行。当我在调试模式下逐步运行它时，它将为每个分页符插入正确的标题行，但是当它自己运行时，它似乎正在跳过部分。我添加了 Sleeps 和 Debug.Prints 以找出哪里出错了，但我仍然无法弄清楚。

Here is the code:

这是代码：

Sub InsertRowPageBreak()

    Dim WS As Worksheet
    Dim rng As Range
    Dim pb As Variant
    Dim Row As Integer
    Dim OffSet As Integer
    Dim InsertRow As Integer

    Set WS = ThisWorkbook.Worksheets(1)
    WS.Activate
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Dim i As Integer
    i = 1

    For Each pb In WS.HPageBreaks
        Debug.Print "Iteration: " & i
        i = i + 1

        Row = pb.Location.Row
        Range("A" & Row).Select
        Debug.Print "Page Break at Row: " & Row

        If (Range("A" & Row - 2).Value Like "*Date*") Then
            InsertRow = Row - 4
            Range("A" & InsertRow).Select
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
            Debug.Print "Inserting Page Break @ Row: " & InsertRow
        Else
            Sleep 150
            InsertRow = Row - 1
            Debug.Print "Inserting Row " & InsertRow
            If (Range("D" & InsertRow).Value Like "*Compliment*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("1:4").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 1"
            ElseIf (Range("D" & InsertRow).Value Like "*Complaint*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("5:8").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 2"
            ElseIf (Range("D" & InsertRow).Value Like "*Question*") Then
                Sleep 150
                Sheets(2).Activate
                Rows("9:12").Select
                Selection.Copy
                Sheets(1).Activate
                Range("A" & InsertRow).Select
                Selection.Insert Shift:=xlDown
                ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
                Debug.Print "Inserted Header 3"
            End If
            Sleep 250
        End If
        Sleep 250
    Next pb

End Sub 

When I run it in Debug mode the Debug.Print prints out

当我在调试模式下运行它 Debug.Print 打印出来

Iteration: 1
Page Break at Row: 33
Inserting Row 32
Inserted Header 1

Iteration: 2
Page Break at Row: 66
Inserting Row 65
Inserted Header 1

Iteration: 3
Page Break at Row: 94
Inserting Row 93
Inserted Header 2

Iteration: 4
Page Break at Row: 119
Inserting Row 118
Inserted Header 3

And when it runs by it's self

当它自己运行时

Iteration: 1
Page Break at Row: 33
Inserting Row 32
Inserted Header 1

Iteration: 2
Page Break at Row: 35
Inserting Row 34

Iteration: 3
Page Break at Row: 92
Inserting Row 91
Inserted Header 2

Iteration: 4
Page Break at Row: 94
Inserting Row 93

Any suggestions or help would be greatly appreciated.

任何建议或帮助将不胜感激。

Thanks, Kevin

谢谢，凯文

回答by chris neilsen

After inserting a PagebreakExcel needs to repaginate in order to update the HPageBreakscollection.

插入PagebreakExcel 后需要重新分页才能更新HPageBreaks集合。

In order to allow Excel to do this while the code is running, use DoEventsin place of your Sleep's

为了允许 Excel 在代码运行时执行此操作，请使用DoEvents代替您Sleep的

回答by Lopsided

I don't have access Excel at the moment, so I can't exactly answer your question, but if I were you, I would do a few things different because right now your code look very error-prone.

我目前无法访问 Excel，因此我无法准确回答您的问题，但如果我是您，我会做一些不同的事情，因为现在您的代码看起来很容易出错。

First off, any time you add or remove rows to a sheet via a loop, you should have it step backwards. For example:

首先，每当您通过循环向工作表添加或删除行时，您都应该让它后退。例如：

dim i as integer

for i = mySheet.usedrange.rows.count to 1 step -1

  'Put your code here

next i

In this case, you would have to do something like loop through the pagebreaks first and identify the row numbers then loop through the row number in reverse order, but it might help.

在这种情况下，您必须先执行诸如遍历分页符并识别行号然后以相反顺序遍历行号之类的操作，但这可能会有所帮助。

Secondly, assign any worksheets you will be using to a variable, and reference those instead of calling the activate method. I used to use activatetoo, but have since learned it is a rookie mistake and I honestly can't remember why I used it in the first place. For instance, instead of doing this:

其次，将您将使用的任何工作表分配给一个变量，并引用它们而不是调用 activate 方法。我以前也用过activate，但后来知道这是一个新手错误，老实说，我不记得我最初为什么使用它。例如，不要这样做：

Sheets(2).Activate
Rows("1:4").Select
Selection.Copy
Sheets(1).Activate
Range("A" & InsertRow).Select

Do something like this:

做这样的事情：

dim myBook as workbook, a as worksheet, b as worksheet

set myBook = Excel.ActiveWorkbook
set a = myBook.Sheets(1)
set b = myBook.Sheets(2)

b.rows("1:4").Select
Selection.Copy
a.Range("A" & InsertRow).Select

Like I said, I know this doesn't exactly answer your question, and I would have put it in a comment if it wasn't so long, but I really think this will greatly reduce errors in the future and help with your overall code smell. If I had Excel right now I could do more. Sorry I can't be more help, but good luck!

就像我说的，我知道这并不能完全回答您的问题，如果时间不长，我会把它放在评论中，但我真的认为这将大大减少将来的错误并有助于您的整体代码闻。如果我现在有 Excel，我可以做更多。对不起，我帮不上忙，但祝你好运！