Error Handling Template

Since Professional Excel Development was published, I’ve been using the error handling method described in chapter 12. It’s very comprehensive and easy to implement. It’s no guarantee that my code is right, of course, but it does guarantee that the end user won’t be plopped into the VBE when an error occurs.

I also MZ-Tools while I’m coding. One of my favorite features is being able to add module headers, procedure headers, and error handling code easily. Below is what I have on the Error Handling tab under MZ-Tools > Options:

If you haven’t read the book, all non-trivial, non-entry point procedures are made into Boolean functions that return TRUE if no error occurs. When I add the above code into a an entry point procedure, I have to delete any line with the variable bReturn in it because that’s only applicable for functions.

The part that I’m not totally clear on is for functions that return something other than Boolean. For instance, I have a function that reads a Jet table, fills an array, and returns a Variant array to be used to populate a ListBox. I can’t make this procedure a Boolean function because I need it to return an array. One option is to treat this function as an entry point, which has the effect of not pushing the error back into the calling procedure. This is the way I’ve been doing it. A second option is to make the function return a Boolean, but pass the “return” variable ByRef. This would change the way I call the procedure, but would still achieve the result I need.

Option 1:

Option 2:

I’m having second thoughts about choosing Option 1. How do you do it?

23 thoughts on “Error Handling Template

  1. Dick, I think the Option 2 is the way to go. I know I asked about this when I first realized I could return multiple results from a function this way (by reading PED of course). I was worried because I had adopted a “never pass variables ByRef” attitude. Anyways, I got a positive response from one of the experts.

  2. I use option 2 as well. I’d like to say I follow the error handling approach at the top of the post, but I’d be lying. When I first got PED I had grand plans to do so, but then I got lazy. I’ve had some issues lately though that have me ready to start. I know MZTools inserts error handling blocks, and I use MZTools extensively, so I have few excuses. I already use a technique to dump info into a log file, so I can track progress, timing, variable values, and the like.

  3. The error handler in MZ-tools is great. I’m amazed at how it inserts the error handling around the procedure body, among other things.

  4. Dick, I also got serious about error handling after reading Stephen’s book. Very similar to your approach but it starts to get interesting when we get to the actual error handler. We do two things. For stand alone apps we have a logging class we plug in that creates a local log file of any error. For “connected” apps, we log all errors straight to a database. In each case MZ tools is invaluable, with its ability to add line numbers to code (and remove them when we are editing), so that we can record the actual line that caused the error. I would typically go for option 2 in your case but I am not sure I would raise an error. More likely would be that we would just handle the false return value with a message of some kind, or let the higher level routine also return a false. If an error did cause the problem, the lower level function should have raised it and logged it already.

  5. I also use the error handler from Professional Excel Development along with MZ-Tools. The majority of the time I use Option 2, but I vary my error handling in a few cases.

    Case 1: If the function has a default fail-safe value, then I will return that.

    Case 2: If I want to return an object, I will set it equal to Nothing and test that condition in the calling function. I have a number of cases where I like to test the validity of an object before using it, and this kills two birds with one stone.

    Case 3: When I want to return a String, I usually just return a zero-length string (“”).

  6. Lost my first comments, so I’ll be briefer this time.

    Option 1 as shown is incomplete. If the current, single argument GetProducts function has its own error handler, then it presumably returns Empty when the query it runs fails for some reason or the query returns no records, then you should be checking whether the return value is Empty.

    Option 2, handling the assignment as a side-effect of the function call, has different problems, not least being that not all arrays or variants that would hold arrays can be passed by reference. For example, GetPtoducts(lProductID, Me.ListBox1.List) doesn’t work.

    While it’s less efficient, it’s probably best to have all true functions (those that need to return something other than success/failure state indicators) return variant type results. That way they can retrurn anything, including error indicators. The calling procedures need to be changed always to check for error return values. In short, a modified/supplemented Option 1.

  7. My general view is that when you need to return 2 values from a function thats a sign to consider using a object instead.
    In a simple case I would go for option 2, but normally everything drops much better when I shift it to a class. The object can then keep track of its status, any error message, as well as doing its actual work.
    I then get to:

    if myobject.getRecordset(Id) then
       ‘ok – do something with it
      doSomeProcess myobject.currentRecordset
       ‘error – msgbox or log myobject.lastError
    end if

    I tend to do most things as a boolean function, except top level callers, and when I am in a real hurry.
    I’m not a big fan of changing function parameters, I would tend to scope the variable at the module level instead. (It is shared across routines so I’d make that explicit).

  8. When writing PED, Rob and I had a long and heated discussion about the merits of these two options. It was Rob’s chapter to write, so we went with his preference to return True/False values from the functions to indicate success or failure, and hence the Option 2 way of passing return values ByRef and using VBA to explicitly re-raise errors up the stack. In pages 426-433, I wrote about the ‘re-throw’ mechanism, whereby the code to re-raise the error up the stack is put in the central error handler itself (rather than the calling procedure). The error then bubbles up the call stack using VB’s natural error handling mechanisms instead of requiring us to use function return values; we can then use Sub, Function and Property procedures and return results from functions rather than a success/failure state.

    I guess it’s a philosophical question. If you’re a natural pessimist, you expect failure and are happy to devote your scarce resources to handling it; if you’re an optimist, you devote your scarce resources to normal successful operations and include discrete error handling for the rare occasion that something might possibly go wrong in the most exceptional circumstances, maybe.

  9. As a rule, I like Subs to be named in verb-object form and Functions to describe what they return. So when you have a GetSomething function, to me that’s already a Sub… But I don’t impose my standards on people who don’t report to me (!) so I’d say you’re already 90% of the way to Option 2. Doing it consistently is far more important than doing it my way. Unless you work for me, of course! (And I’ll admit to being very picky over this).

    All that said, in Stephen’s taxonomy I’m an optimist – I add error processing (pretty much as described) when it proves necessary. If I need to propagate errors back up the call stack then I Raise them. But I mostly write for local users, so I can afford it – if I was remote/disconnected then it would have to be a different story.

    And as I look, PED is on top of the pile of books on my desk…

  10. One follow-up issue with this error handling mechanism – where are others here storing the error log?

    Previously, I just wrote to the program directory (in Program Files), but since the launch of Vista I changed it to a program sub-directory under CSIDL_APPDATA. The problem, though, is that users cannot access this folder without admin permissions. In a corporate environment, this can be a hassle.

    On the other hand, I hate to write this sort of thing to the My Documents folder, so I don’t have a happy solution either way.

    Any thoughts?

  11. Nicholas,

    Presumably CSIDL_APPDATA points to the same directory as the environment variable %APPDATA%, or %USERPROFILE%Application Data.

    No doubt this is an ignorant question, but user processes can write to that directory, and since it’s used in part to store user settings, users would need read/write access to it. Or are you saying that user-initiated processes have higher file system priviledges than users at a console command prompt, for example?

    Whatever. Where does Dr Watson store its crash log files? If your users have Java Runtime Engines, where does it store its update log files? I suspect both are either in the directory pointed to by the %TEMP% environment variable or in subdirectories in it.

    But if the only place from which users can open files is their My Documents folders, and the error logs are intended for users’ benefit, then write them to My Documents. Necessity would dictate your design, no?

  12. On the topic of error logs, my practice has been to write to a text file in a subdirectory of the application. Then, I automate the get external data dialog to display the contents of the text file in a hidden sheet of the application. The sheet is viewable by menu selection, and its data is automatically updated by the menu code.

  13. I too write it to a text file to the foder the addin is in. I figure if the user could put it in that folder, he must have permission to write to it.

    I sometimes include a routine that prepares an email message to a configurable address after an error has been reported. I attach the text file to that msg.

    The mail code depends on my client, if they use Outlook, it uses that, if they have Notes, I include that version of my code.

  14. fzz,

    I typically don’t respond to trolls, but just to clarify a few things I will.

    The CSIDL_* constants are used with SHGetFolderPath and related shell functions available through the API. When you use the %ENVIRONMENT_STRINGS%, I believe this invokes a dll which interprets the string then calls shell32.dll. Using SHGetFolderPath cuts out the middle step.

    AppData is the Microsoft recommended location for program data, whereas My Documents is recommended for user files. I don’t know where the JRE or Dr Watson store their logs, but I imagine DrWatson does event logging anyway, which is different.

    My add-in is a COM add-in that requires admin privileges to install. Many users don’t know where the application’s folder is, and even if they did, Vista no longer permits writing to a Program Files sub-directory from an application with user-level permissions.

    Also, it’s typically used in corporate environments where read/write access to AppData is permitted, but access to it via Windows Explorer is locked down. A savvy user could probably access the folder via the command line, but that would be a technical leap for many users.

    In the end, I’ve decided to continue writing to AppData, but I will add a reader in my next release.

    P.S. Dick – sorry for turning your blog into a forum.

  15. Troll? Fine.

    FWIW, it was never a good idea to write volatile data to subdirectories under Program Files. And it looks like you’re admitting you misstated users needing admin priviledges to read files in their own Application Data folders. Maybe I’m a troll because I point out BS when I see it.

    I haven’t used Vista, but if it works similar to XP for inhibiting access to various directories, it provides every bit as much security as worksheet-level passwords in Excel.

    Where I work, many applications are accessed through Terminal Services. One of those applications is Excel. In Excel on Terminal Server, I can’t launch Explorer from VBA using a Shell statement, but I can launch it from Excel’s File > Open dialog by right clicking on a drive or directory and choosing Explore from the popup menu. Explorer doesn’t show the Terminal Server’s C: drive, but I can enter it in the address bar. I suspect it’d be no more difficult to explore Application Data in Vista.

  16. For cases in which I need a function to return a non boolean value, I raise an error in the error handler of that function. For example, suppose I have a function, vGetAndReturnValue, that is supposed to return a variant value. I use normal error handling within vGetAndReturnValue so if an error occurs in the function it is trapped and an error message is assigned. I also add an Err.Raise glHandled_Error statement within the error handler of vGetAndReturnValue. This makes my function behave like a “built-in” VBA function. I have two options to deal with the error in the calling routine.

    Option 1
    On Error GoTo ErrorHandler:
    vValue = vGetAndReturnValue()

    If an error occurs in the function, control passes to the calling routine’s error handler (but the description, etc. was created in vGetAndReturnValue()

    Option 2
    On Error Resume Next
    vValue = vGetAndReturnValue()
    if Err.Number 0 Then
    Take appropriate action…..
    End if

    With this approach I can modify or add to the error message generated by vGetAndReturnValue or ignore it or take some other action.

  17. I’ll post this under the Error Handling Template rather than starting a new thread.

    Here’s a question for the ‘good-practice’ experts:

    What is recommended and what is essential for correct error trapping?
    Take it that the reader knows the meaning of
    On Error Goto 0|Goto Label|Resume Next
    Resume [Next|Label]

    The first question is on the need for a Resume. Quite a few authors omit one, such as PED page 393 ff. They are assuming that all error handling is cancelled on exit from a procedure. Does that have any implications for the program stack?
    Dick Kusleika uses a Resume

    The second question is on the use of GoTo in body code versus the desirability of one exit point for a procedure for cleanup.


    Style 1 – no cleanup code needed and there is no Resume.

    Function SomeFunc
    On Error GoTo OnError
    Exit Function
    {handling code}
    End Function


    Style 2 – with Resume, without GoTo

    Function SomeFunc
    On Error GoTo OnError
    {cleanup code}
    Exit Function
    {handling code}
    Resume ExitFunc
    End Function


    Style 3 – with Resume, with GoTo

    Function SomeFunc
    On Error GoTo OnError
    GoTo ExitFunc
    {handling code}
    Resume ExitFunc
    {cleanup code}
    End Function


  18. Hi,

    I am aware that this is an old post, however i have a question for you regarding the central error handler used in PED, using the re-throw method.
    If I am writing and Excel UDF, do I still need to call the central error handler?
    Do I need to consider it as an entry point?
    If I set the UDF as Entry Point = True, than a msg will be displayed to the final user in case of error, which is not appropriate. Imaging if I had 2000 function calls on my spreadsheet.
    If I set the UDF as Entry Point = False, than an error will be re-raised and not handled by anybody else. We are already at top level.

    How do you deal with Excel UDF and central error handling?


  19. I don’t use central error handling for functions that are only going to be called from worksheets. I handle all of the errors locally and return the CVErr number that I want based on the situation.

  20. Hi Dick,
    Thanks for your suggestion. I am not going to use it either, it seems the easier solution.

  21. Hi Dick,

    I am still having few problems implementing it. I have an excel UDF function and and entry point procedure that both call another function on which I have a central error handler in place.
    If I am in debug mode, as I run the entry function I want the central error handler to stop where the error occurs, however, I don’t want it to stop if I have hundreds of functions that call it.
    Do you have any advice on how to sort this problem out?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.