ERROR HANDLING question

B

Brettjg

I'm trying to write an error handling routine to be used in many workbooks,
and want to keep the code to paste in to an absolute minimum.

The following code WORK PROPERLY:
In the various workbooks:
err_handler:
If Not err_flag = "Y" Then
KeyVal = Application.Run("PERSONAL.xls!ERROR_HANDLER", KeyVal,
Err.Number, Err.Description)
err_flag = "Y"
Resume
Else
On Error GoTo 0
Resume
End If

and in PERSONAL.xls:
Public Function ERROR_HANDLER(KeyVal, err_num, err_desc)
Dim err_type As String
Application.EnableEvents = True
Application.Run "WAV_CRASH"
err_type = "Error " & err_num & " " & err_desc
End Function

but what I would very much like is:
in the various workbooks (THIS DOES NOT WORK PROPERLY)
KeyVal = Application.Run("PERSONAL.xls!ERROR_HANDLER", KeyVal, Err.Number,
Err.Description, err_flag)

and in PERSONAL
Public Function ERROR_HANDLER(KeyVal, err_num, err_desc, err_flag_in)
Dim err_type As String
If Not err_flag_in = "Y" Then
Application.EnableEvents = True
Application.Run "WAV_CRASH"
err_type = "Error " & err_num & " " & err_desc
err_flag_in = "Y"
Resume
Else
On Error GoTo 0
Resume
End If
End Function

but when I run this it crashes on "No error found" (now that's a bloody
first!) and highlghts in the function on the first Resume rather than where
the real error occured. I'm sure it's something to do with passing the info
back and forth between the calling macro and the function, but not sure of
the syntax required.

The next question on this topic is: can I just put the App.run ERROR_HANDLER
into a workbook once only, rather than in every routine? Say somewhere in
ThisWorkbook, so that it will run the function on any error (except those
that I exclude). Regards, Brett.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top