Error handling between higher and lower level procedures

Question

I am running into a problem with a lower level UDF kicking an error up to the highest level subroutine's error handling. I thought I could fix this with an On Error GoTo -1, but the problem still occurs.

According to Microsoft, "without an On Error GoTo -1 statement, an exception is automatically disabled when a procedure is exited" (https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/on-error-statement). Does this mean that when I call a lower level subroutine I am "exiting" a procedure and nullifying the On Error GoTo -1 statement?

As an example, this is for a subroutine that takes an excel document and combines it with jpg images into a pdf. The jpg images need to be ordered by the number that ends their file name (i.e. Photomics0). Occasionally the file name will not have a number at the end of the file name. I want this to cause the normal error that should occur.

The highest level subroutine has the following error handling:

Sub Example()

Do While t = 1

On Error GoTo errorMSG1
Set wb2 = Workbooks(Copyrange)
On Error GoTo -1
'
' more code here & call to other subroutines
'
Exit Sub

Loop

errorMSG1:
    On Error GoTo -1
'
' Code that notifies user that they don't have the excel document open in 
' background and opens it for them
'
End Sub

The lowest level UDF (3 levels down) runs into an error with the code:

retvalint = CLng(retval)

due to the fact that retval should be the number at the end of the jpg file name. With no number retval is empty and throws an error causing the code to jump to errorMSG1, which I don't want it to do.

I should also say that this always happens when the original error I am trying to trap does not occur. I have not tested to see if this happens when the original error does occurs.

I have tried throwing On Error GoTo -1 absolutely everywhere in all levels of my subroutines and functions, with no avail.

Also, on a side note, is this an example of letting errors "bubble up"?

EDIT: I should also say that I previously tried using On Error GoTo 0, but the higher level sub has a large Do Loop in it. I noticed that when the original error I was trying to catch occurred on the >=2 iteration it would spit out the the original Runtime Error error.

Note - that is the VB.Net link. [This](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/on-error-statement) is the VBA one. — BigBen, Oct 19 '19 at 21:22
Oh, shows how little I know. Didn't know those were different. — B. Moore, Oct 19 '19 at 21:29
Also useful: https://stackoverflow.com/questions/14158901/difference-between-on-error-goto-0-and-on-error-goto-1-vba — BigBen, Oct 19 '19 at 21:34

score 0 · Answer 1 · answered Oct 20 '19 at 03:12

I don't understand the documentation for this, but I figured it out from testing.

On Error GoTo 0 stops using the current Error handler. On Error GoTo -1 clears the current error.

Here's an example of this:

Sub OnErrorTest()
    On Error GoTo myErrHnd
        Err.Raise 42
        myErrorSub
    On Error GoTo 0
        Err.Raise 44
    Exit Sub

myErrHnd:
    Debug.Print ("Error: " & Err.Number)
    Debug.Print ("Description: " & Err.Description & vbNewLine)
    Resume Next
End Sub

Sub myErrorSub()
    On Error GoTo myOtherErrHnd
    Workbooks("").Close
    Exit Sub

myOtherErrHnd:
    On Error GoTo -1 ' aka Err.Clear
End Sub

So to fix the issue you're having, it looks like you need to switch to On Error GoTo 0 after setting the workbook. Normally, errors will bubble up if there is an error handler set in any of their parent functions.

Error handling between higher and lower level procedures

1 Answers1