VBA Error Handling

In my previous post I showed the difference between

Break on All Errors
Break in Class module
Break on unhandled errors

After looking at each of them we came to the conclusion that we should really use Break on unhandled errors as our default option. See my post
We this option on, however we need to set up o more sofisticated approach to make our life easier.
I looked into the problem and the best approach I could find is the one described in details in the book,
Professional Excel Developers, Chapter 15, VBA Error Handling.
The Error Handling system described there are two: the functin return value and the re-throw method.
I will not go into the details of the two system in this post, I will just add few comments of mine and present you the main ideas.
To start with, The main Vba keyword to deal with Error handling are

1) The object Err
2) On Error Goto Label
3) On Error Resume Next
4) Resume / Resume Next / Resume Label
5) On Error Goto 0

The Err object is a global object whose property are filled by Vba as an error occurs.
Err.Number, Err.Source, Err.Descripton and Err.Raise are by far the most important ones.

Each time we meet a Exit Sub, Exit Fucntion, Exit Property, End Sub, End Function, End Property, Resume and On Error statement the property of the object error are reset. So some time we want to be carefull and store then into some variables.


An Error Handler is a labeled section of a procedure that will run as an error occur.

The Only way into this part of the code is an error, the only way out of this code should be a resume statement. You can see an example here.

Private Sub MySub()
    On Error Goto ErrorHandler  
   'Some code goes here
  
ExitProc:
  Exit Sub
ErrorHandler:
  'Clean up code goes here
  if CentralErrorHandler("Mymodule","MySub") Then
     Stop
     Resume
  else
     Go to ExitProc
  End if

Exit Sub

The second importan principle is the Single Exi Point. Eache time we write a procedure, we need to make sure that there is a single exit point. In this example is ExitProc.

The call to the CentralErrorHandler funcion happens only when we cannot deal with the error within the code, so an exception must be raised. In my personal implementation I actually changed the name of the function from CentraErrorHandler to Exception.LogMe, or if you want Exception.Inizialize.

The CentralErrorHandler function will be responsible for
1) Log erros to a txt log file
2) Activate or deactivate the Debug mode
3) Show a message to the User is we are at an entry point or in Debug mode
4) Re-raise the error is we are not at an entry point or we are not in Debug mode

The call looks like

Public Function CentralErrorHandler(module,proc,entryPoint,showMessage) as boolean

 module and proc tells the CentralErrorHandler what it the source of the error. In this case MyModule:MySub

entryPoint tells it if we are at an entry point.
showMessage tells it if we need a message displayed.


The CentralErroHandler function looks like

Public Function CentralErrorHandler(module,proc,entrypoint,showmessage) as boolean

  'Store the variable of the Global Error message
   Static errMsg as string


   errNum = Err.Num
   errSource = Err.Source
   errDes = Err.Description
   
   'We cannot allow errorn in the CentralErrorHandler
   On Error Resume Next

   errFullSource = module & ":" & proc 
   errLogTxt = errFullSource & " " &  Err.Num & " " & Err.Des
   
   if len(errMsg) = 0 Then errMsg = Err.Description
 
  'Log the errLogTxt Error into a text file

   if entryPoint OR DebugMode then
      if showMessage Then msgbox(errMsg)

      errMsg = vbNullString  

   else

      On Error Goto 0 
      Err.Raise errNum,  errFullSource, errMsg

   end if

End Function

The idea is
1) We store first the property of the Err Object, otherwise they will be reset by the call to Resume
2) We create the new source code and txt to be logged
3) We log the error to the file. We could add: if ToBeLog Then SaveToFile()
4) the errMsg is Static, which means that we will show the original message
5) If we are in DebugMode or at an EntyPoint show a message an reset the string
6) Otherwise re-raise the error

DebugMode is a boolean costant that tells the compiler if we are in DegubMode of not. We can define in at the module level that contains the global error handler.

In few words: when we call the CentralErrorHanlder and we are at an entry point or Debugmode is true a msgbox is shown and the program stops and the errMsg string is cleared. (see the example above on how to call it)
If the debug mode is false and we are not at entry point, a message is re-thrown, with the original Err.Num and Err.Description, but a new Source: MySub:MyModule.

What is an entryPoint?
An entry point is a point from which the user can start execution: menu button, worksheet events...

 For the System to work we need
1) Any Entry point procedure must never call another entry point procedure. If two of them need to run the same code, we can move the code out to a non-entry level procedure
If this happens, we have that the the entry point procedure called, will show a message rather than raising the error up to the caller.

A special case are the Excel User define Functions, which I still need to made my mind up how to treat them.

If we set an Excel UDF as EntryPoint = False and DebugMode = False than an error is re-thrown, so an Excel UDF must have EntryPoint = True

If we set for an Excel UDF EntryPoint = True then as there is an error a msgbox will be diplayed. This is not compliant from what you would expect for an Excel UDF, and image what happens if we had to run hundreds of them.

So we can have Excel UDF EntryPoint = True, showMessage = False.
This gets better, no message anymore.
But if we have 200,000 calls with an error we will log it 200,000 time in the txtfiles, whis is kind of inefficient.
So we could have

ExcelUDF EntryPoint = True, showMessage = False, LogTxt = False

This is getting better, but as you see we have nearly turned off all the Central handling facilities!
No message, no error re-thrown, no txt log. This begs the queston do we need a CentralErrorHandling at all for an Excel UDF?

Lastly if we define an ExcelUDF EntryPoint = true, than we cannot call it from any other part of the code, which is pretty limiting.
So what we can do it to move the code from the entry point to another internal function such that

Public Function MyUDFFunc() as Variant
   MyUDFFunc = MyUDFFuncInternal()
End Function

So that we can set MyUDFFuncInternal EntryPoint = False, in such a case the msgbox will be shown only in Debugmode.    EntryPoint = False, showMessage = True , LogTxt = False

Then MyUDFFunc instead does not need any handler at all.
If we are in DebugMode = True, all the debugging will happen in MyUDFFuncInternal, which will show a message and stop
If we are in DebugMode = False, any error will be logged by MyUDFFuncInternal, whill will no show any message because DebugMode = False, EntryPoint = False. If MyUDFFuncInternal is successfull it will pass the value up to MyUDFFunc. If MyUDFFuncInternal will re-throw an error, MyUDFFunc will just show #N/A, becasue it will exit from execution straightaway.

This could be the best solution: All the Excel UDF don't have any CentralErrorHandler at all, they just delegate the work at some internal fucntion, which wil full support the CentralErrorHandler approach.
This will allow us to reuse them in code easily.
I am still not completely convinced that this is the best approach though.

UPDATE:
After a quick chat with the author of Dailydose of excel, I came to realize that he does not use the Centra Error Handling either for Excel UDF. So my suggested soluiton is the best way I suggest to go.
For Excel UDF no central error Handling. We delegate their functon to some Internal function that implements the CentralErrorHandling Approach. We can set for this functin ErrorLog = False, to prevent to log 100,000 or more calls to failing functions