ExcelBanter - error trapping

Useful error handling requires it be at the module level -- in fact,
IMO it should be at at a more atomic level. However, you can create
single 'global' handler -- for what it is worth.

Option Explicit

Sub UseGlobalFaultHandler()
Dim x
x = Application.WorksheetFunction.Match(11, Array(1, 2), 0)
End Sub
Sub Main()
On Error Resume Next
UseGlobalFaultHandler
If Err.Number < 0 Then MsgBox Err.Description & Err.Source
End Sub

The reason the above global error handler is practically useless is
that VBA doesn't provide any information about where the error
occurred, not not even the subroutine that had the problem let alone
the specific line.

A much more structured error handler can be implemented in VBA but is
easier with VB.Net which supports Try...Catch...Finally...End Try One
can simulate this by 'bracketing' code that one expects to fault:

Option Explicit

Sub CauseError()
Dim x
On Error GoTo ErrTrap
x = Application.WorksheetFunction.Match(11, Array(1, 2), 0)
MsgBox x
CleanExit:
'Clean up code here
Exit Sub
ErrTrap:
MsgBox Err.Description
Resume CleanExit
End Sub
Sub Main()
On Error Resume Next
CauseError
If Err.Number < 0 Then MsgBox Err.Description & Err.Source
End Sub

While the above is the closest to the Try...Catch...Finally structure,
it is somewhat convoluted. Nonetheless, it is the 'cleanest' structure
for handling errors in a routine that must clean up after itself.

Finally, for routines that don't involve clean up or for routines that
are do multiple things some of which may not apply under certain
circumstances (for example applying custom formatting to a chart will
depend on what attributes a specific chart will have), I use the
simpler:

Option Explicit

Sub TMPreferredErrHandler()
Dim x
On Error Resume Next
x = Application.WorksheetFunction.Match(11, Array(1, 2), 0)
If IsEmpty(x) Then MsgBox Err.Description Else MsgBox x
On Error GoTo 0
End Sub
Sub Main()
On Error Resume Next
TMPreferredErrHandler
If Err.Number < 0 Then MsgBox Err.Description & Err.Source
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article . com,
says...
Hi,

I have a program with lots of different modules. Can i create another
module that tells the program to save and close with a message to the
user if any of the other modules fail. I have heard of error trapping
but i believe you have to assign this to every module, i would like to
try and avoid this if posible

regards

John