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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Dim bReturn As Boolean Const sSOURCE As String = “{PROCEDURE_NAME}()” On Error GoTo ErrorHandler bReturn = True {PROCEDURE_BODY} ErrorExit: On Error Resume Next {PROCEDURE_NAME} = bReturn Exit {PROCEDURE_TYPE} ErrorHandler: bReturn = False If bCentralErrorHandler(msMODULE, sSOURCE) Then Stop Resume Else Resume ErrorExit End If |
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:
1 |
Me.ListBox1.List = GetProducts(lProductID) |
Option 2:
1 2 |
If Not GetProducts(lProductID, aProducts()) Then Err.Raise glHandled_Error Me.ListBox1.List = aProducts() |
I’m having second thoughts about choosing Option 1. How do you do it?