VBA + Excel +尝试捕获

camsedfj  于 2023-03-09  发布在  其他
关注(0)|答案(3)|浏览(138)

在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

过程正常,但是...
我尝试做一个尝试捕捉,所以如果网络主机离线,而不是显示一个运行时错误,我抓住它,并抑制。
在VBA中尝试catch而不显示错误消息的最佳方法是什么?

dfddblmv

dfddblmv1#

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,则可以执行以下操作

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

此外,如果你的感觉真的尝试/朗朗上口,你可以模仿这样的。

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

所以把它扩展到真正的核心...

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样式中发生的任何错误都不会得到处理,因此如果您这样做

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

将导致未处理的异常

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会忽略所有异常。

shyt4zoc

shyt4zoc2#

大概是这样的

Try
    ...
Catch (Exception e)
    ...
End Try

在VBA中可能如下所示:

' The "Try" part
On Error Resume Next
...
On Error GoTo 0
' The "Catch" part
If Err.Number <> 0 Then
...
End If

但是,此表单可能没有遵循最佳做法。

n3h0vuf2

n3h0vuf23#

这是为了将特雷弗的公认答案推广到那些通过搜索来到这里的人,我还在其中加入了我自己的方法。
此外,我更喜欢只尝试捕获真正需要的东西,所以我也包括了尽快结束错误捕获。

立即就地处理错误,并在以下情况后恢复:

(我添加了这个方法。我认为它是最接近实际Try-Catch块的方法。)

Private Sub UseTryCatchEquivalent()
    DoSolidStuff()

' Try
    On Error Goto Catch
    DoErrorProneStuff1()
    DoErrorProneStuff2() ' Does not get executed when 1 errors.
    On Error Goto 0
' Catch
    If False Then
Catch:
        HandleError()
'        Exit Sub ' Optionally quit sub here.
    End If
' End TryCatch

    DoSolidStuff()
End Sub
立即在底部处理错误并退出子组件:
Private Sub HandleErrorAndExit()
    DoSolidStuff()

    On Error Goto Catch
    DoErrorProneStuff1()
    DoErrorProneStuff2() ' Does not get executed when 1 errors.
    On Error Goto 0

    DoSolidStuff()
    Exit Sub

Catch:
    HandleError()
End Sub
在底部立即处理错误,并在某个点继续:
Private Sub HandleErrorAndResume()
    DoSolidStuff()

    On Error Goto Catch
    DoErrorProneStuff1()
    DoErrorProneStuff2() ' Does not get executed when 1 errors.
    On Error Goto 0

Continue:
    DoSolidStuff()
    Exit Sub

Catch:
    HandleError()
    Resume Continue
End Sub
先忽略所有错误,稍后运行并处理它们:
Private Sub IgnoreErrorAndHandleLater()
    DoSolidStuff()

    ' Everything from here on out will ignore all errors.
    On Error Resume Next
    DoErrorProneStuff1()
    DoErrorProneStuff2() ' Does get executed when 1 errors.
    ' Stop ignoring errors.
    On Error Goto 0 
    If Err.Number <> 0 Then
        HandleError()
        Err.Clear
'        Exit Sub ' Optionally quit sub here.
    End If

    DoSolidStuff()
End Sub
如果要分别处理它们:
Private Sub IgnoreErrorAndHandleLater()
    DoSolidStuff()

    On Error Resume Next
    DoErrorProneStuff1()
    On Error Goto 0 
    If Err.Number <> 0 Then
        HandleError()
        Err.Clear
'        Exit Sub
    End If

    On Error Resume Next
    DoErrorProneStuff2()
    On Error Goto 0 
    If Err.Number <> 0 Then
        HandleError()
        Err.Clear
'        Exit Sub
    End If

    DoSolidStuff()
End Sub

相关问题