excel multiple on error goto

Question

I am trying to get multiple on error statements to work and can't figure it out.

If pdf can be found in local then open, if not then open network location. If there is no PDF in then return msgbox.

Sub Cutsheets()
Application.ScreenUpdating = False

Dim finalrow As Integer

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
On Error GoTo net1

If Not Application.Intersect(ActiveCell, Range("A9:A" & finalrow)) Is Nothing Then
    'Local Location
ActiveWorkbook.FollowHyperlink "C:\Local" & ActiveCell & ".pdf"
Application.SendKeys "{ENTER}", True
End If
Exit Sub

net1:
If Not Application.Intersect(ActiveCell, Range("A9:A" & finalrow)) Is Nothing Then
    'Network Location
On Error GoTo whoa
ActiveWorkbook.FollowHyperlink "P:\Network" & ActiveCell & ".pdf"
Application.SendKeys "{ENTER}", True
End If
Exit Sub

whoa:
MsgBox ("No cutsheet can be found for this item.")

Application.ScreenUpdating = True

End Sub

Also I don't remember why I put sendkeys in there but it doesn't work without it.

You can't clear an exception without a Resume statement, your code ending, or `On Error Goto -1`. See: http://excelmatters.com/2015/03/17/on-error-wtf/ You could just use `Dir` to check if the files exist though. — Rory, Mar 29 '17 at 14:35

score 2 · Answer 1 · edited May 23 '17 at 11:54

2

Using multiple On Error Goto XYZ handlers for control flow is over-complicating some easy validation checks you can do and then simply use the error handling for actual errors.

As @Rory pointed out in a comment you can use the Dir function. You can combine the use of Dir with an If...ElseIf...Else...End If construct to control what you code does:

Option Explicit

Sub Cutsheets()

    On Error GoTo ErrHandler

    Dim strLocalCheck As String
    Dim strNetworkCheck As String

    'check for files - Dir will return "" if file not found
    strLocalCheck = Dir("C:\Local" & ActiveCell.Value & ".pdf")
    strNetworkCheck = Dir("P:\Network" & ActiveCell.Value & ".pdf")

    'control flow
    If strLocalCheck <> "" Then
        ActiveWorkbook.FollowHyperlink strLocalCheck
        Application.SendKeys "{ENTER}", True

    ElseIf strNetworkCheck <> "" Then
        ActiveWorkbook.FollowHyperlink strNetworkCheck
        Application.SendKeys "{ENTER}", True

    Else
        MsgBox "No cutsheet can be found for this item."

    End If

    Exit Sub

ErrHandler:
    Debug.Print "A real error occurred: " & Err.Description

End Sub

edited May 23 '17 at 11:54

Community

1
1

answered Mar 29 '17 at 14:50

Robin Mackenzie

18,801
7
38
56

@ Robin The `MsgBox` will pop up if it cant find anything but it does nothing when it should find a PDF. – Matt Taylor Mar 29 '17 at 15:04
I pretty much followed your logic except for a few checks you are doing... what isn't working? – Robin Mackenzie Mar 29 '17 at 15:06
@ Robin No Hyperlinks are opening. Any idea why? – Matt Taylor Mar 29 '17 at 15:11
1

@MattTaylor why not use a `Shell` command to open the PDF instead of hacking it up with `FollowHyperlink` and `SendKeys`? – Mathieu Guindon Mar 29 '17 at 15:15
Nope - sorry - just copied that bit from your code. I was just trying to show you a better way to do the control flow and avoid the multiple On Error statements. HTH – Robin Mackenzie Mar 29 '17 at 15:17

score 2 · Answer 2 · edited Sep 21 '17 at 18:32

I am trying to get multiple on error statements to work

Don't.

Imagine you're the VBA runtime. You're executing a procedure called Cutsheets, and you come across this instruction:

On Error GoTo net1

From that point on, before you blow up in the user's face, you're going to jump to the net1 label if you ever encounter a run-time error. So you keep running instructions. Eventually you run this line:

ActiveWorkbook.FollowHyperlink "C:\Local" & ActiveCell & ".pdf"

And when the FollowHyperlink method responds with "uh nope, can't do that" and raises a run-time error, your execution context changes.

You're in "error handling" mode.

So you jump to the net1 label. You're in "error handling" mode. There are certain things you can do in "normal execution mode" that you can't (or shouldn't) do in "error handling mode". Raising and handling more errors is one of these things.

On Error GoTo whoa

You're already handling a run-time error: what should you do when you encounter that statement in an error handler subroutine? Jump to the whoa right away?

When the VBA runtime is in "error handling mode", your job as a programmer is to handle runtime errors and do everything you can to get back to "normal execution mode" as soon as possible - and that's normally done with a Resume instruction, or by leaving the current scope.

Copying a chunk of code from the "normal execution path" and trying to run it (slightly altered) in "error handling mode" isn't handling errors and getting back to normal execution mode as soon as possible.

Error handling or not, copy-pasting chunks of code is poorly written code anyway.

Extract a procedure instead:

Private Sub OpenPortableDocumentFile(ByVal path As String)
    On Error GoTo ErrHandler
    ActiveWorkbook.FollowHyperlink path
    Application.SendKeys "{ENTER}", True
    Exit Sub
ErrHandler:
    MsgBox "Could not open file '" & path & "'."
End Sub

Now that that's out of the way, clean up your control flow by verifying if the file exists before you pass an invalid path to the OpenPortableDocumentFile procedure.

The best error handling strategy is to avoid raising run-time errors in the first place.

excel multiple on error goto

2 Answers2