title: VBA Excel Error Handling - especially in functions - Professional Excel Development Style
body: <p>I got the book "Professional Excel Development" by Rob Bovey and it is opening up my eyes.</p>
  
  <p>I am refitting my code with error handling. However, there is a lot I don't understand. I especially need to know how to correctly use it in functions.  I use Bovey's rethrow version of the error handler (at bottom).  When I started, I was using the basic boolean (non-rethrow) method and turned my subroutines into boolean functions. (P.S. I am switching back to the boolean method based on the answer.)</p>
  
  <p>I need guidance on how to fit functions into this scheme. I want them to return their real values (a string or double, e.g., or -1 if they fail in some cases) so I can nest them in other functions and not just return an error handling boolean.</p>
  
  <p>This is what a typical subroutine call to bDrawCellBorders(myWS) would look like within an entry point. Sub calls seem to be working well.  (I.e. it is a subroutine that was turned into a function only so it can return a boolean to the error handling scheme.)</p>
  
  <pre><code>Sub UpdateMe()  ' Entry Point
  
      Const sSOURCE As String = "UpdateMe()"
  
      On Error GoTo ErrorHandler
  
      Set myWS = ActiveCell.Worksheet
      Set myRange = ActiveCell
      myWS.Unprotect
  
  ' lots of code
  
      If Not bDrawCellBorders(myWS) Then ERR.Raise glHANDLED_ERROR    ' Call subroutine
  
  ' lots of code
  
  ErrorExit:
      On Error Resume Next
      Application.EnableEvents = True
      myWS.Protect AllowFormattingColumns:=True
      Exit Sub
  
  ErrorHandler:
      If bCentralErrorHandler(msMODULE, sSOURCE,,True) Then  ' Call as Entry Point
          Stop
          Resume
      Else
          Resume ErrorExit
      End If
  End Sub
  </code></pre>
  
  <p>However, I don't know how to extend this to real functions.  This is based off an example in the book that was drawn up for a subroutine, and I just switched it to a function.
  Questions:
   * How do I call it? Is it simply like      x = sngDoSomeMath(17)
   * Will its error handling function properly?
   * Where is the right place or places to call the error handling routine with bReThrow=true?</p>
  
  <pre><code>Public Function sngDoSomeMath(ByVal iNum As Integer) As Single
  
  Dim sngResult As Single
  
  Const sSOURCE As String = "sngDoSomeMath()"
  
  On Error GoTo ErrorHandler
  
  ' example 1, input did not pass validation. don't want to 
  ' go up the error stack but just inform the
  ' calling program that they didn't get a good result from this 
  ' function call so they can do something else
  If iNum &lt;&gt; 42 Then
      sngResult = -1    'function failed because I only like the number 42
      GoTo ExitHere
  End If
  
  ' example 2, true error generated
  sngResult = iNum / 0
  
  sngDoSomeMath = lResult
  
  ExitHere:
      Exit Function
  ErrorHandler:
  
  ' Run cleanup code
  '  ... here if any
  
  ' Then do error handling
  
  If bCentralErrorHandler(msMODULE, sSOURCE, , , True) Then ' The true is for RETHROW
      Stop
      Resume
  End If
  
  End Function
  </code></pre>
  
  <p>The Error Handler Routine:</p>
  
  <pre><code>'
  ' Description:  This module contains the central error
  '               handler and related constant declarations.
  '
  ' Authors:      Rob Bovey, www.appspro.com
  '               Stephen Bullen, www.oaltd.co.uk
  '
  ' Chapter Change Overview
  ' Ch#   Comment
  ' --------------------------------------------------------------
  ' 15    Initial version
  '
  Option Explicit
  Option Private Module
  
  ' **************************************************************
  ' Global Constant Declarations Follow
  ' **************************************************************
  Public Const gbDEBUG_MODE As Boolean = False    ' True enables debug mode, False disables it.
  Public Const glHANDLED_ERROR As Long = 9999     ' Run-time error number for our custom errors.
  Public Const glUSER_CANCEL As Long = 18         ' The error number generated when the user cancels program execution.
  
  
  ' **************************************************************
  ' Module Constant Declarations Follow
  ' **************************************************************
  Private Const msSILENT_ERROR As String = "UserCancel"   ' Used by the central error handler to bail out silently on user cancel.
  Private Const msFILE_ERROR_LOG As String = "Error.log"  ' The name of the file where error messages will be logged to.
  
  
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ' Comments: This is the central error handling procedure for the
  '           program. It logs and displays any run-time errors
  '           that occur during program execution.
  '
  ' Arguments:    sModule         The module in which the error occured.
  '               sProc           The procedure in which the error occured.
  '               sFile           (Optional) For multiple-workbook
  '                               projects this is the name of the
  '                               workbook in which the error occured.
  '               bEntryPoint     (Optional) True if this call is
  '                               being made from an entry point
  '                               procedure. If so, an error message
  '                               will be displayed to the user.
  '
  ' Returns:      Boolean         True if the program is in debug
  '                               mode, False if it is not.
  '
  ' Date          Developer       Chap    Action
  ' --------------------------------------------------------------
  ' 03/30/08      Rob Bovey       Ch15    Initial version
  '
  Public Function bCentralErrorHandler( _
         ByVal sModule As String, _
         ByVal sProc As String, _
         Optional ByVal sFile As String, _
         Optional ByVal bEntryPoint As Boolean, _
         Optional ByVal bReThrow As Boolean = True) As Boolean
  
      Static sErrMsg As String
  
      Dim iFile As Integer
      Dim lErrNum As Long
      Dim sFullSource As String
      Dim sPath As String
      Dim sLogText As String
  
      ' Grab the error info before it's cleared by
      ' On Error Resume Next below.
      lErrNum = ERR.Number
      ' If this is a user cancel, set the silent error flag
      ' message. This will cause the error to be ignored.
      If lErrNum = glUSER_CANCEL Then sErrMsg = msSILENT_ERROR
      ' If this is the originating error, the static error
      ' message variable will be empty. In that case, store
      ' the originating error message in the static variable.
      If Len(sErrMsg) = 0 Then sErrMsg = ERR.Description
  
      ' We cannot allow errors in the central error handler.
      On Error Resume Next
  
      ' Load the default filename if required.
      If Len(sFile) = 0 Then sFile = ThisWorkbook.Name
  
      ' Get the application directory.
      sPath = ThisWorkbook.Path
      If Right$(sPath, 1) &lt;&gt; "\" Then sPath = sPath &amp; "\"
  
      ' Construct the fully-qualified error source name.
      sFullSource = "[" &amp; sFile &amp; "]" &amp; sModule &amp; "." &amp; sProc
  
      ' Create the error text to be logged.
      sLogText = "  " &amp; sFullSource &amp; ", Error " &amp; _
                 CStr(lErrNum) &amp; ": " &amp; sErrMsg
  
      ' Open the log file, write out the error information and
      ' close the log file.
      iFile = FreeFile()
      Open sPath &amp; msFILE_ERROR_LOG For Append As #iFile
      Print #iFile, Format$(Now(), "mm/dd/yy hh:mm:ss"); sLogText
      If bEntryPoint Or Not bReThrow Then Print #iFile,
      Close #iFile
  
      ' Do not display or debug silent errors.
      If sErrMsg &lt;&gt; msSILENT_ERROR Then
  
          ' Show the error message when we reach the entry point
          ' procedure or immediately if we are in debug mode.
          If bEntryPoint Or gbDEBUG_MODE Then
              Application.ScreenUpdating = True
              MsgBox sErrMsg, vbCritical, gsAPP_NAME
              ' Clear the static error message variable once
              ' we've reached the entry point so that we're ready
              ' to handle the next error.
              sErrMsg = vbNullString
          End If
  
          ' The return vale is the debug mode status.
          bCentralErrorHandler = gbDEBUG_MODE
  
      Else
          ' If this is a silent error, clear the static error
          ' message variable when we reach the entry point.
          If bEntryPoint Then sErrMsg = vbNullString
          bCentralErrorHandler = False
      End If
  
      'If we're using re-throw error handling,
      'this is not the entry point and we're not debugging,
      're-raise the error, to be caught in the next procedure
      'up the call stack.
      'Procedures that handle their own errors can call the
      'central error handler with bReThrow = False to log the
      'error, but not re-raise it.
      If bReThrow Then
          If Not bEntryPoint And Not gbDEBUG_MODE Then
              On Error GoTo 0
              ERR.Raise lErrNum, sFullSource, sErrMsg
          End If
      Else
          'Error is being logged and handled,
          'so clear the static error message variable
          sErrMsg = vbNullString
      End If
  
  End Function
  </code></pre>