VBA 如果出错再试一次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45813664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA If Error Try Again
提问by TheNiers
We have automated our reporting processes using SQL Server, Access and Excel. One of our queries however has difficulties running in the morning. Sometimes it gets a timeout error. When this happens, the whole system breaks down and we have to manually continue the processes.
我们使用 SQL Server、Access 和 Excel 自动化了我们的报告流程。然而，我们的一个查询在早上运行时遇到了困难。有时它会收到超时错误。发生这种情况时，整个系统就会崩溃，我们必须手动继续这些过程。
I was hoping to add a VBA loop to allow the query to try again if it happens to fail.
我希望添加一个 VBA 循环，以允许查询在碰巧失败时重试。
I want the system to:
我希望系统：
- Run the query.
- If it fails, wait 5 minutes and try again.
- If it fails 5x in a row, stop the code.
- 运行查询。
- 如果失败，请等待 5 分钟，然后重试。
- 如果连续 5 次失败，请停止代码。
I have written the following code but I have no way of testing it. I was hoping any of you guys could check it out and comment on wether it should work or not.
我已经编写了以下代码，但我无法对其进行测试。我希望你们中的任何人都可以检查它并评论它是否应该起作用。
'Counter for the errors
ErrorCount = 0
GoTo CheckConnection
CheckConnection:
If ErrorCount < 6 Then
'Try to execute the query
db.Execute sqlq
'If it fails, ignore the error message and go to BadConnection
On Error GoTo BadConnection
Else
'If the query failed 5x, just give up and show the error message
db.Execute sqlq
Resume Next
End If
BadConnection:
'Add +1 to the counter
ErrorCount = ErrorCount + 1
'Allow the application to wait for 5 minutes
Application.Wait (Now + TimeValue("0:05:00"))
'Try the query again
GoTo CheckConnection
采纳答案by YowE3K
You aren't resuming in the correct spot, it needs to be in the error-handling code:
您没有在正确的位置恢复，它需要在错误处理代码中：
'Counter for the errors
ErrorCount = 0
GoTo CheckConnection 'This statement is pointless if the label is directly after it
CheckConnection:
'Try to execute the query
' This says to go to BadConnection if an error occurs after it,
' not if an error occurred previously
On Error GoTo BadConnection
db.Execute sqlq
' Start allowing errors to crash Excel again
On Error GoTo 0
'Query worked - continue processing
'....
'...
Exit Sub
'Error handling code
BadConnection:
' Start allowing errors to crash Excel again
On Error GoTo 0
If ErrorCount = 5 Then
'If the query failed 5x, just give up and show the error message
MsgBox "Giving up"
Exit Sub
End If
'Add +1 to the counter
ErrorCount = ErrorCount + 1
'Allow the application to wait for 5 minutes
Application.Wait (Now + TimeValue("0:05:00"))
'Try the query again by Resuming at CheckConnection
Resume CheckConnection
回答by Vityata
This is something that can work out using Recursionand an Optional parameter:
这是可以使用递归和可选参数解决的问题：
Option Explicit
Public Sub TestMe(Optional errorCount As Long = 0)
On Error GoTo TestMe_Error
'Your code just to test it, make an error
Debug.Print errorCount / 0
On Error GoTo 0
Exit Sub
TestMe_Error:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure TestMe"
errorCount = errorCount + 1
Select Case errorCount
Case 1, 2, 3
Application.Wait Now + #12:05:00 AM#
Call TestMe(errorCount)
Case Else 'The 5th time it exits
Exit Sub
End Select
End Sub
Recursion is used, to rerun the code. The times the code is rerun is saved in the parameter errorCount. Thus, the 5. time it exits.
使用递归来重新运行代码。代码重新运行的次数保存在参数中errorCount。因此，5. 时间它退出。
In general, avoid GoToand use it only for error handling. GOTO still considered harmful?
一般情况下，避免GoTo并仅将其用于错误处理。GOTO 还算有害吗？
