VBA 错误处理查询

Question

提问by DevilWAH

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.

我想要做的是确保如果在任何时候出现错误，我打开的工作簿和 excel 实例都会正常关闭。

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.

我想要的是一个 On 错误 - 关闭应用程序并退出子程序。

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?

所以下面这段代码，当工作表不存在时会导致错误，为什么它不跳过“ book.close”语句，我知道这会引发错误，但我希望它忽略它？

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

Answer 1

回答by Siddharth Rout

My 2 cents on Error Handling.

我的错误处理 2 美分。

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

1)你不希望你的应用程序崩溃并让你的用户挂起！想象一下这会给他们带来的挫败感。

2)Error handling doesn't mean that you are trying to ignore error.

2)错误处理并不意味着您试图忽略错误。

3)Error handling is neither defensive programming or aggressive programming. IMHO it is proactive programming.

3)错误处理既不是防御性编程，也不是侵略性编程。恕我直言，这是主动编程。

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

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  Exit Sub
Whoa:
70  MsgBox "Description  : " & Err.Description & vbNewLine & _
    "Error Number : " & Err.Number & vbNewLine & _
    "Error at Line: " & Erl
End Sub

enter image description here

在此处输入图片说明

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

5)在诸如工作表更改事件之类的子程序中，必须进行错误处理。想象一下，您已将启用事件设置为 False 并且您的代码中断了！在您将事件重新设置为 true 之前，代码下次不会运行

6)I can go on and on :-) Would recommend this link

6）我可以继续下去:-)会推荐这个链接

Topic: To ‘Err' is Human

主题：“错误”是人类

Link: http://www.siddharthrout.com/2011/08/01/to-err-is-human/

链接：http: //www.siddharthrout.com/2011/08/01/to-err-is-human/

Tip:

提示：

Use MZ Tools. It is free!

使用MZ 工具。这是免费的！

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

Answer 2

回答by Tony Dallimore

I am not quite sure I understand your objective. If I did, I would probably disagree.

我不太确定我理解你的目标。如果我这样做了，我可能会不同意。

Is this code you are developing? I almost never use error handling in code I am developing. I want the interpreter to stop on the statement that gives the error. I want to understand why that error has occurred. What could I have done to avoid the error? Did I fail to check the file exists? Did I fail to check the path is accessable? I will add the missing code before I do anything else.

这是您正在开发的代码吗？我几乎从不在我正在开发的代码中使用错误处理。我希望解释器在给出错误的语句上停止。我想了解为什么会发生该错误。我可以做些什么来避免错误？我没有检查文件是否存在？我是否没有检查路径是否可以访问？在我做任何其他事情之前，我会添加缺少的代码。

By the time you have finished development, you should plan for there to be no error condition for which you have not included proper code. Of course, this is impossible; you cannot make your code foolproof because fools are so ingenious. The version you release to users must contains error handling.

到您完成开发时，您应该计划没有包含正确代码的错误情况。当然，这是不可能的；你不能让你的代码万无一失，因为傻瓜是如此巧妙。您发布给用户的版本必须包含错误处理。

But you could not release this code to users since it would stop without warning. Would the user guess something had gone wrong with the macro or would they assume this was what was supposed to happen? If they decide the macro has failed what are they going to say to you? "It did not do what I was expecting and I do not know why." What are you going to say back? "What were you doing?" I do not think I have ever had a user give a believable description of what they were doing at the time of a failure. At the very least you want:

但是您无法将此代码发布给用户，因为它会在没有警告的情况下停止。用户是否会猜测宏出了问题，或者他们会认为这是应该发生的事情？如果他们认为宏失败了，他们会告诉你什么？“它没有达到我的预期，我不知道为什么。” 你要说什么？“你在做什么？” 我认为我从来没有让用户对他们在失败时所做的事情做出可信的描述。至少你想要：

Call MsgBox("Sorry I have had an unrecoverable error within QOScode()." & _
            " Please record: " & Err.Number & " " & Err.Description & _
            " and report to extension 1234")

With this, the user does not wonder if something has gone wrong and you know where it went wrong and, with luck, why.

有了这个，用户就不会怀疑是否出了问题，您知道哪里出了问题，幸运的是，为什么会出错。

Answer 3

回答by brettdj

To handle the foreseen issue neatly you can use short error handling to test that the Workbook actually exists (ie If Not Wb Is Nothing Then, if so work on it, with a common ending (ie destroying the object)

为了巧妙地处理可预见的问题，您可以使用简短的错误处理来测试工作簿是否确实存在（即If Not Wb Is Nothing Then，如果存在，则使用共同的结尾（即销毁对象）

The second sample shows how to add additional handling for unforeseen errors once the workbook has been opened.I have used Err.Raiseto create deliberate error to give the user a choice as to how to proceed (close the workbook immediately post error ormake the workbook visible)

第二个示例展示了如何在打开工作簿后为不可预见的错误添加额外的处理。我曾经Err.Raise创建故意的错误，让用户选择如何继续（关闭工作簿立即发布错误或使工作簿可见）

As an aside, don't use Dimand Newtogether. I have re-written
Dim app As New Excel.Application
into
Dim xlApp As Excel.Application Set xlApp = New Excel.Application

顺便说一句，不要一起使用Dim和New。我已经改写
Dim app As New Excel.Application
成
Dim xlApp As Excel.Application Set xlApp = New Excel.Application

1. Handling the no workbook issue

1. 处理无工作簿问题

Sub QOScode()
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    Dim Wb As Excel.Workbook
    On Error Resume Next
    Set Wb = xlApp.Workbooks.Add(ActiveWorkbook.Path & "\QOaS DGL stuff.xlsx")    'this sheet does not exist
    On Error GoTo 0    '

    If Not Wb Is Nothing Then
        MsgBox Wb.Sheets("ACLS").Cells(3, 3)
        'Do what you have to do
        Wb.Close False
    End If

    xlApp.Quit
    Set xlApp = Nothing
End Sub

2. Handling no workbook and other unforseen error which may otherwise leave the worbook open

2. 处理没有工作簿和其他不可预见的错误，否则可能会使工作簿保持打开状态

Sub QOScode2()
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    Dim Wb As Excel.Workbook
    Dim lngChk As Long
    On Error Resume Next
    Set Wb = xlApp.Workbooks.Add(ActiveWorkbook.Path & "\QOaS DGL stuff.xlsx")    'this sheet does not exist
    On Error GoTo 0    '

    If Not Wb Is Nothing Then
        On Error GoTo ProblemHandler
        MsgBox Wb.Sheets("ACLS").Cells(3, 3)
        'Do what you have to do

        'Deliberate error
        Err.Raise 2000, "test code", "Sample Error"
        Wb.Close False
    Else
        MsgBox "Workbook not found,code will now exit"
    End If

    xlApp.Quit
    Set xlApp = Nothing
    Exit Sub

ProblemHandler:
    'Test to see if workbook was still open when error happened
    If Not Wb Is Nothing Then
        lngChk = MsgBox("The code encountered an error" & vbNewLine & Err.Number & vbNewLine & "Do you want to close the file?", vbYesNo, Err.Description)
        If lngChk = vbYes Then
            'close book. Code will proceed to destroy app
            Wb.Close False
        Else
            'make workbook visible and leave code
            Wb.Visible = True
            Exit Sub
        End If
    Else
        MsgBox "The code encountered an error - the file was already closed at this point" & vbNewLine & "Error number " & Err.Number & vbNewLine, Err.Description
    End If
    'destroy app (either if the workbook was closed, or user chose to close it)
    xlApp.Quit
    Set xlApp = Nothing

End Sub

VBA 错误处理查询

提问by DevilWAH

回答by Siddharth Rout

回答by Tony Dallimore

回答by brettdj

相关推荐

最近更新

标签

VBA 错误处理查询

提问by DevilWAH

回答by Siddharth Rout

回答by Tony Dallimore

回答by brettdj

相关推荐

VBA 从用户表单更新公共数组

vba Excel (2003) - 在单元格上自动插入日期

VBA 无法在列上设置日期格式

使用 VBA 将 url 发送到活动的 IE 窗口

相关推荐

最近更新

标签