问题描述
我使用 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) 中的错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!