正确处理 VBA (Excel) 中的错误 [英] Properly Handling Errors in VBA (Excel)

问题描述

我使用 VBA 已经有一段时间了，但我对错误处理仍然不太确定.

I've been working with VBA for quite a while now, but I'm still not so sure about Error Handling.

一篇好文章是其中之一CPearson.com

然而，我仍然想知道我过去做 ErrorHandling 的方式是否完全错误:块 1

However I'm still wondering if the way I used to do ErrorHandling was/is completely wrong: Block 1

On Error Goto ErrCatcher
   If UBound(.sortedDates) > 0 Then

       // Code

   Else
ErrCatcher:
       // Code

   End If

if 子句，因为如果它为真，将被执行，如果它失败，Goto 将进入 Else 部分，因为数组的 Ubound 永远不应该为零或更少，没有错误，这个方法有效到目前为止还不错.

The if clause, because if it is true, will be executed and if it fails the Goto will go into the Else-part, since the Ubound of an Array should never be zero or less, without an Error, this method worked quite well so far.

如果我理解正确的话应该是这样的:

If I understood it right it should be like this:

区块 2

On Error Goto ErrCatcher
    If Ubound(.sortedDates) > 0 Then

       // Code
    End If

    Goto hereX

ErrCatcher:
       //Code
    Resume / Resume Next / Resume hereX

hereX:

甚至像这样:第 3 块

On Error Goto ErrCatcher
    If Ubound(.sortedDates) > 0 Then

       // Code
    End If

ErrCatcher:
    If Err.Number <> 0 then
       //Code
    End If

我看到的最常见的方式是，错误Catcher"位于 sub 的末尾，而 Sub 实际上之前以Exit Sub"结束，但是如果如果你反过来跳来阅读代码，sub 是相当大的?

The most common way I see is that one, that the Error "Catcher" is at the end of a sub and the Sub actually ends before with a "Exit Sub", but however isn't it a little confusing if the Sub is quite big if you jump vice versa to read through the code?

区块 4

以下代码的来源:CPearson.com

  On Error Goto ErrHandler:
   N = 1 / 0    ' cause an error
   '
   ' more code
   '
  Exit Sub

  ErrHandler:

   ' error handling code'

   Resume Next

End Sub 

应该像第 3 块那样吗?

Should it be like in Block 3 ?

推荐答案

我绝对不会使用 Block1.在与 Errors 无关的 IF 语句中包含 Error 块似乎不太合适.

I definitely wouldn't use Block1. It doesn't seem right having the Error block in an IF statement unrelated to Errors.

块 2,3 &4 我猜是一个主题的变体.我更喜欢使用 Blocks 3 &4 超过 2 只是因为不喜欢 GOTO 语句；我一般使用Block4方法.这是我用来检查是否添加了 Microsoft ActiveX 数据对象 2.8 库以及如果没有添加或在 2.8 不可用时使用较早版本的代码示例.

Blocks 2,3 & 4 I guess are variations of a theme. I prefer the use of Blocks 3 & 4 over 2 only because of a dislike of the GOTO statement; I generally use the Block4 method. This is one example of code I use to check if the Microsoft ActiveX Data Objects 2.8 Library is added and if not add or use an earlier version if 2.8 is not available.

Option Explicit
Public booRefAdded As Boolean 'one time check for references

Public Sub Add_References()
Dim lngDLLmsadoFIND As Long

If Not booRefAdded Then
    lngDLLmsadoFIND = 28 ' load msado28.tlb, if cannot find step down versions until found

        On Error GoTo RefErr:
            'Add Microsoft ActiveX Data Objects 2.8
            Application.VBE.ActiveVBProject.references.AddFromFile _
            Environ("CommonProgramFiles") + "Systemadomsado" & lngDLLmsadoFIND & ".tlb"

        On Error GoTo 0

    Exit Sub

RefErr:
        Select Case Err.Number
            Case 0
                'no error
            Case 1004
                 'Enable Trust Centre Settings
                 MsgBox ("Certain VBA References are not available, to allow access follow these steps" & Chr(10) & _
                 "Goto Excel Options/Trust Centre/Trust Centre Security/Macro Settings" & Chr(10) & _
                 "1. Tick - 'Disable all macros with notification'" & Chr(10) & _
                 "2. Tick - 'Trust access to the VBA project objects model'")
                 End
            Case 32813
                 'Err.Number 32813 means reference already added
            Case 48
                 'Reference doesn't exist
                 If lngDLLmsadoFIND = 0 Then
                    MsgBox ("Cannot Find Required Reference")
                    End
                Else
                    For lngDLLmsadoFIND = lngDLLmsadoFIND - 1 To 0 Step -1
                           Resume
                    Next lngDLLmsadoFIND
                End If

            Case Else
                 MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
                End
        End Select

        On Error GoTo 0
End If
booRefAdded = TRUE
End Sub

这篇关于正确处理 VBA (Excel) 中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！