vba error handling

Proper VBA error handling

Written by on in Excel, MS Office, Outlook, PowerPoint, Word

Writing VBA code is hard, but properly debugging code is even harder. Sounds like non-sense? Well I dare say developers spend more time debugging code than writing it. Looking for errors is what developers do most of the time! A critical part of debugging is proper error handling (VBA error handling in our case).

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.

— Brian W. Kernighan

However, today I don’t want to expand on debugging VBA. That I covered in this post. No – today let’s learn how to properly handle errors

The Mouse Trap Analogy

What is error handling? Take this analogy: Say you have a mouse (an error) in the house which turns up every now and then in the least expected moment as slips from your hands (an uncaught exception if you prefer). Without knowing where the mouse is and when it (the exception/error) will appear (in which line of code) you would need to search entire house to catch it (run through the entire code in our case). Obviously a better approach is setting mouse traps in several critical places in the house (corridors etc.) and waiting for the mouse to fall into your trap.
rat_trap
So what is our mouse trap when speaking about VBA error handling? The On Error do this statement!

Using VBA On Error

The VBA On Error statement – tells VBA what it should do from now on, within the vicinity of the current block of code (Function or Sub), when an error/exception is raised. It is like setting a mouse trap – with the difference that you can tell it to drop the mouse off the dumpster or put it in your hands to manage.

Let’s remind the full syntax of the On Error statement in VBA first:

On Error { GoTo [ line | 0 ] | Resume Next }

Directly from MSDN we learn the different VBA error handling options we have with the On Error statement:

On Error

  • Goto – or 
    On Error GoTo [ lineLabel | 0 | -1 ]

    if preffered. The Goto instruction in VBA let’s you do a jump to a specific VBA code line number to follow through with error handling or simply to let code execution move on. It comes in three flavors:

    • lineLabel – will jump to a specific line number label
    • 0 – will disable any previously set error handling within the current procedure
  • Resume Next – Simply ignore any error raised and move code execution to the next line. VBA error handling for the lazy, although beware in case of recurring errors (error overflow) – an error will still be raised

On Error examples

With the above synax in mind let’s look at some On Error VBA error handling examples:

On Error Resume Next
Skip any raised errors

Dim x, y
x = y /0 'Divide by 0 error!
On Error Resume Next
x = y /0 'No error raised

On Error Goto 0
Disable any previous VBA error handling

Dim x, y
On Error Resume Next 'Skip errors
x = y /0 'No error raised
On Error Goto 0 'Disable error handling 
x = y /0 'Divide by 0 error!

On Error Goto Label
On error raised jump to a specific line label

Dim x, y
On Error Goto ErrorHandl
x = y /0 'No error raised
On Error Goto 0 'Disable error handling 
x = y /0 'Divide by 0 error!

The VBA Err Object

Whenever a VBA error is raised the Err object is updated with relevant information needed to diagnose the error. Let look at this object for a second.

Err Object functions and properties

The Err object facilitates the following functions and properties:

  • Number – the most important property of the Err Object, the error number raised. On MSDN you can find the full list of VBA errors. The available range for custom user errors is 513-65535.
  • Clear – clear the current Error. Useful when using the Resume Next statement.
  • Raise(Number, [Source], [Description], [HelpFile], [HelpContext]) – raises an error. You need to provide an error Number. See here for a whole list of VBA error Numbers. Want to raise a custom error? The available range for custom user errors is 513-65535.
  • Source – the source of the error – usually your VBAProject.
  • Description – the description of the error.
Dim errMsg As String
On Error Resume Next
'7 = Out of Memory
Err.Raise (7)

If Err.Number <> 0 Then
  errMsg = "Error number: " & Str(Err.Number) & vbNewLine & _
		   "Source: " & Err.Source & vbNewLine & _
		   "Description: " & Err.Description
  Debug.Print errMsg
  Err.Clear
End If

This is the expected output:
VBA Error: Out of Memory

See also  Get Outlook recipients information via VBA (Outlook users data)

VBA Error Handling Best Practices

Now that we know our way around VBA error handling let’s learn some good practices on VBA error handling. The best practice for error handling is putting all handlers (jump labels) at the end of your code block – whether its a VBA Sub or Function. The below example shows how it is done:

Single VBA error handler

If you want to handle all errors in a single section see example below:

    On Error GoTo ErrorHandler
    Dim x, y
    x = y / 0 'Divide by zero!
    Exit Sub
ErrorHandler:
    Debug.Print "Error number: " & Err.Number
    Err.Clear

Notice the Exit Sub statement just before the ErrorHandler label. This statement is important to make sure the ErrorHandler is accessed only when an error is raised.

Multiple VBA error handler

If you want to handle multiple errors e.g. based on the Error number see example below:

    On Error GoTo ErrorHandler
    Dim x, y
    x = y / 0 'Divide by zero!
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 6:
        GoTo DivideByZeroError
    Case 7:
        GoTo OutOfMemoryError
    Case Default:
        GoTo OtherError
    End Select
DivideByZeroError:
    Debug.Print "Divide by zero!"
    Err.Clear
    Exit Sub
OutOfMemoryError:
    Debug.Print "Out of memory!"
    Err.Clear
    Exit Sub
OtherError:
    Debug.Print "Other error!"
    Err.Clear
    Exit Sub

The ErrorHandler block in this case redirects to the designated error handler based on the Err.Number. Unrecognized errors are redirected to the OtherError block. This makes VBA error handling neat and tidy. I definitely prefer the multiple VBA error handler as it gives you and the user more insights and control over the errors raised by VBA.

Custom VBA errors

In some cases you will want to raise a custom error. The best way to do it is using the Err.Raise procedure.

When creating custom errors make sure to keep them well documented. I recommend creating an Enum object and listing all custom errors like below:

Enum CustomErrors
    CustomErr1 = 514 'First custom error number
    CustomErr2 = 515 
    '...
End Enum

Now we can use our Custom Error numbers in our VBA error handler:

 On Error GoTo ErrorHandler
    Err.Raise CustomErrors.CustomErr1 'Raise a customer error using the Enum
    Exit Sub
    
ErrorHandler:
    Select Case Err.Number
    Case CustomErrors.CustomErr1:
        GoTo CustomErr1Handler
    Case CustomErrors.CustomErr2:
        GoTo CustomErr1Handler
    Case Else:
        GoTo OtherError
    End Select

CustomErr1Handler:
    Debug.Print "CustomErr1"
    Err.Clear
    Exit Sub
CustomErr2Handler:
    Debug.Print "CustomErr2"
    Err.Clear
    Exit Sub
OtherError:
    Debug.Print "Other error!, Error: " & Err.Number
    Err.Clear
    Exit Sub

Neat and tidy right! Ok… but what about cleaning up those Error msgs? Clearing I don’t want to have duplicate error message descriptions lying around the place. A simple Get function can help:

Function GetErrorMsg(no As Long)
    Select Case no
        Case CustomErr1:
            GetErrorMsg = "This is CustomErr1"
        Case CustomErr1:
            GetErrorMsg = "This is CustomErr2"
    End Select
End Function

Let’s collect what we know into a consolidated code sample:
VBA Error Handling

    On Error GoTo ErrorHandler
    Err.Raise CustomErrors.CustomErr1
    Exit Sub
    
ErrorHandler:
    Select Case Err.Number
    Case CustomErrors.CustomErr1:
        GoTo CustomerErr1Handler
    Case CustomErrors.CustomErr2:
        GoTo CustomerErr2Handler
    Case Else:
        GoTo OtherError
        
    End Select
CustomerErr1Handler:
    Debug.Print GetErrorMsg(CustomErr1)
    'Handle the error
    Err.Clear
    Exit Sub
CustomerErr2Handler:
    Debug.Print GetErrorMsg(CustomErr1)
    'Handle the error
    Err.Clear
    Exit Sub
OtherError:
    Debug.Print GetErrorMsg(Err.Number)
    'Handle the error
    Err.Clear
    Exit Sub

The Resume Next statement

The Resume Next statement is great when you want on a error being raised you want execution to move on to the next statement and continue code execution. You can use the VBA Resume Next statement to resume code execution directly from your VBA error handler:

    On Error GoTo ErrorHandler
    Dim x, y
    x = y / 0 'Divide by zero!
    Debug.Print "Continue execution"
    Exit Sub
    
ErrorHandler:
    Debug.Print "Error: " & Err.Description
    Resume Next 'Return to line 4

VBA Error Log

Logging VBA errors is very useful in the VBA error handling concepts. This allows you to make sure you don’t lose track of any errors that might have occurred in your Workbooks although you might not want to handle these errors directly. Read here if you want to learn more about writing to text files.

The LogError procedure will append the error message prefixed with the current time (date and time):

Sub LogError(msg As String)
    Dim fileName As String, fileNo As Integer
    fileNo = FreeFile 'Get first free file number
    fileName = ThisWorkbook.Path & "\error_log.txt"
    Open fileName For Append As #fileNo
    Print #fileNo, Now & ":" & msg
    Close #fileNo
End Sub

Sub TestErrorLog()
    On Error GoTo ErrorHandler
    Dim x, y
    x = y / 0 'Divide by zero!
    Debug.Print "Continue execution"
    Exit Sub
    
ErrorHandler:
    LogError Err.Description
    Resume Next
End Sub
Related posts:

Comments are closed.