VBA + Excel + Try Catch
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44638867/
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 + Excel + Try Catch
提问by HShhanick
In VBA, I'm doing a simple script that records a version of a spreadsheet being used.
在 VBA 中，我正在做一个简单的脚本来记录正在使用的电子表格的版本。
Private Sub Workbook_Open()
version = "1.0"
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "<WEB SERVICE>"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHTTP.send ("version=" + version)
End Sub
The process works fine, but...
该过程工作正常，但是...
I'm trying to do a try catch so if the web host is offline, instead of showing a run time error I catch it and suppress.
我正在尝试尝试捕获，因此如果 Web 主机处于离线状态，则不会显示运行时错误，而是将其捕获并抑制。
What is the best way to try catch in VBA so there is no error message shown?
在 VBA 中尝试 catch 以便没有显示错误消息的最佳方法是什么？
回答by Trevor
Private Sub Workbook_Open()
on error goto Oops
version = "1.0"
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "<WEB SERVICE>"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHTTP.send ("version=" + version)
exit sub
Oops:
'handle error here
End Sub
If you wanted to, for example, change the URL because of the error, you can do this
例如，如果您因为错误而想更改 URL，则可以执行此操作
Private Sub Workbook_Open()
on error goto Oops
version = "1.0"
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "<WEB SERVICE>"
Send:
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHTTP.send ("version=" + version)
exit sub
Oops:
'handle error here
URL="new URL"
resume Send 'risk of endless loop if the new URL is also bad
End Sub
Also, if your feeling really try/catchy, you can emulate that like this.
另外，如果你的感觉真的很吸引人，你可以像这样模仿。
Private Sub Workbook_Open()
version = "1.0"
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "<WEB SERVICE>"
on error resume next 'be very careful with this, it ignores all errors
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHTTP.send ("version=" + version)
if err <> 0 then
'not 0 means it errored, handle it here
err.clear 'keep in mind this doesn't reset the error handler, any code after this will still ignore errors
end if
End Sub
So extending this to be really hard core...
所以将其扩展为真正的核心......
Private Sub Workbook_Open()
version = "1.0"
on error resume next
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
if err <> 0 then
'unable to create object, give up
err.clear
exit sub
end if
URL = "<WEB SERVICE>"
objHTTP.Open "POST", URL, False
if err <> 0 then
'unable to open request, give up
err.clear
exit sub
end if
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
objHTTP.send ("version=" + version)
if err <> 0 then
'unable to send request, give up
err.clear
exit sub
end if
End Sub
Also worth noting that any errors that happen in an on error gotostyle will not be handled, so if you did this
另外值得注意的是，on error goto样式中发生的任何错误都不会被处理，所以如果你这样做了
private sub MakeError()
dim iTemp as integer
on error goto Oops
iTemp = 5 / 0 'divide by 0 error
exit sub
Oops:
itemp = 4 / 0 'unhandled exception, divide by 0 error
end sub
Will cause an unhandled exception, however
会导致未处理的异常，但是
private sub MakeError()
dim iTemp as integer
on error resume next
iTemp = 5 / 0 'divide by 0 error
if err <> 0 then
err.clear
iTemp = 4 / 0 'divide by 0 error, but still ignored
if err <> 0 then
'another error
end if
end if
end sub
Will not cause any exceptions, since VBA ignored them all.
不会导致任何异常，因为 VBA 忽略了它们。
回答by Sevenfold
Something like this:
像这样的东西：
Try
...
Catch (Exception e)
...
End Try
Might look like this in VBA:
在 VBA 中可能看起来像这样：
' The "Try" part
On Error Resume Next
...
On Error GoTo 0
' The "Catch" part
If Err.Number <> 0 Then
...
End If
However, this form may not be following best practices.
但是，此表单可能未遵循最佳实践。
