正确处理 VBA (Excel) 中的错误

本文介绍了正确处理 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

然而，我仍然想知道我过去做 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

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