错误时 GoTo 不工作；代码中断

发布于 2024-11-04 09:03:45 字数 389 浏览 0 评论 0原文

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

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

这是一个应该解释它的屏幕截图。即使就其本身而言，此屏幕截图对我来说似乎也毫无意义。

为什么它会在出现错误后立即断线处理程序？

原文

分享到QQ

分享到微博

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

发布评论

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

岁月静好 2024-11-11 09:03:45

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

On Error... 语句仅适用于它出现的例程（Sub 或 Function）（尽管它也会捕获从调用的例程中“冒泡”的错误）在您使用它的例程中）。
On Error 设置状态。也就是说，一旦您发出On Error...，它将在例程的其余部分中保持有效，除非被新的On Error... 取代。
On Error... 有四种形式：
1. 出错时转到 ：必须在同一例程中定义，方法是在标签名称后紧跟冒号 (:)自己在一条线上。
2. On Error Resume：立即重试抛出错误的语句。几乎从未使用过，因为它可能是无限的。
3. On Error Resume Next：忽略错误并继续执行继续。有时在清理例程结束时很有用（例如，如果您想关闭可能打开或未打开的记录集）。或者，如果您在任何可能引发错误的行之后立即检查 Err 对象（如果 Err.Number 为零），也可以使用此形式(0)，该语句成功且没有抛出错误）。对于大多数情况来说，这工作量太大了。
4. On Error GoTo 0：关闭错误处理。

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

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

请注意，大卫·赫弗南（David Heffernan）在他的回答中给了你这个问题的核心部分，而它就在我的回答之前......

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

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 Error sets 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 Err object immediately after any potentially error-throwing line (if Err.Number is zero (0), the statement succeeded without throwing an error). This is way too much work for most situations.
4. On Error GoTo 0: turns off error handling.

Given this, it's usual to place the On Error... statement immediately followng the routine's declaration (the Sub or Function statement), though some people put their Dim statements 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.

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....

回复收藏 0 原文

﹂绝世的画 2024-11-11 09:03:45

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

请参阅 http://www.cpearson.com/excel/errorhandling.htm

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

感谢蒂姆·威廉姆斯（Tim Williams）提出的这个问题：2个中的第二个“On Error goto ”语句将被忽略

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

回复收藏 0 原文

我的鱼塘能养鲲 2024-11-11 09:03:45

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

回复收藏 0 原文

淡看悲欢离合 2024-11-11 09:03:45

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

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

回复收藏 0 原文

彻夜缠绵 2024-11-11 09:03:45

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

回复收藏 0 原文

归属感 2024-11-11 09:03:45

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

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

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

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

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

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

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

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.

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:

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.

回复收藏 0 原文

眼泪也成诗 2024-11-11 09:03:45

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

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

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

错误对话框

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

兰斯·罗伯茨（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，然后重新导入。

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.

error dialog

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.

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.

回复收藏 0 原文

~没有更多了~