excel - Capturing connection errors

Question

Following on from my Connections query, I now want to capture any connections that cannot be reached and throw an error message to state that the relevant connection cannot be reached.

This is the code I have so far, however, I'm not sure if it's capturing any errors:

Private Sub btnRefreshConns_Click()

    On Error GoTo ErrorHandler
    Dim cn As WorkbookConnection

    For Each cn In ActiveWorkbook.Connections
        cn.Refresh
    Next

    Exit Sub

ErrorHandler:
    MsgBox "A connection could not be reached" & cn.Name & ": " & cn.Description

End Sub

Could someone please assist me or let me know if this code would work?

Answer 1

Yes, the sample will catch connection errors. Change the MsgBox line to this to fix the reporting:

MsgBox "A connection could not be reached: " & Err.Number & ": " & Err.Description

Err has info about the latest error.

There's only one catch: all errors will be caught, not just connection errors. That is why I suggest not referring to cn in your error handler: you don't know what state it will be in if an unrelated error happens (say, out of memory - which can happen!). A better approach is to wrap just the code of interest. Inside your For loop:

    On Error GoTo ErrorHandler
    cn.Refresh
    On Error GoTo 0        ' Reset default error handling

Edit: The On Error GoTo 0 unhooks your ErrorHandler and returns to the default error-handling mode, which is to show the error dialog box. The 0 is just a magic number that VBA is programmed to recognize for this purpose. See more details and explanation at Chip Pearson's page on the subject, which is my personal go-to reference.

I have never used On Error GoTo -1, but this answer suggests I'm not missing anything :) . It is apparently used to continue executing normal code after ErrorHandler, but I have always used Resume for that and had no problems.

Caveat: never never never forget the Exit Sub before ErrorHandler. I did once and got stuck in an infinite error loop I could only escape by killing Excel. Resume causes an error if you run it when an error isn't actually being handled and that error threw to the error handler having the Resume... yeah. Ugly.