VBA's Error Handling: How can it be done better? (1 Viewer)

Banana

split with a cherry atop.
Local time
Today, 06:02
Joined
Sep 1, 2005
Messages
6,318
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:
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:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
I understand what you are saying. Ada and PASCAL have this concept, as well as BASIC on some systems. HP BASIC for OpenVMS, e.g. They "protect" code by doing something like...

When Error In
{code lump goes here}
Use
{an error code lump goes here}
End When

Unfortunately, VBA is what it is. But you can still do this:

On Error Goto GeeWhiz
{code lump goes here}
Goto LumpDone
GeeWhiz:
{an error code lump goes here}
LumpDone:

It almost looks the same.

Your idea about table-driven response strategies is not unheard of either. Just remember that one size rarely fits all, so one table will rarely fit every case. Speaking of case... the error code lump can be ALMOST general and like your lookup case with a Select statement and subsequent cases for the various error codes.

I don't disagree with your contention of what would be nice, but as my old coach used to say, "We gots to play th' game wit' the players what suited up."

Or, when handed a sack of lemons, make lemonade?
 

Banana

split with a cherry atop.
Local time
Today, 06:02
Joined
Sep 1, 2005
Messages
6,318
It almost looks the same.

That's certainly more valid than my earlier idea of using a If/Then block which I wasn't 100% sure if it'd work.

Speaking of which, I'm aware there's debate over whether GOTO construct violate good programming practices as it can lead to spaghetti codes. Of course, some has demonstrated that if used judiciously, it can be quite reasonable. Others still insist that we should be using control blocks to dictate the flow, not manually controlling it ourselves.

Your idea about table-driven response strategies is not unheard of either. Just remember that one size rarely fits all, so one table will rarely fit every case.

True; that's why I said earlier it'd have to be use in junction with error recording. The example I saw defaults to Quit whenever it finds no response. I think that would be incredibly annoying to my users, especially if all they did was enter a bad string in a field or something like that. I'm thinking defaulting to Exit Sub is preferable and as the error log grow, it can be analyzed and response modified as necessary. Quit would only make sense if there's risk of data corruption requiring compact or whatever.

It's a pity that the error handling isn't a simple copy and paste because as I described earlier, you still have to manually enter the name of subroutine and module name if you want to store where the error occurred.

One idea I saw was to declare a private constant within each module containing the module's name. The error table could then refer to the private constant instead of hard-coding form name every time.

Maybe anyone else has thought of how to dynamically retrieve & record the procedures the error occurred in? That would take the error handling up a notch toward a true copy & paste solution and never need to remember to update the correct hard-coded variables every time you paste it somewhere else.

I don't disagree with your contention of what would be nice, but as my old coach used to say, "We gots to play th' game wit' the players what suited up."

So true.

Or, when handed a sack of lemons, make lemonade?

Isn't that what I'm trying to do here? ;)
 

Banana

split with a cherry atop.
Local time
Today, 06:02
Joined
Sep 1, 2005
Messages
6,318
Hmm, here's a great article that I somehow missed in my earlier search.

This is very detailed article, and I particularly like how they showed that call stacks can be exposed using custom procedures. If we can have a record of the call stack, then there's no need to hard-code the module and procedures for error log at all. Not to mention that having the call stack is much more informative as you know from which procedures invoked the present procedures, helpful when it works just fine but not when it's been invoked by this procedures.

The only penalty is that you have to manually call the custom procedures for every start of line. You also have to avoid exiting the procedures prematurely.

Which brings me to another aspects: I mentioned that I dislike having multiple points of exit. For example, I may test something to see if I really should go forward with the procedures as it may dependent on having the necessary variables. A prime example would be a listbox that has a dynamic rowsource that is dependent on the form's recordsource's key. I do not want to call the procedure if the key happens to be null (e.g. we're on the new record), so I usually code thus:

Code:
Private Sub Something()

On Error GoTo PROC_ERR

If IsNull(Me.PrimaryKey) Then
    Exit Sub
End If

'Execute code as normal here

But now after thinking about it, I'm thinking it should be better to do thus:

Code:
If IsNull(Me.PrimaryKey) Then
    GoTo ExitSub
End If

'Execute code here

GoTo ExitSub
ErrorHandler:

'Execute error handling here

ExitSub:

'Execute clean up code

End Sub

This basically guarantee that there is only one point of entry and one point of exit, that it *always* will clean up after itself. In that case, custom procedure for maintaining a call stack will work just fine because it can always be last thing before End Sub.

Any thoughts?
 

Dom DXecutioner

AWF VIP
Local time
Today, 06:02
Joined
Jun 25, 2007
Messages
57
Greetings Gentlemen...

If i may share my 2-cents, as the saying goes.

I personally find nothing wrong with the "On Error Goto Err_Handler" structure. It's not all that difficult to understand or implement, but I'll admit that an extended error handler would be ideal.

Being able to identify at which point the code went to sh*t woulbe ideal, knowing the name of the procedure where the error occured would also be helpful.

Having said that... it is possible to get the line number where the procedure occured by using the Erl function. You could read more details at http://www.fmsinc.com/tpapers/vbacode/index.html

If i'm not mistaken, we could also find the name of the calling procedure by using API and the AddressOf function.

As for the GoTo usage, i suppose the use of this would depend on the programmers skills. If the programmer is not well organized, indeed, this would have the potential of creating "spaghetti-code", and making it virtually impossible to debug.

I use a class module "cException" to log and report the errors...it's no brain surgery, but i usually try to test as much as possible to address errors before the app gets released.

Sample code;


Code:
Private Exception As New cException

Private Sub RandomRoutine()
[COLOR="SeaGreen"]'// enable error handler[/COLOR]
On Error GoTo Err_Handler

	If MyKoolRoutineIsObject = True Then
		DoSomethinNice
	Else
		DoSomethingNotSoNice
	End If

Err_Exit:
	[COLOR="seagreen"]'// clean up[/COLOR]
	Set Exception = Nothing

Err_Handler:
	[COLOR="seagreen"]'// check for possible error using the constants in a separate module bas_GlobalConstants[/COLOR]
	If Err.Number = ERR_OBJECT_NOT_SET Then
		[COLOR="SeaGreen"]'// resume next as this was expected[/COLOR]
		Resume Next
	Else
		With Exception
			.Info Err.Number, Err.Description
			.Routine = "RandomRoutine"
 			[COLOR="seagreen"]'// notify user via message box, this should not set to false
			'// if there's a risk of data lost or other major issues[/COLOR]
                       .Notify = False
			[COLOR="seagreen"]'// this will make access quit after when the Exception object is destroyed
			'// or at Class_Terminate[/COLOR]
			.Quit = False
			.SaveTo = XExceptionXML	[COLOR="SeaGreen"]'// XExceptionTextFile or XExceptionTable[/COLOR]
            [COLOR="seagreen"]'// you could also simplify the code by using the overloaded .Add function
			'// .Add Err.Number, Err.Description, False, False, XExceptionXML[/COLOR]            Resume Err_Exit
		End With
	End If
End Sub

Ok, i'll admit, maybe a bit of an overkill, but i'm happy with it :) This allows me to check the error log, group the errors, address the issue or ignore that error from that point on.
 

Banana

split with a cherry atop.
Local time
Today, 06:02
Joined
Sep 1, 2005
Messages
6,318
Now, I wonder if the Exception object would be a good candidate for maintaining the call stack as I explained in my previous post; make a property that will add to the call stack and if it's short and sweet, it'd be much easier to maintain.

Something like:

Code:
Prviate Ex As clsException

Private Sub Something()

Ex.ACall = "Something" 'Add the Something sub to the call stack

On Error Goto ErrorHandler:

'Execute code

GoTo ExitSub
ErrorHandler:

'Execute error-handling

ExitSub:

'Execute clean up

Ex.SCall 'Remove the most recent procedure from the call stack

End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
Well, here's another chance to go nuts... Bear with me for a while because I have to stray afield to get back to the home pasture here.

In OpenVMS, the concept of a call frame is well defined and there is a highly formalized frame structure that can be used to identify the caller, and the caller's caller, etc. down to the base of the main process. As many calls as you've got, the frame structure supports it. There are ways to reach into the stack with appropriate code to see who called you. There are conventions as to how caller names are represented.

Ever since Windows NT, which was a near-total rewrite of Win 95/98/ME, the structure of Windows internally has resembled OpenVMS. (Funny thing about that...) I would never accuse MS of "stealing a good thing" - heavens forfend that I should even breathe a HINT of such heresy. But I find it significant that one of the main authors of OpenVMS was Dave Cutler, and that the main author of WinNT was ... Dave Cutler.

The concept of Windows user groups - in which you are granted a security token to identify yourself as a member of a group - suspiciously resembles an OpenVMS "Rights Identifier" that has the same general effect.

The concept of an access control list that looks at user and group security tokens to define access permissions looks very much like ... you guessed it! OpenVMS Accesss Control Lists.

Ever hear of Windows Clusters? There was a time when DEC (original authors of OpenVMS) teamed with MS because there was a version of Windows NT that ran on DEC Alpha computers. Fastest Windows you ever saw. During that time, MS strangely became aware of clustering technology that led to something called "Wolfpack" (and other names, not all of which were as nice.) But VMS had clusters in 1981. I know 'cause I ran one.

If you look at some of the internals of NTFS, with file header info, ACLs, retrieval pointers (extents), distinct file ownership issues, etc., you would see that NTFS and OpenVMS's ODS-5 structure is darned familiar.

Given the numerous similarities between WinNT and OpenVMS, and given that Win2K and WinXP are derivatives of WinNT, I would be very surprised to find that stack traceback was NOT similar to OpenVMS abilities.

One more little item for thought. If you want to contemplate Dave Cutler's sneaky nature, think about the fact that WNT is only one letter (in each column) later in collating sequence than VMS. That is, in the ASCII collating sequence using Ada syntax, next("V") = "W", next("M") = "N", etc.

So I would suggest that if you could find appropriate documentation on the subject, there does exist a way to trace back your stacked context. I don't know quite how far back it goes, ... but if you tracked back to your parent process's stack, the bottom entry MIGHT be a call frame from a different CPU mode, suggesting that a user-mode process is actually a semi-formal subroutine of the O/S's task scheduler, which is itself a subroutine of the O/S boot loader. But maybe Dave didn't go quite THAT far when MS stole (uhhh... borrowed) VMS technology.
 

Banana

split with a cherry atop.
Local time
Today, 06:02
Joined
Sep 1, 2005
Messages
6,318
Apparently, if you look into google groups, several VB programmers will say there's no way to retrieve call stack progammatically, as once it's complied, it's very different than what you would see.

However, one has offered a solution, which if I understand it correctly, will add module and sub names all over the codes so there's no hard-coding required, and you can remove it as well. However, this is for a VB IDE, so there's no way of knowing if it would work within VBA.

Here's the google group. Incidentally, there's a good help of scorn poured on an douchebag there so it's a entertaining read as well. :D
 

Banana

split with a cherry atop.
Local time
Today, 06:02
Joined
Sep 1, 2005
Messages
6,318
Hmm... hmmm...

This is very interesting

I suppose that if we jump some hoops, it *just* may be possible to extend the error handling. The idea here is to get the error object to callback every procedures. The procedures would have no error handling, and if callback is used, the error handling is one up in the call stack and therefore will catch the error and handle it.

If it *can* be done, then we can simply extend the object to accept arguments for local error handling, push/pop call stack and then some more.

Hmmm.... what do you think?
 

Dom DXecutioner

AWF VIP
Local time
Today, 06:02
Joined
Jun 25, 2007
Messages
57
Hi Banana,

Were you able to figure out how to extend the error object? Your theory seems like a workable one; not sure if you got to tested, but it would make for interesting reading :)

I have not done any testing my self, mostly because of time constraints and frankly, i'm not sure that i would require such intensive error handler, but i've been wrong before :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:02
Joined
Feb 28, 2001
Messages
26,996
Banana, with regard to the comments on spaghetti code because of GOTO statements... I usually agree with Nicklaus Wirth on nearly everything - but in this case I have a reservation to consider.

If the language is close enough to Pascal or Ada then you have a shot at minimizing your GOTO statements very near to zero. BUT if the language does not support all such structures, or if there are oddball cases, then it is wrong to avoid GOTO statements. If we were running VPA - Visual Pascal for Applications - then GOTO statements could be reduced pretty well. But VBA derives from a language where GOTO is an important element.

In the final analysis, it isn't the language that was ever the problem. It was the programmer whose undisciplined use of GOTO led to more threads of spaghetti than an Italian pasta factory. To remove GOTO from a language because some students don't know how to live without it is, at least to my mind, a somewhat Draconian - or Procrustean - solution.

So long as no person is a perfect programmer and no compiler is a perfect translator of ALL possible programming structures, I think it is wrong to lose the ability of GOTO to get out of multiple layers of slop at once. When you are in the middle of a really complex sequence of IF, SELECT, WHILE or UNTIL, DO, etc. and it is nested ten layers deep in logic segments, you can run into a situation where you want to just jump out of the middle of that mess to some error-abort code. (Like, you do structural sanity checks and all of a sudden you find an abomination. Time to self-destruct and don't wait for ten layers of code to pass up a failing status code.)

When people can be perfectly efficient and write perfect code all of the time, THEN we can make GOTO harder to use. (Hey, did my pragmatist traits come through clearly enough on that one?)
 

Banana

split with a cherry atop.
Local time
Today, 06:02
Joined
Sep 1, 2005
Messages
6,318
Hi Banana,

Were you able to figure out how to extend the error object? Your theory seems like a workable one; not sure if you got to tested, but it would make for interesting reading :)

I have other priorities and the fact that callback function is anything but straightforward has compelled me to put it on back burner for the time being. When I've got everything wrapped, I'll experiment a bit more.

That said, I have found my error handling class to be very fancy. It promises to does a lot of thing for me, for example:
  • Maintain a callstack, which will be dumped into a error log in event of an error
  • Create an automatic error log, which will be stored on a BE
  • In event of network problems, fail-safe to dumping a text file with a prompt for the user to email the text file to administrator (e.g. me)
  • Consult a table of error to determine how to respond (e.g. resume, resume next, exit sub, quit); useful for a bunch of errors that are common re-occurring, pull a string from the table that are more verbose and clearer to users than Access's default error message for use in a message box displayed to the user; default to exit sub and a generic message box if not in the table.
  • In case of error response being Resume, display a box asking the user if they would like to re-try an operation; useful if user may have forgotten to connect the VPN or lost the connection or something that can be fixed easily, with a counter and a cap to prevent code from going into an infinite loop with Resume.
  • Allow me to define a custom error response without looking up the table for a local situation where I may want a different response or implement a local error handler.

I reduced the variables to only one per procedures and one per module; the procedure's name, so the routine now looks something like this:
Code:
Option Compare Database
Option Explicit

Private Const ModuleName As String = "MyModule"

Private Sub Example()
Ex.Push ModuleName, "Example"
On Error GoTo ProcErr

'Code executes
Err.Raise 75

GoTo ExitSub
ProcErr:
'Custom Local Error Handling go here!

'Generic Error Handling!
Select Case Ex.Exception(Err.Number, Err.Description)
    Case ErrContinue
        Resume Next
    Case ErrRetry
        Resume
    Case ErrExit
        Resume ExitSub
    Case ErrQuit
        Quit
End Select

ExitSub:

'Clean-Up Code goes here!

'This should be always the last line prior to End Sub!
Ex.Pop
End Sub

Whenever I add a new procedure (with wizard disabled, of course), I just need to paste in the block after the code execution and type the first two line myself (which is why I instantiated the class as "Ex", shortest name I could possibly give it. :D ).

Overall, pretty pleased with it myself. But there's gonna be the real litmus test when I have the databaee much more mature. It runs fine in few practice test, but practice test seldom are indication of real world application. Let's see.

That said, I hope to reduce the error handler class to something like this:

Code:
Private Sub Example()
Ex.Push 'Procedure name is implicitly collected in the callstack from the callback

'Execute code here

'Handle local error
Ex.Exception(11, Resume Next)
Err.Raise 11

Ex.Pop
End Sub

With callback, the error handling would be completely handled within the error handling class. But that's going to depend on whether such thing can be done.
 
Last edited:

Banana

split with a cherry atop.
Local time
Today, 06:02
Joined
Sep 1, 2005
Messages
6,318
Just one more snag:

In a good designed application, the error handling code should be centralized as much as possible. A blind generic error handler is better than no error handler, but it's always best to have a specific error handler and for error that is beyond a routine's scope, the error should "bubble" upward.

In other words, if a routine get thrown a critical error, it should toss the error upward to the calling routine, telling it "I don't know what $@# is this, see what you can do about it." If it's something the calling routine can handle, do it there, but for something that is application-wide error (e.g. memory is full or there has been a buffer overflow), it should again bubble upward to the highest level routine in where nuclear option is issue; force the application to terminate.

Now, VBA does allow bubbling upward, but it's half assed. For example, you can certainly do this:
Code:
Private Sub Caller()

On Error GoTo GeeWhiz

Call Slave

GeeWhiz:

'Execute error handling here

End Sub

Private Sub Slave()

'Do something where we don't care about the outcome
On Error Resume Next
Err.Raise 11
On Error GoTo 0

'Do something where we DO care about the outcome
Err.Raise 11

End Sub

In this case, the second error will go upward to Caller routine and get handled there. All good. But if you have an intermiediate routine like this:

Code:
Private Sub Caller()

On Error GoTo GeeWhiz

Call MiddleMan

GeeWhiz:

'Error Handling here!

End Sub

Private Sub MiddleMan()

On Error GoTo GeeWhiz

Call Slave

GeeWhiz:

If Err.Number = 9 Then
 'Do something here
Else
 On Error GoTo 0
End If

End Sub

Private Sub Slave ()

'Do something stupid but don't care
On Error Resume Next
Err.Raise 11

'Do something stupid and care.
On Error GoTo 0
Err.Raise 11

End Sub

Here, slave routine can throw the error back to middleman, but middleman has its own error handling, which isn't capable of handling the error. But setting Error GoTo 0 *DOESNT" work here because this will just clear the error and resume the execution, ignoring the error, for bad or worse.

I suppose I could extend the error handling class to manage such case so the exceptions continues to get carried upward as needed and is handled in appropriate places.

Or maybe there's a good reason to NOT do this? I'm quite curious about your feedback; is that important to us to send error to proper routines?
 

GeoMetry

New member
Local time
Today, 09:02
Joined
Jul 24, 2007
Messages
9
I just wish I could figure out a way to have the basic error handling code inserted each time I create a function or a sub. Access fills in the end sub for me why not the error routine I want?
 

Guus2005

AWF VIP
Local time
Today, 14:02
Joined
Jun 26, 2007
Messages
2,645
I just wish I could figure out a way to have the basic error handling code inserted each time I create a function or a sub. Access fills in the end sub for me why not the error routine I want?

There are tools to fill that gap. Look for MZTools.
 

speakers_86

Registered User.
Local time
Today, 09:02
Joined
May 17, 2007
Messages
1,919
Of course this is an old thread, but you might all like to see the functions at this site that scans your code and adds error handling all by itself.
 

ChrisO

Registered User.
Local time
Tomorrow, 00:02
Joined
Apr 30, 2003
Messages
3,202
There seems to be a fascination with ‘code that writes code’ and that fascination can prove to be dangerous.

Another observation is that some people think that VBA is some sort of ‘second cousin’ in a database environment and it doesn’t really matter how it is written provided it fulfils its need; it is not.

The often stated basic premise that all procedures need error handling is simply wrong, no matter who says it. Some procedures are designed to pass back an error to the caller and those procedures must not have error handling. Example: ‘Break in Class Module’ is good for testing but it may be more useful to ‘Break on Unhandled Errors’ at runtime.

Each procedure should be hand written and presented in the best way we can. Each is an entity unto itself and requires individual crafting, not some global ‘slap in the face’ with a quick fix.

It becomes a technical nonsense to run a ‘global fix’ on code of any sort.
The most important statement in that article is to make a backup before attempting it.

-----------------------------

Rather than running the risk of causing damage to one of your applications I’ve attached a demo which has the code to add global error handling. The demo should run fine in Access 2003 before running the code.

Then run subroutine SetAllErrorChecking in module basManualFunctions and see what happens. Clear all the errors and run it again and see what happens.



Chris.
 

Attachments

  • TestIt.zip
    116.4 KB · Views: 248

speakers_86

Registered User.
Local time
Today, 09:02
Joined
May 17, 2007
Messages
1,919
I can see why caution is necessary, but I did try your sample, and the errors were easy to fix. If you have a lot of code, I think the add error handling procedure can help more than it hurts.
 

ChrisO

Registered User.
Local time
Tomorrow, 00:02
Joined
Apr 30, 2003
Messages
3,202
Consider a large program with vast amounts of code…

At first blush it would seem that automating the addition of error handling would be a good thing. But the larger the amount of code then the larger the number of errors which could be induced.

So we have a large program where nobody said there was anything wrong with it, except for the lack of error handling, and we induce a large number of errors into it.

Then we spend a large amount of time trying to remove the large number of errors which were induced adding the error handlers into code which was not faulty in the first place.

I think the logic is a bit screwed.

Chris.
 

speakers_86

Registered User.
Local time
Today, 09:02
Joined
May 17, 2007
Messages
1,919
The errors we are talking about is only the initial On Error line interrupting the procedure header when a continuation (_) is used. Its easier to move the On Error line then it is to add error handling for each procedure, even if you are using MZ tools. And the code posted could be modified to look for the continuation, or even to prompt the developer with the module name and ask for permission before continuing. I can understand the concern, but I can see the value at the same time.
 

Users who are viewing this thread

Top Bottom