I do not know how other people here feel, but I've always thought of VBA's error handling as clunky, ugly and most of all, tedious as there's several things to be done manually.
For one thing, I'm spoiled by try/catch blocks. For those not familiar with this structure:
Not only the try/catch block reads much more smoothly than 'OnError GoTo X', it makes the code less spaghetti-y, now that we don't need to make sure there's a Exit Sub before the error handler section to prevent the flow spilling over into the error handler. Furthermore, there's now a centralized location for all clean up code; no need to remember to include the cleanups in the error handler.
But VBA doesn't support try/catch block, so we'll have to do best as we can. That doesn't stop me, however, from wondering if we could make the whole error handling much more simpler and self-maintaining.
Here is my usual error routines:
I usually make use of a error-logging table to give me what modules and what procedures the error occurred, the error number, description, time-stamp and the user.
I just came across an new idea and am considering implementing this: Whenever an error is encountered, do a lookup against a error table to determine how to response to the error (e.g. Resume, Resume Next, Exit Sub, or Quit).
Combined with error log, I could initially set default value to exit sub, and as time progress and I get to know more about errors, modify the response. This is closer to a bit more dynamic error than the present system I have which simply exit the sub (not always desirable).
That said, I can't help but wonder if we can create a class module to extend the Error object's property and methods, and somehow expose more about the error itself, so there's less amount spent of typing custom values (especially sub's names) per new procedures.
Or maybe someone has thought up of a great error-handling system and might want to share how we can all do better.
Any thoughts?
PS: How typical. After I post, I thought of an new idea. The present error handling requires the routine to "fold back" on itself. I realized that the Err object can only contain one error at a time. Therefore, we could simply insert the whole block within a If/Then block and it'd be now a linear construct.
Example:
For one thing, I'm spoiled by try/catch blocks. For those not familiar with this structure:
Code:
Try
'Execute code
'Cause an error, which is called an exception.
Catch ex As Exception
'Handle exception here
Finally
'This executes whether an exception was not thrown or not. This is great place for putting in your clean up code.
End Try
Not only the try/catch block reads much more smoothly than 'OnError GoTo X', it makes the code less spaghetti-y, now that we don't need to make sure there's a Exit Sub before the error handler section to prevent the flow spilling over into the error handler. Furthermore, there's now a centralized location for all clean up code; no need to remember to include the cleanups in the error handler.
But VBA doesn't support try/catch block, so we'll have to do best as we can. That doesn't stop me, however, from wondering if we could make the whole error handling much more simpler and self-maintaining.
Here is my usual error routines:
Code:
Private Sub Something
On Error GoTo PROC_ERR
'Execute my code here
ExitSub:
'Execute my clean up code here
Exit Sub
PROC_ERR
'Catch any errors that may be specific to this sub and handle it locally
'If generic error, call a public sub to record the error
GoTo ExitSub:
End Sub
I usually make use of a error-logging table to give me what modules and what procedures the error occurred, the error number, description, time-stamp and the user.
I just came across an new idea and am considering implementing this: Whenever an error is encountered, do a lookup against a error table to determine how to response to the error (e.g. Resume, Resume Next, Exit Sub, or Quit).
Combined with error log, I could initially set default value to exit sub, and as time progress and I get to know more about errors, modify the response. This is closer to a bit more dynamic error than the present system I have which simply exit the sub (not always desirable).
That said, I can't help but wonder if we can create a class module to extend the Error object's property and methods, and somehow expose more about the error itself, so there's less amount spent of typing custom values (especially sub's names) per new procedures.
Or maybe someone has thought up of a great error-handling system and might want to share how we can all do better.
Any thoughts?
PS: How typical. After I post, I thought of an new idea. The present error handling requires the routine to "fold back" on itself. I realized that the Err object can only contain one error at a time. Therefore, we could simply insert the whole block within a If/Then block and it'd be now a linear construct.
Example:
Code:
Private Sub Something()
On Error GoTo PROC_ERR
'Execute code here
PROC_ERR:
If Not IsNull(Err) 'Not sure if that is a valid, will need to test the correct syntax
'Execute error handling here
End If
'Execute clean up code here
End Sub
Last edited: