vba 出错时转到不工作；代码中断

On Error GoTo not working; Code breaks

提问by rdevitt

I am writing a VBA function to import data from one table to another in Access. The table I'm importing into has more strict data constraints (i.e. types, size etc.), so I'm expecting a lot of errors.

我正在编写一个 VBA 函数来将数据从一个表导入到另一个表中。我要导入的表具有更严格的数据约束（即类型、大小等），因此我预计会出现很多错误。

Rather than sift through every VBA error that comes up, I want my recordset loop to skip the entire current record and make a note of it in a separate table whenever it runs into an error. So every other line I've inserted On Error GoTo RecordError. But for some reason it's not handling every error. My code just breaks and tells me what the error is. I have the "Break on Unhandled Exceptions" option checked already.

与其筛选出现的每个 VBA 错误，我希望我的记录集循环跳过整个当前记录，并在遇到错误时将其记录在单独的表中。所以我插入的每隔一行On Error GoTo RecordError。但出于某种原因，它并没有处理每个错误。我的代码只是中断并告诉我错误是什么。我已经选中了“中断未处理的异常”选项。

Here's a screenshot that should explain it.

这是一个应该解释它的屏幕截图。

Why would it be breaking on the line immediately following an Error handler?

为什么它会在错误处理程序之后立即中断？

回答by RolandTumble

I think you're not understanding how VB(A) error handling works. Follow these principles:

我认为您不了解 VB(A) 错误处理的工作原理。遵循以下原则：

• An On Error...statement only applies to the routine (Sub or Function) in which it appears (though it will also catch errors that "bubble up" from routines that are called from within the routine in which you use it).
• On Errorsets a state. That is, Once you issue an On Error...it remains in force for the rest of the routine, unless superceded by a new On Error....

• There are four forms of On Error...:

  1. On Error GoTo <label>: <label>must be defined in the same routine, by writing the label name immediately followed by a colon (:) on a line by itself.
  2. On Error Resume: immediately retries the error-throwing statement. Hardly ever used, since it's potentially infinite.
  3. On Error Resume Next: ignores the error & continues. Sometimes useful at the end of routines for cleanup (for instance, if you want to Close a Recordset that may or may not be open). Alternatively, this form can also be used if you check the Errobject immediatelyafter any potentially error-throwing line (if Err.Numberis zero (0), the statement succeeded without throwing an error). This is waytoo much work for most situations.
  4. On Error GoTo 0: turns off error handling.

• 一个On Error...声明仅适用于在它出现的例程（Sub或Function）（尽管它也将赶上的错误，“冒泡”与那些从你所使用的程序中调用程序）。
• On Error设置一个状态。也就是说，一旦您发出 ，On Error...它在例程的其余部分仍然有效，除非被新的On Error....

• 有四种形式On Error...：

  1. On Error GoTo <label>:<label>必须在同一个例程中定义，通过在一行中紧跟一个冒号 (:) 写入标签名称。
  2. On Error Resume: 立即重试抛出错误的语句。几乎从未使用过，因为它可能是无限的。
  3. On Error Resume Next: 忽略错误并继续。有时在用于清理的例程结束时很有用（例如，如果您想关闭可能打开也可能未打开的 Recordset）。或者，如果您在任何可能引发错误的行之后立即检查Err对象（如果为零 (0)，则语句成功且不引发错误），也可以使用此形式。这是方式在大多数情况下太多的工作。Err.Number
  4. On Error GoTo 0: 关闭错误处理。

Given this, it's usual to place the On Error...statement immediately followng the routine's declaration (the Subor Functionstatement), though some people put their Dimstatements in between. If you want to temporarily change the manner of error handling within a routine, put the "new" one right before the code to which it is to apply, and (if used), the "revert" (reissuing the original), right after.

鉴于此，通常将On Error...语句紧跟在例程的声明（SuborFunction语句）之后，尽管有些人将他们的Dim语句置于两者之间。如果您想临时更改例程中的错误处理方式，请将“新”放在要应用的代码之前，并且（如果使用）“还原”（重新发布原始），紧跟在其之后.

Even given all that, I have no idea why it would break on the error-throwing line when "Break on Unhandled Errors" is selected, unless you've managed to confuse it so much that it thinks there's no active error handling (and I'd be surprised if it compiled if that were the case).

即使考虑到所有这些，我也不知道为什么在选择“中断未处理的错误”时它会在错误抛出线上中断，除非您设法混淆了它以至于它认为没有主动错误处理（并且我如果是这样的话，如果它编译会感到惊讶）。

Note that David Heffernan gave you the essential part of this in his answer, and it was here before mine....

请注意，大卫·赫弗南 (David Heffernan) 在他的回答中为您提供了其中的重要部分，而且在我之前就已经出现了....

回答by mendel

The reason it is not working is because you cannot use On Error Goto ... within an error handler.

它不起作用的原因是因为您不能在错误处理程序中使用 On Error Goto ... 。

see http://www.cpearson.com/excel/errorhandling.htm

见http://www.cpearson.com/excel/errorhandling.htm

you cannot use On Error to skip a few lines, instead on error should go to a error handler which then resume's to the desired next line (in your example you could probably get away with one error handler which contains a resume next which will take you back to the next field).

您不能使用 On Error 跳过几行，而在出现错误时应该转到错误处理程序，然后将其恢复到所需的下一行（在您的示例中，您可能会使用一个错误处理程序，其中包含下一个将带您的简历）回到下一个字段）。

thanks to Tim Williams on this question: The second of 2 'On Error goto ' statements gets ignored

感谢蒂姆威廉姆斯在这个问题上：2 'On Error goto' 语句中的第二个被忽略

and BTW ParseInt on a ZIP will destroy zip codes that begin with a 0, zipcodes should probably be treated as text.

并且 BTW ParseInt 在 ZIP 上会破坏以 0 开头的邮政编码，邮政编码可能应该被视为文本。

回答by Andoriyu

Setting the debug mode to 'break on all errors' will make the program execution stop at the line that causes an error even when the error handler has been correctly written. This can be confusing as it appears that error handling is not working.

将调试模式设置为“中断所有错误”将使程序执行在导致错误的行处停止，即使错误处理程序已正确编写。这可能会令人困惑，因为错误处理似乎不起作用。

回答by Philippe Grondier

Error handling with VBA is a real PITA. I'd propose you to have a look at this answer to the 'MS-Access, VBA and error handling' question, and have it adapted to your own situation. You can easily write down some code that will store all your error messages in a table, building a de facto error reporting system.

使用 VBA 进行错误处理是真正的 PITA。我建议您查看“MS-Access、VBA 和错误处理”问题的答案，并使其适应您自己的情况。您可以轻松地写下一些代码，将所有错误消息存储在一个表中，从而构建一个事实上的错误报告系统。

回答by David Heffernan

You need to place the On Errorline before the code whose errors you wish to handle.

您需要将该On Error行放置在您希望处理其错误的代码之前。

What's more you only need to have one On Errorline. The error handler then stays active until the subroutine exits or you execute another On Errorstatement.

更重要的是，您只需要On Error一行。然后错误处理程序保持活动状态，直到子例程退出或您执行另一条On Error语句。

回答by David-W-Fenton

Nobody has really answered your question.

没有人真正回答过你的问题。

Say your code is something like this (a skeletal framework):

假设你的代码是这样的（一个骨架框架）：

Public Sub MySub()
On Error GoTo errHandler
  Dim rs As DAO.Recordset

  Set rs = CurrentDB.OpenRecords([SQL SELECT])
  If rs.RecordCount >0 Then
     rs.MoveFirst
     Do Until rs.EOF
       [do whatever that produces the error]
errSkipToNext:
       rs.MoveNext
     Loop
  End If

exitRoutine:
  If Not (rs Is Nothing) Then
     rs.Close
     Set rs = Nothing
  Exit Sub

errHandler:
  Select Case Err.Number
    Case X, Y, Z ' where these are error numbers you want to ignore
      Err.Clear
      ' do whatever it is you need to do in order to record the offending row
      Call RecordError(rs!PK, Err.Number) ' PK is a field that identifies the bad record
      GoTo errSkipToNext
    Case Else
      MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
         "Error!"
      Resume exitRoutine
  End Select
End Sub

In this code, you use a SELECT CASE in your error handler to decide which errors you want to ignore. In my code framework above, I listed the error numbers as X, Y, Z, but you'd replace that with the real error numbers you want to ignore, instead.

在此代码中，您在错误处理程序中使用 SELECT CASE 来决定要忽略哪些错误。在我上面的代码框架中，我将错误编号列为X, Y, Z，但您可以将其替换为您想要忽略的实际错误编号。

You don't want to ignore every single error because you might end up ignoring important errors elsewhere in your subroutine. If you don't want to figure out what the limited number of errors you want to ignore happen to be, I would suggest that you set a flag at the beginning of the code block that produces the errors you want to ignore, then use an `If bolErrorInCodeBlockToIgnore Then to decide if you're ignoring all errors or not. Something like this:

您不想忽略每一个错误，因为您最终可能会忽略子程序中其他地方的重要错误。如果您不想弄清楚要忽略的有限错误数量是多少，我建议您在产生要忽略的错误的代码块的开头设置一个标志，然后使用`If​​ bolErrorInCodeBlockToIgnore Then 决定是否忽略所有错误。像这样的东西：

Public Sub MySub()
On Error GoTo errHandler
  Dim rs As DAO.Recordset
  Dim bolErrorInCodeBlockToIgnore As Boolean

  Set rs = CurrentDB.OpenRecords([SQL SELECT])
  If rs.RecordCount >0 Then
     rs.MoveFirst
     Do Until rs.EOF
       bolErrorInCodeBlockToIgnore = True
       [do whatever that produces the error]
errSkipToNext:
       rs.MoveNext
     Loop
  End If

exitRoutine:
  If Not (rs Is Nothing) Then
     rs.Close
     Set rs = Nothing
  Exit Sub

errHandler:
  If bolErrorInCodeBlockToIgnore Then
     Err.Clear
     ' do whatever it is you need to do in order to record the offending row
     Call RecordError(rs!PK, Err.Number) ' PK is a field that identifies the bad record
     bolErrorInCodeBlockToIgnore = False
     GoTo errSkipToNext
  Else
     MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
        "Error!"
     Resume exitRoutine
  End If
End Sub

I would much prefer the first, as I'm a firm believer in only ignoring known errors, not any old error that happens. But it might be quite difficult to come up with tests that will produce all the possible errors you want to ignore.

我更喜欢第一个，因为我坚信只忽略已知错误，而不是发生的任何旧错误。但是，想出会产生您想要忽略的所有可能错误的测试可能非常困难。

回答by AndrewM

I have seen error handling fail too. Here is one example.

我也看到错误处理失败。这是一个例子。

Public Function Have(ByVal item As Variant) As Boolean
'Have = Have data.  Simplifies handling nulls and empty strings in validation code

    On Error GoTo Procerr

    If IsNull(item) Then
        Have = False
    **ElseIf Len(Trim(item)) = 0 Then  'Faster than Item <> ""**
        Have = False
    ElseIf item = 0 Then
        Have = False
    Else
        Have = True
    End If

exitproc:
    Exit Function

Procerr:
    'Errors sometimes occur if an unbound control is referenced
    Have = False

End Function

The code sometimes fails on the line flagged with **. Here is the error message.

该代码有时会在标有 ** 的行上失败。这是错误消息。

Note that the error handler has failed. In this case, the form that called the code returned had its recordsource set on the fly to an empty recordset, hence the fields on the screen are not visible. The form is a continuous form, so records and fields are not visible when the form is loaded with an empty recordset. The have() function is not directly called by my code, but somehow seems to be triggered by the me.requery method. The have() has been called hundreds of millions of times in my code but this is the only instance that causes it to fail and the error handler is not involked.

请注意，错误处理程序已失败。在这种情况下，调用返回代码的表单将其记录源即时设置为空记录集，因此屏幕上的字段不可见。表单是一个连续的表单，所以当表单加载一个空的记录集时，记录和字段是不可见的。我的代码没有直接调用 have() 函数，但不知何故似乎是由 me.requery 方法触发的。have() 在我的代码中被调用了数亿次，但这是导致它失败并且错误处理程序没有被调用的唯一实例。

To Lance Roberts re original question. utf-8 unicode can sometimes play havoc with ms-access as it seems to be allow data to be confused for instruction codes (my guess). utf-8 can get into your data if data was originally loaded from a text file. utf-8 with a byte order mark (BoM) is particularly nasty. When you run some procedure that works with the data, strange errors can occur and it may look like your file has become corrupt. In other cases, text handling functions give wrong answers, e.g. Mid() will see the BOM and if you specify a starting point will start at the BOM, but Len() ignores the BOM. I am speculating that if you have this issue, then ms-access may not handle errors correctly. I have had similar issues importing data and importing utf-8 as ANSI was the cause. Note that utf-8 and ANSI are identical most of the time for plain English data so your errors may not be on every line. My errors were mostly with time-date fields. Try exporting the data first and then forcing it to be ANSI and remove any BoM and and reimporting it.

向兰斯·罗伯茨 (Lance Roberts) 提出原始问题。utf-8 unicode 有时会对 ms-access 造成严重破坏，因为它似乎允许将数据与指令代码混淆（我的猜测）。如果数据最初是从文本文件加载的，则 utf-8 可以进入您的数据。带有字节顺序标记 (BoM) 的 utf-8 特别讨厌。当您运行某些处理数据的程序时，可能会发生奇怪的错误，并且看起来您的文件已损坏。在其他情况下，文本处理函数会给出错误的答案，例如 Mid() 将看到 BOM，如果您指定起点，则将从 BOM 开始，但 Len() 会忽略 BOM。我推测如果您遇到此问题，则 ms-access 可能无法正确处理错误。我在导入数据和导入 utf-8 时遇到了类似的问题，因为 ANSI 是原因。请注意，对于纯英文数据，utf-8 和 ANSI 在大多数情况下是相同的，因此您的错误可能不会出现在每一行。我的错误主要是时间-日期字段。尝试先导出数据，然后将其强制为 ANSI 并删除所有 BoM，然后重新导入。