Excel VBA error handling - especially in features - Excel Professional Development Style

Question

I got Excel Professional Development, Rob Bowie, and it opened my eyes.

I am fixing my error handling code. However, I do not understand much. I especially need to know how to properly use it in functions. I am using the Bovey rethrow version of the error handler (below). When I started, I used the basic boolean (non-rethrow) method and turned the routines into logical functions. (PS I am returning to a logical method based on the answer.)

I need to be guided by how to embed functions in this circuit. I want them to return their real values ​​(a string or double, for example, or -1 if they were not executed in some cases), so I can nest them in other functions, and not just return the logical error processing.

This is what a typical bDrawCellBorders (myWS) routine call will look like inside an entry point. Subheadings seem to work well. (Ie is a routine that has been turned into a function only so that it can return a boolean value to an error handling scheme.)

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 

However, I do not know how to extend this to real functions. This is based on an example in a book that was compiled for a subroutine, and I just switched it to a function. Questions: * What should I call it? It's just like x = sngDoSomeMath (17) * Will the error handling function work correctly? * Where is the appropriate place or places to call the error handling procedure using bReThrow = true?

 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 <> 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 

Error Handler Procedure:

 ' ' 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 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) <> "\" Then sPath = sPath & "\" ' Construct the fully-qualified error source name. sFullSource = "[" & sFile & "]" & sModule & "." & sProc ' Create the error text to be logged. sLogText = " " & sFullSource & ", Error " & _ CStr(lErrNum) & ": " & sErrMsg ' Open the log file, write out the error information and ' close the log file. iFile = FreeFile() Open sPath & 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 <> 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 

Answer 1

I needed a little more help with this particular technique, so I went straight to the source and Mr. Bowie was kind enough to answer. He gave me permission to post his response to the StackOverflow community.

The instructions below relate to his preferred error handling method for the logical error processing functions, rather than the alternative retrow method, as described in his book Excel Professional Development, 2nd edition.

---

Hi Shari,

In response to your questions about error handling in functions, there are three error handling scenarios that you can have with a function in VBA:

1) The function is so trivial that it does not need an error handler. into an unlikely event caused by an error in such a function that it will spill into the error handler of the calling procedure.

2) A non-trivial function needs an error handler and uses the logical return value system described in the book. Any other values ​​that the return function must perform are returned via ByRef arguments. This case covers the vast majority of the functions that I write. There are some things that you cannot do with functions like this, giving them directly to the argument of another function is one for example, but I consider this a good compromise to achieve an error handling bullet.

3) A nontrivial function needs an error handler and should return a value, not errors related to its status. This is a rare situation because I can convert 99% plus them in case 2 by restructuring my code. If you cannot do this, your only choice is to select an arbitrary return value that does not have a range of normal return values ​​and use this to indicate that an error has occurred. If the caller of this function sees this arbitrary error flag, that it cannot continue.

Rob Bovey Application Professionals http://www.appspro.com/

---

Code Example (Shari W)

---

 ' Show how to call a function using this error handling method. Const giBAD_RESULT As Integer = -1 Function TestMath() ' An Entry Point Dim sngResult As Single Dim iNum As Integer ' Call the function, actual result goes in sngResult but it returns the error handling boolean. ' A true error like Div 0 will go to error handler. ' Set Up Error Handling for Entry Point Application.EnableCancelKey = xlErrorHandler Dim bUserCancel As Boolean Const sSOURCE As String = "TestMath()" On Error GoTo ErrorHandler ' End Error Set Up iNum = 0 ' Try 0 to create error If Not bDoSomeMath(iNum, sngResult) Then ERR.Raise glHANDLED_ERROR ' If function does parameter checking and wants to return a bad input code, check for that. If sngResult = giBAD_RESULT Then MsgBox ("Bad input to bDoSomeMath " & iNum) Else MsgBox ("I believe the answer is " & sngResult) End If ErrorExit: On Error Resume Next Exit Function ErrorHandler: If bCentralErrorHandler(msMODULE, sSOURCE, , True) Then Stop Resume Else Resume ErrorExit End If End Function Function bDoSomeMath(ByVal iNum As Integer, ByRef sngResult As Single) As Boolean ' Error handling Set Up Dim bReturn As Boolean Const sSOURCE As String = "bDoSomeMath()" On Error GoTo ErrorHandler bReturn = True ' End Error Set Up If iNum < 0 Or iNum > 1000 Then sngResult = giBAD_RESULT 'function failed because I only like the numbers 0 to 1000 GoTo ErrorExit Else sngResult = 100 / iNum ' generate a true error by iNum = 0 End If ErrorExit: On Error Resume Next bDoSomeMath = bReturn Exit Function ErrorHandler: bReturn = False If bCentralErrorHandler(msMODULE, sSOURCE, , , True) Then Stop Resume Else Resume ErrorExit End If End Function 

Answer 2

This is a terrific book by Rob.

My two cents of error handling (either for a procedure or for a function) are based on KISS (keep it just stupid)

Understand what you want from an error handler?

This is usually what I want / expect from my error handler ...

• The line in which the error occurred.
• Error number
• Error message
• Reset Events, if applicable

Allows you to break the above. Since you already know what your error handler looks like, consider this example.

 Sub Sample() Dim i As Integer, j As Integer On Error GoTo Whoa Application.ScreenUpdating = False i = 1111111111 For j = 1 To i Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value Next i LetsContinue: Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub 

This is a very simple error handler, but it helps me a lot. So let's now tweak it to make it more useful. If you run the code above, you will receive an error message, as shown in the screenshot below, and if you notice, this will not help.

Now consider all the points that I mentioned in Logic above

• The line in which the error occurred.

There is a property called ERL , which few people know about. You can use it to get the line number of the code where the error occurred. To do this, you need to specify your code number. See this example.

 Sub Sample() Dim i As Integer, j As Integer 10 On Error GoTo Whoa 20 Application.ScreenUpdating = False 30 i = 1111111111 40 For j = 1 To i 50 Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value 60 Next j LetsContinue: 70 Exit Sub Whoa: 80 MsgBox Erl 90 Resume LetsContinue End Sub 

When you run the above code, you will get this

So now I know that the error occurred on line 30, which i = 1111111111

Go to the next

2. Error number
3. Error message

The error number and error message can be obtained from Err.Number and Err.Description respectively. So now combine the ERL , Err.Number and Err.Description

Check out this example.

 Sub Sample() Dim i As Integer, j As Integer 10 On Error GoTo Whoa 20 Application.ScreenUpdating = False 30 i = 1111111111 40 For j = 1 To i 50 Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value 60 Next j LetsContinue: 70 Exit Sub Whoa: 80 MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _ "Error Message : " & Err.Description & vbNewLine & _ "Error Number : " & Err.Number 90 Resume LetsContinue End Sub 

When you run this code, you will get something like this.

You can further customize the error message to make it more user friendly. for instance

 '~~> Message you want to deliver to the user in case the error happens Const sMsg As String = "Please take a screenshot of this message and contact the developer for a resolution" '~~> Title of your message box Const sTitle As String = "Oopsie Daisies" '~~> Change the above as applicable Sub Sample() Dim i As Integer, j As Integer 10 On Error GoTo Whoa 20 Application.ScreenUpdating = False 30 i = 1111111111 40 For j = 1 To i 50 Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value 60 Next j LetsContinue: 70 Exit Sub Whoa: 80 MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _ "Error Message : " & Err.Description & vbNewLine & _ "Error Number : " & Err.Number & vbNewLine & vbNewLine & _ sMsg, vbCritical, sTitle 90 Resume LetsContinue End Sub 

To the next:)

Reset Events, if applicable

When you work with events and an error occurs, if error handling is missing, the code is interrupted. Unfortunately, this does not reset events. It is very important that you reset events in the error handler.

If you notice in the above code, we set Application.ScreenUpdating = False . When the code breaks, this event does not receive a reset. In this case, you will have to handle this in the LetsContinue error LetsContinue . See this example.

 '~~> Message you want to deliver to the user in case the error happens Const sMsg As String = "Please take a screenshot of this message and contact the developer for a resolution" '~~> Title of your message box Const sTitle As String = "Oopsie Daisies" '~~> Change the above as applicable Sub Sample() Dim i As Integer, j As Integer 10 On Error GoTo Whoa 20 Application.ScreenUpdating = False 30 i = 1111111111 40 For j = 1 To i 50 Debug.Print ThisWorkbook.Sheets(1).Cells(i, 1).Value 60 Next j LetsContinue: 70 Application.ScreenUpdating = True 80 Exit Sub Whoa: 90 MsgBox "The Error Happened on Line : " & Erl & vbNewLine & _ "Error Message : " & Err.Description & vbNewLine & _ "Error Number : " & Err.Number & vbNewLine & vbNewLine & _ sMsg, vbCritical, sTitle 100 Resume LetsContinue End Sub 

Like Philip, I also highly recommend using MZ-Tools for VBA. I use it now for donkeys ...

Hope this helps.

Answer 3

a suggestion on managing error handling in VBA can be found here .

The same tool (MZ-Tools) and method (standard / general error handler that can be used to create an automatic error reporting system) will work with Excel.