By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,401 Members | 928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,401 IT Pros & Developers. It's quick & easy.

I'm goofed up in On Error

P: n/a
I thought that I had a grasp of how VBA error handling functioned, but have
just become painfully aware that I don't.

I thought that the "On Error GoTo 0" in the second sub below would turn off
error handling for that procedure. I single stepped the program, it gets an
error on the last line (the Name Workdir... line), the next command to
execute is Resume SkipReport. I know why the error occurs, but I don't
understand how it resumes in the error handler from the other procedure.

Would someone kindly explain this for me.

-----------------------------------------------------
Public Sub BatchPrint()

On Error GoTo BadReport
ChangeName "File1", "File2"
[other code in here removed]
Exit Sub

BadReport:

'====== Continues here
Resume SkipReport
End Sub
-------------------------------------------------------------
Private Sub ChangeName(FromName As String, ToName As String)

On Error Resume Next
Kill ToName

On Error GoTo 0
' ===== Errors here
Name WorkDir & FromName As WorkDir & ToName

End Sub
-------------------------------------------------------
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Ro**********************@newssvr28.news.prodi gy.com...
I thought that I had a grasp of how VBA error handling functioned, but have just become painfully aware that I don't.

I thought that the "On Error GoTo 0" in the second sub below would turn off error handling for that procedure. I single stepped the program, it gets an error on the last line (the Name Workdir... line), the next command to
execute is Resume SkipReport. I know why the error occurs, but I don't
understand how it resumes in the error handler from the other procedure.

Would someone kindly explain this for me.

-----------------------------------------------------
Public Sub BatchPrint()

On Error GoTo BadReport
ChangeName "File1", "File2"
[other code in here removed]
Exit Sub

BadReport:

'====== Continues here
Resume SkipReport
End Sub
-------------------------------------------------------------
Private Sub ChangeName(FromName As String, ToName As String)

On Error Resume Next
Kill ToName

On Error GoTo 0
' ===== Errors here
Name WorkDir & FromName As WorkDir & ToName

End Sub
-------------------------------------------------------


When you say that "On Error GoTo 0" will "turn off error handling for that
procedure", that is correct in the sense of you no longer have any control
over what happens when an error occurs. Access will handle that error for
you - normally simply by displaying an error message. However, in this case
ChangeName has been called from BatchPrint so Access tries to do something
with that un-handled error by going back to BatchPrint and looking for
error-handling code there.

My advice would be to re-write ChangeName from a sub to a function which
returns either true or false but does not display an error message - you can
deal with the function returning false in your BatchPrint. You could write
more detailed error-handling, but here I have used On Error Resume Next to
cover both lines, so either true or false is returned. If this was my code
I might do a bit more checking of the file names and report the error more
specifically (illegal file name, lack of permissions, etc)

Private Function ChangeName(FromName As String, ToName As String) As Boolean
On Error Resume Next
Kill ToName
Name WorkDir & FromName As WorkDir & ToName
If Err.Number = 0 Then ChangeName = True
End Sub

Public Sub BatchPrint()

On Error GoTo BadReport

If ChangeName("File1", "File2") = True Then
Msgbox "Good"
Else
Msgbox "Bad"
End If

....
.....
etc
Fletcher


Nov 12 '05 #2

P: n/a
Think of error handling as a kind of stack.

As On Error statements (apart from On Error GoTo 0) are executed error
handlers are popped onto the stack (for the current procedure there can
onlty be one active error handler), an On Error GoTo 0 pops the error
handler for the procedure off the stack (so error handling for the current
procedure is indeed switched off).

When an error occurs the stack is then used (LIFO) in order to determine how
that error is to be handled.

So in the example you give (I've added line numbers to make it clearer)
' ******************************************
Public Sub BatchPrint()

10 On Error GoTo BadReport
20 ChangeName "File1", "File2"
30 [other code in here removed]
40 Exit Sub

BadReport:

'====== Continues here
50 Resume SkipReport
End Sub
-------------------------------------------------------------
Private Sub ChangeName(FromName As String, ToName As String)

10 On Error Resume Next
20 Kill ToName

30 On Error GoTo 0
' ===== Errors here
40 Name WorkDir & FromName As WorkDir & ToName

End Sub
' ******************************************

At Batchprint 10 an error handler is popped on so the stack could be
represented as
- BatchPrint10

Then another errohandler is popped on at ChangeName 10 so the stack could be
thought of as
- BatchPrint10
- ChangeName 10

Then On Error GoTo 0 at Line ChangeName30 pops the error handler off for
that procedure so the stack now looks like
- BatchPrint10

This then means that when the error occurs at ChangeName40 the current error
handler is at line 10 in BatchPrint. so the instruction at that line for
handling errors is used (i.e. jump to the BadReport label and execute the
staments after that).

Terry

"Randy Harris" <ra***@SpamFree.com> wrote in message
news:Ro**********************@newssvr28.news.prodi gy.com...
I thought that I had a grasp of how VBA error handling functioned, but have just become painfully aware that I don't.

I thought that the "On Error GoTo 0" in the second sub below would turn off error handling for that procedure. I single stepped the program, it gets an error on the last line (the Name Workdir... line), the next command to
execute is Resume SkipReport. I know why the error occurs, but I don't
understand how it resumes in the error handler from the other procedure.

Would someone kindly explain this for me.

-----------------------------------------------------
Public Sub BatchPrint()

On Error GoTo BadReport
ChangeName "File1", "File2"
[other code in here removed]
Exit Sub

BadReport:

'====== Continues here
Resume SkipReport
End Sub
-------------------------------------------------------------
Private Sub ChangeName(FromName As String, ToName As String)

On Error Resume Next
Kill ToName

On Error GoTo 0
' ===== Errors here
Name WorkDir & FromName As WorkDir & ToName

End Sub
-------------------------------------------------------

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.