Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error trapping
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
error trapping
Nope. Every procedure and function must have error handling. You need to
modify every one to look something like this. sub test on error goto errorhandler: 'your code here exit sub ErrorHandler: call module1.SaveAndClose end sub On the up side this if just a bunch of cutting and pasting, so it should not take too long to do. HTH " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Trapping | Excel Discussion (Misc queries) | |||
Trapping #VALUE! error | Excel Discussion (Misc queries) | |||
Error Trapping from WSH | Excel Discussion (Misc queries) | |||
error trapping | Excel Programming | |||
Error Trapping | Excel Programming |