VBA + Excel +尝试赶上 [英] VBA + Excel + Try Catch

问题描述

在VBA中，我正在做一个简单的脚本，记录正在使用的电子表格的版本.

In VBA, I'm doing a simple script that records a version of a spreadsheet being used.

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

我正在尝试进行一次尝试捕获，因此，如果Web主机处于脱机状态，则不会显示运行时错误，而是捕获并抑制.

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.

在VBA中尝试捕获的最佳方法是什么，这样就不会显示错误消息了?

What is the best way to try catch in VBA so there is no error message shown?

推荐答案

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   

例如，如果由于错误而想要更改URL，则可以执行此操作

If you wanted to, for example, change the URL because of the error, you can do this

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  

还值得注意的是，不会处理on error goto样式中发生的任何错误，因此如果您这样做

Also worth noting that any errors that happen in an on error goto style will not be handled, so if you did this

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

不会引起任何异常，因为VBA忽略了所有这些异常.

Will not cause any exceptions, since VBA ignored them all.

这篇关于VBA + Excel +尝试赶上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！