VBA错误处理查询 [英] VBA error handling query
问题描述
我想要做的是确保如果在任何时候出现错误，我打开的工作簿和excel实例都正常关闭。
我相信有更好的和更简单的方法来实现这一点，而不是我想出来的。
Sub QOScode（）
错误GoTo失败
Dim app作为新Excel.Application
app.Visible = False'默认情况下，Visible为False，所以这不是必需的
Dim book As Excel.Workbook
Set book = app.Workbooks.Add ActiveWorkbook.Path&\QOS DGL stuff.xlsx）
'设置错误处理，所以如果任何事情发生，Excel的实例与QOS表被优雅地关闭
错误GoTo关闭书
'MsgBox book.Sheets（ACLS）。单元格（3，3）
'做你要做的
'
Closebook：
On Error Resume Next
book.Close SaveChanges：= False
app.Quit
设置app = Nothing
错误GoTo 0
失败：
End Sub
我想要的是一个单一的错误 - 关闭应用和退出子。
任何人都可以提供一些被认为是最佳做法的样本？
干杯
Aaron
所以这段代码以下，当该表不存在时会导致错误，为什么不跳过 book.close 语句，我知道这会引发错误，但是我想它忽略它？
Sub QOScode（）
错误GoTo Closebook
Dim app As New Excel.Application
app.Visible = False
Dim book As Excel.Workbook
Set book = app.Workbooks.Add（ActiveWorkbook.Path&\ QOaS DGL stuff.xlsx）'此表不存在
'
MsgBox book.Sheets（ACLS）。单元格（3，3）
'做你必须做的
'
关闭本页：
Err.Clear
错误恢复下一步
book.Close SaveChanges：= False'对象变量或块变量未设置（错误91）
app.Quit
设置app = Nothing
错误GoTo 0
End Sub
我的2美分错误处理。
您应该总是进行错误处理。
某些原因
1）你不会希望你的应用程序分解，让你的用户挂起！想想一下它会导致他们的沮丧。
2）错误处理并不意味着你试图忽略错误。 >
3）错误处理既不是防御性编程，也不是积极的编程。 IMHO是主动编程。
4）很少有人知道您可以找出导致错误的行。我所说的财产是ERL。考虑这个例子
Sub Sample（）
Dim i As Long
Dim j As Long，k As Long
10 On Error GoTo Whoa
20 i = 5
30 j =Sid
40 k = i * j
50 MsgBox k
60退出子
哇：
70 MsgBox说明：&错误描述& vbNewLine& _
错误编号：&错误编号& vbNewLine& _
行错误& Erl
End Sub
5）在工作表更改事件中，必须做错误处理。想象一下，您将启用事件设置为False并且您的代码中断！代码将不会运行，直到您将事件设置为true
6）我可以继续下去:-)会推荐此链接
主题：Err是人类
链接： http：//www.siddharthrout .com / 2011/08/01 / to-err-is-human /
提示：
使用 MZ工具。这是免费的！
这是我如何编写代码。
Sub QOScode（）
Dim app As New Excel.Application
Dim book As Excel.Workbook
10 On Error GoTo Whoa
20 Set book = app.Workbooks.Open（ActiveWorkbook.Path&\QOS DGL stuff.xlsx）
30 MsgBox book.Sheets（ACLS）。Cells（3，3）
'
'做你必须做的
'
LetsContinue：
40 On Error Resume Next
50 book.Close SaveChanges：= False
60 Set book = Nothing
70 app.Quit
80设置app = Nothing
90 On Error GoTo 0
100退出Sub
哇：
110 MsgBox说明：&错误描述& vbNewLine& _
错误编号：&错误编号& vbNewLine& _
行错误& Erl
120简历LetsContinue
End Sub
I was hoping someone could have a look and tidy this up for me; I have to say error handling is not strong point of mine. I have the code block below and I have been playing around with some error handles but it is not as I really want it.
What I am trying to do is ensure that if at any point there is an error the workbook and excel instance I have opened are closed down gracefully.
I am sure there are much nicer and simpler ways to achieve this than what I have come up with.
Sub QOScode()
On Error GoTo Fail
Dim app As New Excel.Application
app.Visible = False 'Visible is False by default, so this isn't necessary
Dim book As Excel.Workbook
Set book = app.Workbooks.Add(ActiveWorkbook.Path & "\QOS DGL stuff.xlsx")
'set up error handeling so if any thing happens the instance of excel with QOS sheets is closed gracefully
On Error GoTo Closebook
' MsgBox book.Sheets("ACLS").Cells(3, 3)
'Do what you have to do
'
Closebook:
On Error Resume Next
book.Close SaveChanges:=False
app.Quit
Set app = Nothing
On Error GoTo 0
Fail:
End Sub
What I want is a single On error - close app and exit sub.
Can anyone provide a sample of what would be considered best practice for doing this?
Cheers
Aaron
So this code below, when the sheet does not exist it will cause the error, why does it not skip the "book.close" statement, I know this throws an error, but I want it to ignore it?
Sub QOScode()
On Error GoTo Closebook
Dim app As New Excel.Application
app.Visible = False
Dim book As Excel.Workbook
Set book = app.Workbooks.Add(ActiveWorkbook.Path & "\QOaS DGL stuff.xlsx") 'this sheet does not exist
'
MsgBox book.Sheets("ACLS").Cells(3, 3)
'Do what you have to do
'
Closebook:
Err.Clear
On Error Resume Next
book.Close SaveChanges:=False 'Object variable or with block variable not set (error 91)
app.Quit
Set app = Nothing
On Error GoTo 0
End Sub
My 2 cents on Error Handling.
You should always do error handling.
Some of the Reasons
1) You wouldn't want your app to break down and leave your users hanging! Imagine the frustration that it would cause them.
2) Error handling doesn't mean that you are trying to ignore error.
3) Error handling is neither defensive programming or aggressive programming. IMHO it is proactive programming.
4) Very few people are aware that you can find out the line which is causing the error. The property that I am talking about is ERL. Consider this example
Sub Sample()
Dim i As Long
Dim j As Long, k As Long
10 On Error GoTo Whoa
20 i = 5
30 j = "Sid"
40 k = i * j
50 MsgBox k
60 Exit Sub
Whoa:
70 MsgBox "Description : " & Err.Description & vbNewLine & _
"Error Number : " & Err.Number & vbNewLine & _
"Error at Line: " & Erl
End Sub
5) In subs like worksheet change event, it is a must to do error handling. Imagine you have set the Enable Event to False and your code breaks! The code won't run next time till you set the events back to true
6) I can go on and on :-) Would recommend this link
Topic: To ‘Err’ is Human
Link: http://www.siddharthrout.com/2011/08/01/to-err-is-human/
Tip:
Use MZ Tools. It is free!
Here is how I would write your code.
Sub QOScode()
Dim app As New Excel.Application
Dim book As Excel.Workbook
10 On Error GoTo Whoa
20 Set book = app.Workbooks.Open(ActiveWorkbook.Path & "\QOS DGL stuff.xlsx")
30 MsgBox book.Sheets("ACLS").Cells(3, 3)
'
'Do what you have to do
'
LetsContinue:
40 On Error Resume Next
50 book.Close SaveChanges:=False
60 Set book = Nothing
70 app.Quit
80 Set app = Nothing
90 On Error GoTo 0
100 Exit Sub
Whoa:
110 MsgBox "Description : " & Err.Description & vbNewLine & _
"Error Number : " & Err.Number & vbNewLine & _
"Error at Line: " & Erl
120 Resume LetsContinue
End Sub
这篇关于VBA错误处理查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
