Excel 中的 VBA 代码随机停止执行。没有错误信息出现

VBA Code in Excel randomly stops executing. No error messages occur

提问by Mike

Essentially, I have an Updata button that takes information from two columns, in two spreadsheets (within 1 book). The overall goal of this code is to take all the values from one column, and then append the values from the other column below it.

本质上，我有一个更新按钮，可以从两个电子表格（一本书内）的两列中获取信息。此代码的总体目标是获取一列中的所有值，然后将另一列中的值附加到其下方。

Worksheets("Overall Flow").Range("A4:A1004").Value = Worksheets("Active").Range("A2:A1002").Value
Dim i As Integer
For i = 4 To 1004
    If Worksheets("Overall Flow").Range("A" & Trim(str(i))) = "" Then
        Worksheets("Overall Flow").Range("A" & Trim(str(i)) & ":A" & Trim(str(1000 + i))).Value = Worksheets("Inactive").Range("A2:A1002").Value
        i = 1005
    End If
Next

For some reason, the first line executes, and then finishes. When I put break points, then do step-by-step, no other steps happen afterwards.

出于某种原因，第一行执行，然后完成。当我放置断点时，然后一步一步地进行，之后不会发生其他步骤。

When I run the first line individually, it appears to work fine, but not when:

当我单独运行第一行时，它似乎工作正常，但在以下情况下则不然：

Worksheets("Overall Flow").Range("A" & Trim(str(i)) & ":A" & Trim(str(1000 + i))).Value = Worksheets("Inactive").Range("A2:A1002").Value

or

或者

Worksheets("Overall Flow").Range("A4:A1004").Value = Worksheets("Inactive").Range("A2:A1002").Value

is present aftwards.

出现在后面。

采纳答案by transistor1

This is what I am using to test your code. Since I don't know what's in the spreadsheets, I can't reproduce exactly what you're seeing so I'm first putting dummy data into the ranges.

这就是我用来测试您的代码的内容。由于我不知道电子表格中有什么，我无法准确重现您所看到的内容，因此我首先将虚拟数据放入范围内。

For me it is running fine every time, and I've tried it on 2 different computers - Excel 2003, and Excel 2010.

对我来说，它每次都运行良好，我已经在 2 台不同的计算机上尝试过它 - Excel 2003 和 Excel 2010。

I set a breakpoint and stepped with F8, and also Shift F8 and both worked fine.

我设置了一个断点并使用 F8 和 Shift F8 步进，两者都运行良好。

Something may be different with your data (i.e. the first cell being copied over from the inactive sheet is blank and therefore execution stops after processing the first cell -- check that column A4 is not blank), or perhaps some memory has gotten corrupted from having Office being killed.

您的数据可能有所不同（即从非活动工作表复制的第一个单元格是空白的，因此在处理第一个单元格后执行停止 - 检查列 A4 是否不空白），或者某些内存已因办公室被杀。

In a Module I have:

在一个模块中，我有：

Sub test()
    Worksheets("Active").Range("A2:A1002").Value = "active"
    Worksheets("Active").Range("A5").Value = ""
    Worksheets("Inactive").Range("A2:A1002").Value = "inactive"

    Worksheets("Overall Flow").Range("A4:A1004").Value = Worksheets("Active").Range("A2:A1002").Value
    Dim i As Integer
    For i = 4 To 1004
        If Worksheets("Overall Flow").Range("A" & Trim(Str(i))) = "" Then
            Worksheets("Overall Flow").Range("A" & Trim(Str(i)) & ":A" & Trim(Str(1000 + i))).Value = Worksheets("Inactive").Range("A2:A1002").Value
            i = 1005
        End If
    Next
End Sub

Have you tried the same code on another computer?

您是否在另一台计算机上尝试过相同的代码？

回答by aevanko

Update: Tweaked code (now with error checking!)

更新：调整代码（现在有错误检查！）

Main points concerning the current code:

关于当前代码的要点：

• When copying the ACTIVE range, check for last consecutive cell used. This is faster and more effecient than a loop.
• Why are you trimming a number you know will not contain spaces?
• There's no need to set i = 1005, just use Exit For. This is more effecient and clear to the reader what the intention is. I don't use this in the code below since I avoided looping altogether.

• 复制 ACTIVE 范围时，检查最后使用的连续单元格。这比循环更快、更有效。
• 你为什么要修整一个你知道不包含空格的数字？
• 无需设置 i = 1005，只需使用Exit For 即可。这对读者来说更有效，更清楚其意图是什么。我没有在下面的代码中使用它，因为我完全避免了循环。

Here's a different way you can do this without any looping, which I think is more clear and effecient. Try this and see if it works for you:

这是一种无需任何循环即可执行此操作的不同方式，我认为这种方式更清晰有效。试试这个，看看它是否适合你：

Sub test()

Dim lastRow As Long, offSet As Long
lastRow = Worksheets("Active").Range("A2").End(xlDown).row

'Sanity checks
If IsEmpty(Worksheets("Active").Range("A2")) = True Then offSet = 1: lastRow = 2
If lastRow > 1001 Then lastRow = 1002

Worksheets("Overall Flow").Range("A4:A" & lastRow + 2).Value = _
Worksheets("Active").Range("A2:A" & lastRow).Value

If lastRow < 1002 Then
    Worksheets("Overall Flow").Range("A" & lastRow + (3 - offSet) & _
    ":A1004").Value = Worksheets("Inactive").Range("A2:A1002").Value
End If

End Sub

Notes:

注意事项：

• Sanity check 1 is for if A2 is blank in the Active sheet.
• Sanity check 2 is for if there are cells beyond A1002 with values in Active sheet.

• 健全性检查 1 用于如果 A2 在活动工作表中为空白。
• 完整性检查 2 用于检查 A1002 以外的单元格是否在活动表中具有值。

回答by Salvo

Solution to this is very unusual.

对此的解决方案非常不寻常。

CTRL+BREAK CTRL+BREAK CTRL+BREAK ESC

CTRL+中断 CTRL+中断 CTRL+中断 ESC

It just happened to me againg after long time, I was looking for a solution and I came here then this sequence came back to my mind and I tried.

很长一段时间后，它再次发生在我身上，我正在寻找解决方案，我来到这里，然后这个序列又回到了我的脑海中，我尝试了。

It worked for me, I hope this will help someone.

它对我有用，我希望这会对某人有所帮助。

回答by Rafael Emshoff

It has already been mentioned in transistor1's answer, but only as a side comment.

晶体管1的回答中已经提到了它，但只是作为附带评论。

I had a similar problem, that VBA code simply stopped executing in the middle of a function. Just before that it also jumped back a few lines of code. No Error Message was shown.

我有一个类似的问题，VBA 代码只是在函数中间停止执行。就在这之前，它也跳回了几行代码。没有显示错误消息。

I closed all open Excel programs, and upon reopening the File everything worked fine again.

我关闭了所有打开的 Excel 程序，重新打开文件后一切正常。

So my confirmed Answer to this problem is: Corrupted Memory, restart Excel.

所以我对这个问题的确认答案是：Corrupted Memory, restart Excel。

Edit: after doing this, I also encountered the Problem that Visual Basic Editor crashed when I tried uncommenting a particular line. So I created a New Excel file and copied my code. Now I don't have any problems anymore.

编辑：执行此操作后，我还遇到了当我尝试取消注释特定行时 Visual Basic 编辑器崩溃的问题。所以我创建了一个新的 Excel 文件并复制了我的代码。现在我没有任何问题了。

回答by Zeke

I had this issue and I tracked it down to custom VBA functions used in Conditional Formatting that was processed while application.screenupdating was still set to True.

我遇到了这个问题，我将其追溯到在 application.screenupdating 仍设置为 True 时处理的条件格式中使用的自定义 VBA 函数。

I'm not sure how consistent this behaviour is but, when a custom VBA function is referred to in a conditional formatting rule, when the screen updates, it will not step through the code even when employing break points or the debug.assert method. Here's the breakdown of what happened:

我不确定这种行为的一致性如何，但是，当在条件格式规则中引用自定义 VBA 函数时，当屏幕更新时，即使使用断点或 debug.assert 方法，它也不会单步执行代码。这是发生的事情的细分：

Context:

语境：

• 2 open workbooks.
• Conditional formatting and custom function in question were in workbook1.
• The code I was attempting to execute was in workbook2.

• 2个打开的工作簿。
• 有问题的条件格式和自定义函数在 workbook1 中。
• 我试图执行的代码在 workbook2 中。

Process

过程

1. I call a procedure in workbook2.
2. Workbook2's procedure reaches a line executing an autofilter command.
3. Autofilter command triggers a screen updatein all open workbooks (any command that triggers a Worksheet_Change or Worksheet_Calculate event can apply here).
4. Screen update processes the conditional formatting rules, including the rule in workbook1 calling workbook1's custom function.
5. Custom function is run in a 'silent' state (i.e. with no interaction with user, ignoring break points and "debug.assert" calls; this appears to be by design as part of the conditional formatting feature)
6. Custom function finishes execution and ceases all other active code execution.

1. 我在 workbook2 中调用了一个过程。
2. Workbook2 的过程到达执行自动筛选命令的行。
3. Autofilter 命令在所有打开的工作簿中触发屏幕更新（任何触发 Worksheet_Change 或 Worksheet_Calculate 事件的命令都可以在此处应用）。
4. 屏幕更新处理条件格式规则，包括 workbook1 中调用 workbook1 的自定义函数的规则。
5. 自定义函数在“静默”状态下运行（即不与用户交互，忽略断点和“debug.assert”调用；这似乎是作为条件格式功能的一部分设计的）
6. 自定义函数完成执行并停止所有其他活动代码的执行。

I fixed my problem by adding a Application.ScreenUpdating = False line at the start to prevent screen updates and, by extension, conditional format processing (but it's best to keep custom functions away from conditional formatting to begin with).

我通过在开始时添加 Application.ScreenUpdating = False 行来解决我的问题，以防止屏幕更新和扩展，条件格式处理（但最好使自定义函数远离条件格式）。

I'm not sure if this is relevant to your situation at all but I hope it helps somebody.

我不确定这是否与您的情况有关，但我希望它对某人有所帮助。

回答by Robert Cline

I ran into the same problem. I had a sub routine that gave random errors throughout the code without giving error messages. By pressing F8, the code would resume.

我遇到了同样的问题。我有一个子例程，它在整个代码中给出随机错误而不给出错误消息。通过按 F8，代码将恢复。

I found someone had posted a Subroutine he called "ThatCleverDevil" I do not remember the resource or who posted it. It would warn you an error was about to occur. The routine is posted below.

我发现有人发布了一个名为“ThatCleverDevil”的子程序，我不记得该资源或发布者是谁。它会警告您即将发生错误。下面贴出例程。

I split the code into component sub-routines. The short snippits ran with no interruption or erros. I created a subroutine that called each snippit. Errors resumed.

我将代码拆分为组件子例程。短片断运行没有中断或错误。我创建了一个调用每个片段的子程序。错误恢复。

They would run individually, but not all together.

它们会单独运行，但不会全部一起运行。

RESOLUTION: Between called sub-routines, I ran the following line of code:

解决方案：在被调用的子例程之间，我运行了以下代码行：

Application.Wait Second(Now) + 1

Application.Wait Second（现在）+ 1

The code then ran without error.

然后代码运行没有错误。

Thanks to whomever it was that wrote ThatCleverDevil. And special thanks to the coder who wrote about Application.Wait.

感谢编写 ThatCleverDevil 的人。特别感谢编写 Application.Wait 的编码员。

Sub ThatCleverDevil()

On Error GoTo err

出错时转到错误

MsgBox "About to error" err.Raise 12345 MsgBox "Got here after the error"

MsgBox "即将出错" err.Raise 12345 MsgBox "出现错误后到达这里"

Exit Sub

退出子

err: Stop: Resume

错误：停止：继续

End Sub

结束子

Robert

罗伯特

回答by Benton Chambers

VBA simply is prone to this issue. I have used it for years in corproate workflows because it is so hardcoded into lots of things, but if possible I would just consider alternatives. If this an ad-hoc project R will be faster and offer more flexibility. If this is more production oriented and meant to handle large volumes I would consider informatica.

VBA 很容易出现这个问题。我已经在企业工作流程中使用它多年，因为它被硬编码到很多东西中，但如果可能的话，我只会考虑替代方案。如果这是一个临时项目，R 将更快并提供更大的灵活性。如果这更面向生产并且旨在处理大量数据，我会考虑使用 informatica。

回答by Nadzeya Yakimchyk

To improve the performance I called the function DoEvents inside the loop. It solved the problem for me.

为了提高性能，我在循环内调用了函数 DoEvents。它为我解决了这个问题。