Vba error numbers

In this ArticleVBA Errors Cheat SheetErrorsVBA Error HandlingVBA On Error StatementOn Error GoTo 0On Error Resume NextErr.Number, Err.Clear, and Catching ErrorsError Handling with Err.NumberOn Error GoTo LineOn Error Exit SubErr.Clear, On Error GoTo -1,  and Resetting Err.NumberVBA On Error MsgBoxVBA IsErrorIf Error VBAVBA Error TypesRuntime ErrorsSyntax ErrorsCompile ErrorsDebug > CompileOverFlow ErrorOther VBA Error TermsVBA Catch ErrorVBA…

In this Article

  • VBA Errors Cheat Sheet
    • Errors
  • VBA Error Handling
  • VBA On Error Statement
    • On Error GoTo 0
    • On Error Resume Next
    • Err.Number, Err.Clear, and Catching Errors
      • Error Handling with Err.Number
    • On Error GoTo Line
      • On Error Exit Sub
      • Err.Clear, On Error GoTo -1,  and Resetting Err.Number
      • VBA On Error MsgBox
  • VBA IsError
  • If Error VBA
  • VBA Error Types
    • Runtime Errors
    • Syntax Errors
    • Compile Errors
    • Debug > Compile
    • OverFlow Error
  • Other VBA Error Terms
    • VBA Catch Error
    • VBA Ignore Error
    • VBA Throw Error / Err.Raise
    • VBA Error Trapping
    • VBA Error Message
    • VBA Error Handling in a Loop
  • VBA Error Handling in Access

VBA Errors Cheat Sheet

Errors

On Error – Stop code and display error

On Error Goto 0

On Error – Skip error and continue running

On Error Resume Next

On Error – Go to a line of code [Label]

On Error Goto [Label]

Clears (Resets) Error

On Error GoTo1

Show Error number

MsgBox Err.Number

Show Description of error

MsgBox Err.Description

Function to generate own error

Err.Raise

See more VBA “Cheat Sheets” and free PDF Downloads

VBA Error Handling

VBA Error Handling refers to the process of anticipating, detecting, and resolving VBA Runtime Errors. The VBA Error Handling process occurs when writing code, before any errors actually occur.

VBA Runtime Errors are errors that occur during code execution. Examples of runtime errors include:

  • Referencing a non-existent workbook, worksheet, or other object (Run-time Error 1004)
  • Invalid data ex. referencing an Excel cell containing an error (Type Mismatch – Run-time Error 13)
  • Attempting to divide by zero

VBA On Error Statement

Most VBA error handling is done with the On Error Statement. The On Error statement tells VBA what to do if it encounters an error. There are three On Error Statements:

  • On Error GoTo 0
  • On Error Resume Next
  • On Error GoTo Line

On Error GoTo 0

On Error GoTo 0 is VBA’s default setting. You can restore this default setting by adding the following line of code:

On Error GoTo 0

When an error occurs with On Error GoTo 0, VBA will stop executing code and display its standard error message box.

vba runtime error 13

Often you will add an On Error GoTo 0 after adding On Error Resume Next error handling (next section):

Sub ErrorGoTo0()

On Error Resume Next
    ActiveSheet.Shapes("Start_Button").Delete
On Error GoTo 0

'Run More Code

End Sub

On Error Resume Next

On Error Resume Next tells VBA to skip any lines of code containing errors and proceed to the next line.

On Error Resume Next

Note: On Error Resume Next does not fix an error, or otherwise resolve it. It simply tells VBA to proceed as if the line of code containing the error did not exist. Improper use of On Error Resume Next can result in unintended consequences.

A great time to use On Error Resume Next is when working with objects that may or may not exist. For example, you want to write some code that will delete a shape, but if you run the code when the shape is already deleted, VBA will throw an error. Instead you can use On Error Resume Next to tell VBA to delete the shape if it exists.

On Error Resume Next
    ActiveSheet.Shapes("Start_Button").Delete
On Error GoTo 0

Notice we added On Error GoTo 0 after the line of code containing the potential error. This resets the error handling.

In the next section we’ll show you how to test if an error occurred using Err.Number, giving you more advanced error handling options.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

automacro

Learn More

Err.Number, Err.Clear, and Catching Errors

Instead of simply skipping over a line containing an error, we can catch the error by using On Error Resume Next and Err.Number.

Err.Number returns an error number corresponding with the type of error detected. If there is no error, Err.Number = 0.

For example, this procedure will return “11” because the error that occurs is Run-time error ’11’.

Sub ErrorNumber_ex()

On Error Resume Next
ActiveCell.Value = 2 / 0
MsgBox Err.Number

End Sub

vba run-time error 11 err.number

Error Handling with Err.Number

The true power of Err.Number lies in the ability to detect if an error occurred (Err.Number <> 0).  In the example below, we’ve created a function that will test if a sheet exists by using Err.Number.

Sub TestWS()
    MsgBox DoesWSExist("test")
End Sub

Function DoesWSExist(wsName As String) As Boolean
    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = Sheets(wsName)
    
    'If Error WS Does not exist
    If Err.Number <> 0 Then
        DoesWSExist = False
    Else
        DoesWSExist = True
    End If

    On Error GoTo -1
End Function

Note: We’ve added a On Error GoTo -1 to the end which resets Err.Number to 0 (see two sections down).

With On Error Resume Next and Err.Number, you can replicate the “Try” & “Catch” functionality of other programming languages.

On Error GoTo Line

On Error GoTo Line tells VBA to “go to” a labeled line of code when an error is encountered.  You declare the Go To statement like this (where errHandler is the line label to go to):

On Error GoTo errHandler

and create a line label like this:

errHandler:

Note: This is the same label that you’d use with a regular VBA GoTo Statement.

Below we will demonstrate using On Error GoTo Line to Exit a procedure.

On Error Exit Sub

You can use On Error GoTo Line to exit a sub when an error occurs.

You can do this by placing the error handler line label at the end of your procedure:

Sub ErrGoToEnd()

On Error GoTo endProc

'Some Code
    
endProc:
End Sub

or by using the Exit Sub command:

Sub ErrGoToEnd()

On Error GoTo endProc

'Some Code
GoTo skipExit
    
endProc:
Exit Sub

skipExit:

'Some More Code

End Sub

Err.Clear, On Error GoTo -1,  and Resetting Err.Number

After an error is handled, you should generally clear the error to prevent future issues with error handling.

After an error occurs, both Err.Clear and On Error GoTo -1 can be used to reset Err.Number to 0. But there is one very important difference: Err.Clear does not reset the actual error itself, it only resets the Err.Number.

What does that mean?  Using Err.Clear, you will not be able to change the error handling setting. To see the difference, test out this code and replace On Error GoTo -1 with Err.Clear:

Sub ErrExamples()

    On Error GoTo errHandler:
        
    '"Application-defined" error
    Error (13)
    
Exit Sub
errHandler:
    ' Clear Error
    On Error GoTo -1
    
    On Error GoTo errHandler2:
    
    '"Type mismatch" error
    Error (1034)
    
Exit Sub
errHandler2:
    Debug.Print Err.Description
End Sub

Typically, I recommend always using On Error GoTo -1, unless you have a good reason to use Err.Clear instead.

VBA On Error MsgBox

You might also want to display a Message Box on error.  This example will display different message boxes depending on where the error occurs:

Sub ErrorMessageEx()
 
Dim errMsg As String
On Error GoTo errHandler

    'Stage 1
    errMsg = "An error occured during the Copy & Paste stage."
    'Err.Raise (11)
    
    'Stage 2
    errMsg = "An error occured during the Data Validation stage."
    'Err.Raise (11)
     
    'Stage 3
    errMsg = "An error occured during the P&L-Building and Copy-Over stage."
    Err.Raise (11)
     
    'Stage 4
    errMsg = "An error occured while attempting to log the Import on the Setup Page"
    'Err.Raise (11)

    GoTo endProc
    
errHandler:
    MsgBox errMsg
   
endProc:
End Sub

Here you would replace Err.Raise(11) with your actual code.

VBA IsError

Another way to handle errors is to test for them with the VBA ISERROR Function. The ISERROR Function tests an expression for errors, returning TRUE or FALSE if an error occurs.

Sub IsErrorEx()
    MsgBox IsError(Range("a7").Value)
End Sub

VBA Programming | Code Generator does work for you!

If Error VBA

You can also handle errors in VBA with the Excel IFERROR Function.  The IFERROR Function must be accessed by using the WorksheetFunction Class:

Sub IfErrorEx()

Dim n As Long
n = WorksheetFunction.IfError(Range("a10").Value, 0)

MsgBox n
End Sub

This will output the value of Range A10, if the value is an error, it will output 0 instead.

VBA Error Types

Runtime Errors

As stated above:

VBA Runtime Errors are errors that occur during code execution. Examples of runtime errors include:

  • Referencing a non-existent workbook, worksheet, or other object
  • Invalid data ex. referencing an Excel cell containing an error
  • Attempting to divide by zero

vba runtime error 13

You can “error handle” runtime errors using the methods discussed above.

Syntax Errors

VBA Syntax Errors are errors with code writing. Examples of syntax errors include:

  • Mispelling
  • Missing or incorrect punctuation

The VBA Editor identifies many syntax errors with red highlighting:

vba syntax error example

The VBA Editor also has an option to “Auto Syntax Check”:

vba syntax error option

When this is checked, the VBA Editor will generate a message box alerting you syntax errors after you enter a line of code:

vba syntax compile error

I personally find this extremely annoying and disable the feature.

Compile Errors

Before attempting to run a procedure, VBA will “compile” the procedure. Compiling transforms the program from source code (that you can see) into executable form (you can’t see).

VBA Compile Errors are errors that prevent the code from compiling.

A good example of a compile error is a missing variable declaration:

vba compile error variable

Other examples include:

  • For without Next
  • Select without End Select
  • If without End If
  • Calling a procedure that does not exist

Syntax Errors (previous section) are a subset of Compile Errors.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Debug > Compile

Compile errors will appear when you attempt to run a Procedure. But ideally, you would identify compile errors prior to attempting to run the procedure.

You can do this by compiling the project ahead of time. To do so, go to Debug > Compile VBA Project.

vba debug compile

The compiler will “go to” the first error. Once you fix that error, compile the project again. Repeat until all errors are fixed.

You can tell that all errors are fixed because Compile VBA Project will be grayed out:

vba compile vbaproject

OverFlow Error

The VBA OverFlow Error occurs when you attempt to put a value into a variable that is too large. For example, Integer Variables can only contain values between -32,768 to 32,768. If you enter a larger value, you’ll receive an Overflow error:

vba overflow error

Instead, you should use the Long Variable to store the larger number.

Other VBA Error Terms

VBA Catch Error

Unlike other programming languages, In VBA there is no Catch Statement. However, you can replicate a Catch Statement by using On Error Resume Next and If Err.Number <> 0 Then. This is covered above in Error Handling with Err.Number.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

VBA Ignore Error

To ignore errors in VBA, simply use the On Error Resume Next statement:

On Error Resume Next

However, as mentioned above, you should be careful using this statement as it doesn’t fix an error, it just simply ignores the line of code containing the error.

VBA Throw Error / Err.Raise

To through an error in VBA, you use the Err.Raise method.

This line of code will raise Run-time error ’13’: Type mismatch:

Err.Raise (13)

vba runtime error 13

VBA Error Trapping

VBA Error Trapping is just another term for VBA Error Handling.

VBA Error Message

A VBA Error Message looks like this:

vba runtime error 13

When you click ‘Debug’, you’ll see the line of code that is throwing the error:

vba raise error

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

VBA Error Handling in a Loop

The best way to error handle within a Loop is by using On Error Resume Next along with Err.Number to detect if an error has occurred (Remember to use Err.Clear to clear the error after each occurrence).

The example below will divide two numbers (Column A by Column B) and output the result into Column C. If there’s an error, the result will be 0.

Sub test()
Dim cell As Range

On Error Resume Next
For Each cell In Range("a1:a10")

    'Set Cell Value
    cell.Offset(0, 2).Value = cell.Value / cell.Offset(0, 1).Value
    
    'If Cell.Value is Error then Default to 0
    If Err.Number <> 0 Then
         cell.Offset(0, 2).Value = 0
         Err.Clear
    End If
 Next
End Sub

VBA Error Handling in Access

All of the above examples work exactly the same in Access VBA as in Excel VBA.

Function DelRecord(frm As Form)
'this function is used to delete a record in a table from a form
   On Error GoTo ending
   With frm
      If .NewRecord Then
         .Undo
         Exit Function
      End If
   End With
   With frm.RecordsetClone
      .Bookmark = frm.Bookmark
      .Delete
      frm.Requery
   End With
   Exit Function
   ending:
   End
End Function

“Abort, Retry, Fail?” – MS-DOS error message circa 1986

This post provides a complete guide to VBA Error Handing. If you are looking for a quick summary then check out the quick guide table in the first section.

If you are looking for a particular topic on VBA Error Handing then check out the table of contents below(if it’s not visible click on the post header).

If you are new to VBA Error Handling, then you can read the post from start to finish as it is laid out in logical order.

Contents

  • 1 A Quick Guide to Error Handing
  • 2 The Webinar
  • 3 Download the Error Handling Library
  • 4 Introduction
  • 5 VBA Errors
    • 5.1 Syntax Errors
    • 5.2 Compilation Errors
      • 5.2.1 Using Debug->Compile
      • 5.2.2 Debug->Compile Error Summary
      • 5.2.3 Debug->Compile Usage
    • 5.3 Runtime Errors
      • 5.3.1 Expected Versus Unexpected Errors
    • 5.4 Runtime Errors that are not VBA Errors
  • 6 The On Error Statement
    • 6.1 On Error GoTo 0
    • 6.2 On Error Resume Next
    • 6.3 On Error GoTo [label]
    • 6.4 On Error GoTo -1
    • 6.5 Using On Error
  • 7 Resume Next
  • 8 The Err Object
    • 8.1 Getting the Line Number
    • 8.2 Using Err.Raise
    • 8.3 Using Err.Clear
  • 9 Logging
  • 10 Other Error Related Items
    • 10.1 Error Function
    • 10.2 Error Statement
  • 11 A Simple Error Handling Strategy
    • 11.1 The Basic Implementation
  • 12 A Complete Error Handling Strategy
    • 12.1 An Example of using this strategy
  • 13 Error Handling in a Nutshell
  • 14 What’s Next?

A Quick Guide to Error Handing

Item Description
On Error Goto 0 When error occurs, the code stops and displays the error.
On Error Goto -1 Clears the current error setting and reverts to the default.
On Error Resume Next Ignores the error and continues on.
On Error Goto [Label] Goes to a specific label when an error occurs.
This allows us to handle the error.
Err Object When an error occurs the error information is stored here.
Err.Number The number of the error.
(Only useful if you need to check a specific error occurred.)
Err.Description Contains the error text.
Err.Source You can populate this when you use Err.Raise.
Err.Raise A function that allows you to generate your own error.
Error Function Returns the error text from an error number.
Obsolete.
Error Statement Simulates an error. Use Err.Raise instead.

The Webinar

Members of the Webinar Archives can access the webinar for this article by clicking on the image below.

(Note: Archive members have access to the webinar archive.)

vba error handling video

Download the Error Handling Library

Introduction

Error Handling refers to code that is written to handle errors which occur when your application is running. These errors are normally caused by something outside your control like a missing file, database being unavailable, data being invalid etc.

If we think an error is likely to occur at some point, it is good practice to write specific code to handle the error if it occurs and deal with it.

For all other errors, we use generic code to deal with them. This is where the VBA error handling statement comes into play. They allow our application to deal gracefully with any errors we weren’t expecting.

To understand error handling we must first understand the different types of errors in VBA.

VBA Error Handling

VBA Errors

There are three types of errors in VBA:

  1. Syntax
  2. Compilation
  3. Runtime

We use error handling to deal with runtime errors. Let’s have a look at each of these error types so that it is clear what a runtime error is.

Syntax Errors

If you have used VBA for any length of time you will have seen a syntax error. When you type a line and press return, VBA will evaluate the syntax and if it is not correct it will display an error message.

For example if you type If and forget the Then keyword, VBA will display the following error message

VBA Error Handling

Some examples of syntax errors are

' then is missing
If a > b

' equals is missing after i
For i 2 To 7

' missing right parenthesis
b = left("ABCD",1

Syntax errors relate to one line only. They occur when the syntax of one line is incorrect.

Note: You can turn off the Syntax error dialog by going to Tools->Options and checking off “Auto Syntax Check”. The line will still appear red if there is an error but the dialog will not appear.

Compilation Errors

Compilation errors occur over more than one line. The syntax is correct on a single line but is incorrect when all the project code is taken into account.

Examples of compilation errors are:

  • If statement without corresponding End If statement
  • For without Next
  • Select without End Select
  • Calling a Sub or Function that does not exist
  • Calling a Sub or Function with the wrong parameters
  • Giving a Sub or Function the same name as a module
  • Variables not declared(Option Explicit must be present at the top of the module)

The following screenshot shows a compilation error that occurs when a For loop has no matching Next statement.

VBA Error Handling

Using Debug->Compile

To find compilation errors, we use Debug->Compile VBA Project from the Visual Basic menu.

When you select Debug->Compile, VBA displays the first error it comes across.

When this error is fixed, you can run Compile again and VBA will then find the next error.

Debug->Compile will also include syntax errors in it’s search which is very useful.

If there are no errors left and you run Debug->Compile , it may appear that nothing happened. However, “Compile” will be grayed out in the Debug menu. This means your application has no compilation errors at the current time.

Debug->Compile Error Summary

  • Debug->Compile finds compilation(project wide) errors.
  • It will also find syntax errors.
  • It finds one error each time you use it.
  • When there are no compilation errors left the Compile option will appear grayed out in the menu.

Debug->Compile Usage

You should always use Debug->Compile before you run your code. This ensures that your code has no compilation errors when you run it.

If you do not run Debug->Compile then VBA may find compile errors when it runs. These should not be confused with Runtime errors.

Runtime Errors

Runtime errors occur when your application is running. They are normally outside of your control but can be caused by errors in your code.

VBA Error Handling

For example, imagine your application reads from an external workbook. If this file gets deleted then VBA will display an error when your code tries to open it.

Other examples of runtime errors are

  • a database not being available
  • the user entering invalid data
  • a cell containing text instead of a number

As we have seen, the purpose of error handling is to deal with runtime errors when they occur.

Expected Versus Unexpected Errors

When we think a runtime error could occur we put code in place to handle it. For example, we would normally put code in place to deal with a file not being found.

The following code checks if the file exists before it tries to open it. If the file does not exist then a user friendly message is displayed and the code exits the sub.

' https://excelmacromastery.com/
Sub OpenFile()
    
    Dim sFile As String
    sFile = "C:docsdata.xlsx"
    
    ' Use Dir to check if file exists
    If Dir(sFile) = "" Then
        ' if file does not exist display message
        MsgBox "Could not find the file " & sFile
        Exit Sub
    End If
    
    ' Code will only reach here if file exists
    Workbooks.Open sFile
    
End Sub

When we think an error is likely to occur at some point, it is good practice to add code to handle the situation. We normally refer to these errors as expected errors.

If we don’t have specific code to handle an error it is considered an unexpected error. We use the VBA error handling statements to handle the unexpected errors.

Runtime Errors that are not VBA Errors

Before we look at the VBA Handling there is one type of error we must mention. Some runtime errors are not considered errors by VBA but only by the user.

Let me explain this with an example. Imagine you have an application that requires you to add the values in the variables a and b

result = a + b

Let’s say you mistakenly use an asterisk instead of the plus sign

result = a * b

This is not a VBA error. Your code syntax is perfectly legal. However, from your requirements point of view it is an error.

These errors cannot be dealt with using error handling as they obviously won’t generate any error. You can deal with these errors using Unit Testing and Assertions. I have an in-depth post about using VBA assertions – see How to Make Your Code BulletProof.

The On Error Statement

As we have seen there are two ways to treat runtime errors

  1. Expected errors – write specific code to handle them.
  2. Unexpected errors – use VBA error handling statements to handle them.

The VBA On Error statement is used for error handling. This statement performs some action when an error occurs during runtime.

There are four different ways to use this statement

  1. On Error GoTo 0 – the code stops at the line with the error and displays a message.
  2. On Error Resume Next – the code moves to next line. No error message is displayed.
  3. On Error GoTo [label] – the code moves to a specific line or label. No error message is displayed. This is the one we use for error handling.
  4. On Error GoTo -1 – clears the current error.

Let’s look at each of these statements in turn.

On Error GoTo 0

This is the default behavior of VBA. In other words, if you don’t use On Error then this is the behavior you will see.

When an error occurs, VBA stops on the line with the error and displays the error message. The application requires user intervention with the code before it can continue. This could be fixing the error or restarting the application. In this scenario no error handling takes place.

Let’s look at an example. In the following code, we have not used any On Error line so VBA will use the On Error GoTo 0 behavior by default.

' https://excelmacromastery.com/
Sub UsingDefault()

    Dim x As Long, y As Long
    
    x = 6
    y = 6 / 0
    x = 7

End Sub

The second assignment line results in a divide by zero error. When we run this code we will get the error message shown in the screenshot below

VBA Error Handling

When the error appears you can choose End or Debug

If you select End then the application simply stops.
If you select Debug the application stops on the error line as the screenshot below shows

VBA Error Handling

This behaviour is fine when you are writing VBA code as it shows you the exact line with the error.

This behavior is unsuitable for an application that you are given to a user. These errors look unprofessional and they make the application look unstable.

An error like this is essentially the application crashing. The user cannot continue on without restarting the application. They may not use it at all until you fix the error for them.

By using On Error GoTo [label] we can give the user a more controlled error message. It also prevents the application stopping. We can get the application to perform in a predefined manner.

On Error Resume Next

Using On Error Resume Next tells VBA to ignore the error and continue on.

There are specific occasions when this is useful. Most of the time you should avoid using it.

If we add Resume Next to our example Sub then VBA will ignore the divide by zero error

' https://excelmacromastery.com/
Sub UsingResumeNext()

    On Error Resume Next
    
    Dim x As Long, y As Long
    
    x = 6
    y = 6 / 0
    x = 7

End Sub

It is not a good idea to do this. If you ignore the error, then the behavior can be unpredictable. The error can affect the application in multiple ways.You could end up with invalid data. The problem is that you aren’t aware that something went wrong because you have suppressed the error.

The code below is an example of where using Resume Next is valid

' https://excelmacromastery.com/
Sub SendMail()

   On Error Resume Next
   
    ' Requires Reference:
    ' Microsoft Outlook 15.0 Object Library
    Dim Outlook As Outlook.Application
    Set Outlook = New Outlook.Application

    If Outlook Is Nothing Then
        MsgBox "Cannot create Microsoft Outlook session." _
                   & " The email will not be sent."
        Exit Sub
    End If
    
End Sub

In this code we are checking to see if Microsoft Outlook is available on a computer. All we want to know is if it is available or not. We are not interested in the specific error.

In the code above, we continue on if there is an error. Then in the next line we check the value of the Outlook variable. If there has been an error then the value of this variable will be set to Nothing.

This is an example of when Resume could be useful. The point is that even though we use Resume we are still checking for the error. The vast majority of the time you will not need to use Resume.

On Error GoTo [label]

This is how we use Error Handling in VBA. It is the equivalent of the Try and Catch functionality you see in languages such as C# and Java.

When an error occurs you send the error to a specific label. It is normally at the bottom of the sub.

Let’s apply this to the sub we have been using

' https://excelmacromastery.com/
Sub UsingGotoLine()

    On Error GoTo eh
    
    Dim x As Long, y As Long
    
    x = 6
    y = 6 / 0
    x = 7
    
Done:
    Exit Sub
eh:
    MsgBox "The following error occurred: " & Err.Description
End Sub

The screenshot below shows what happens when an error occurs

VBA Error Handling

VBA jumps to the eh label because we specified this in the On Error Goto line.

Note 1: The label we use in the On…GoTo statement, must be in the current Sub/Function. If not you will get a compilation error.

Note 2: When an error occurs when using On Error GoTo [label], the error handling returns to the default behaviour i.e. The code will stop on the line with the error and display the error message. See the next section for more information about this.

On Error GoTo -1

This statement is different than the other three. It is used to clear the current error rather than setting a particular behaviour.

When an error occurs using On Error GoTo [label], the error handling behaviour returns to the default behaviour i.e. “On Error GoTo 0”. That means that if another error occurs the code will stop on the current line.

This behaviour only applies to the current sub. Once we exit the sub, the error will be cleared automatically.

Take a look at the code below. The first error will cause the code to jump to the eh label. The second error will stop on the line with the 1034 error.

' https://excelmacromastery.com/
Sub TwoErrors()

    On Error Goto eh
        
    ' generate "Type mismatch" error
    Error (13)

Done:
    Exit Sub
eh:
    ' generate "Application-defined" error
    Error (1034)
End Sub

If we add further error handling it will not work as the error trap has not been cleared.

In the code below we have added the line

On Error Goto eh_other

after we catch the first error.

This has no effect as the error has not been cleared. In other words the code will stop on the line with the error and display the message.

' https://excelmacromastery.com/
Sub TwoErrors()

    On Error Goto eh
        
    ' generate "Type mismatch" error
    Error (13)

Done:
    Exit Sub
eh:
    On Error Goto eh_other
    ' generate "Application-defined" error
    Error (1034)
Exit Sub
eh_other:
    Debug.Print "eh_other " & Err.Description
End Sub

To clear the error we use On Error GoTo -1. Think of it like setting a mouse trap. When the trap goes off you need to set it again.

In the code below we add this line and the second error will now cause the code to jump to the eh_other label

' https://excelmacromastery.com/
Sub TwoErrors()

    On Error Goto eh
        
    ' generate "Type mismatch" error
    Error (13)

Done:
    Exit Sub
eh:
    ' clear error
    On Error Goto -1
    
    On Error Goto eh_other
    ' generate "Application-defined" error
    Error (1034)
Exit Sub
eh_other:
    Debug.Print "eh_other " & Err.Description
End Sub

Note 1: There are probably rare cases where using On Error GoTo -1 is useful. In most cases using Resume Next is better as it clears the error and resumes the code at the next line after the error occurs.

Note 2: The Err Object has a member Clear. Using Clear clears the text and numbers in the Err object, but it does NOT reset the error.

Using On Error

As we have seen, VBA will do one of three things when an error occurs

  • Stop and display the error.
  • Ignore the error and continue on.
  • Jump to a specific line.

VBA will always be set to one of these behaviors. When you use On Error, VBA will change to the behaviour you specify and forget about any previous behavior.

In the following Sub, VBA changes the error behaviour each time we use the On Error statement

' https://excelmacromastery.com/
Sub ErrorStates()

    Dim x As Long
    
    ' Go to eh label if error
    On Error Goto eh
    
    ' this will ignore the error on the following line
    On Error Resume Next
    x = 1 / 0
    
    ' this will display an error message on the following line
    On Error Goto 0
    x = 1 / 0
  
Done:  
   Exit Sub
eh:
    Debug.Print Err.Description
End Sub

Resume Next

The Resume Next statement is used to clear the error and then resume the code from the line after where the error occurred.

If your code can have multiple errors and you want to keep detecting them then this line is very useful.

For example, in the following code we want to resume the code after the error has been reported:

Private Sub Main()

    On Error Goto eh
    
    Dim i As Long
    For i = 1 To 3
        ' Generate type mismatch error
         Error 13
    Next i

done:
    Exit Sub
eh:
    Debug.Print i, Err.Description
End Sub

 
We could use On Error Goto -1 to clear the code and then use a goto statement to go back to the code like this:

Private Sub Main()

    On Error Goto eh
    
    Dim i As Long
    For i = 1 To 3
        ' Generate type mismatch error
         Error 13
continue:
    Next i

done:
    Exit Sub
eh:
    Debug.Print i, Err.Description
    On Error Goto -1 ' clear the error
    Goto continue ' return to the code
End Sub

 
The Resume Next provides a nicer way of doing it and it always means the code is much clearer and easier to understand:

Private Sub Main()

    On Error Goto eh
    
    Dim i As Long
    For i = 1 To 3
        ' Generate type mismatch error
         Error 13
continue:
    Next i

done:
    Exit Sub
eh:
    Debug.Print i, Err.Description
    ' clear the error and return to the code
    Resume Next  
End Sub

The Err Object

When an error occurs you can view details of the error using the Err object.

When an runtime error occurs, VBA automatically fills the Err object with details.

The code below will print “Error Number: 13 Type Mismatch” which occurs when we try to place a string value in the long integer total

' https://excelmacromastery.com/
Sub UsingErr()

    On Error Goto eh
    
    Dim total As Long
    total = "aa"

Done:
    Exit Sub
eh:
    Debug.Print "Error number: " & Err.Number _
            & " " & Err.Description
End Sub

The Err.Description provides details of the error that occurs. This is the text you normally see when an error occurs e.g. “Type Mismatch”

The Err.Number is the ID number of the error e.g. the error number for “Type Mismatch” is 13. The only time you really need this is if you are checking that a specific error occurred and this is only necessary on rare occasions.

The Err.Source property seems like a great idea but it does not work for a VBA error. The source will return the project name, which hardly narrows down where the error occurred. However, if you create an error using Err.Raise you can set the source yourself and this can be very useful.

Getting the Line Number

The Erl function is used to return the line number where the error occurs.

It often causes confusion. In the following code, Erl will return zero

' https://excelmacromastery.com/
Sub UsingErr()

    On Error Goto eh
    
    Dim val As Long
    val = "aa"

Done:
    Exit Sub
eh:
    Debug.Print Erl
End Sub

This is because there are no line numbers present. Most people don’t realise it but VBA allows you to have line numbers.

If we change the Sub above to have line number it will now print out 20

' https://excelmacromastery.com/
Sub UsingErr()

10        On Error Goto eh
          
          Dim val As Long
20        val = "aa"

Done:
30        Exit Sub
eh:
40        Debug.Print Erl
End Sub

Adding line numbers to your code manually is cumbersome. However there are tools available that will allow you to easily add and remove line numbers to a sub.

When you are finished working on a project and hand it over to the user it can be useful to add line numbers at this point. If you use the error handling strategy in the last section of this post, then VBA will report the line where the error occurred.

Using Err.Raise

Err.Raise allows us to create errors. We can use it to create custom errors for our application which is very useful. It is the equivalent of the Throw statement in JavaC#.

The format is as follows

Err.Raise [error number], [error source], [error description]

Let’s look at a simple example. Imagine we want to ensure that a cell has an entry that has a length of 5 characters. We could have a specific message for this

' https://excelmacromastery.com/
Public Const ERROR_INVALID_DATA As Long = vbObjectError + 513

Sub ReadWorksheet()

    On Error Goto eh
    
    If Len(Sheet1.Range("A1")) <> 5 Then
        Err.Raise ERROR_INVALID_DATA, "ReadWorksheet" _
            , "The value in the cell A1 must have exactly 5 characters."
    End If
    
    ' continue on if cell has valid data
    Dim id As String
    id = Sheet1.Range("A1")
    

Done:
    Exit Sub
eh:
    ' Err.Raise will send code to here
    MsgBox "Error found: " & Err.Description
End Sub

When we create an error using Err.Raise we need to give it a number. We can use any number from 513 to 65535 for our error. We must use vbObjectError with the number e.g.

Err.Raise vbObjectError + 513

Using Err.Clear

Err.Clear is used to clear the text and numbers from the Err.Object. In other words, it clears the description and number.If you want the clear the actual error you can use either On Error GoTo -1 or Resume Next

It is rare that you will need to use Err.Clear but let’s have a look at an example where you might.

In the code below we are counting the number of errors that will occur. To keep it simple we are generating an error for each odd number.

We check the error number each time we go through the loop. If the number does not equal zero then an error has occurred. Once we count the error we need to set the error number back to zero so it is ready to check for the next error.

' https://excelmacromastery.com/
Sub UsingErrClear()

    Dim count As Long, i As Long

    ' Continue if error as we will check the error number
    On Error Resume Next
    
    For i = 0 To 9
        ' generate error for every second one
        If i Mod 2 = 0 Then Error (13)
        
        ' Check for error
        If Err.Number <> 0 Then
            count = count + 1
            Err.Clear    ' Clear Err once it is counted
        End If
    Next

    Debug.Print "The number of errors was: " & count
End Sub

Note 1: Err.Clear resets the text and numbers in the error object but it does not clear the error – see Resume Next Or On Error GoTo -1 for more information about clearing the actual error.

Logging

Logging means writing information from your application when it is running. When an error occurs you can write the details to a text file so you have a record of the error.

The code below shows a very simple logging procedure

' https://excelmacromastery.com/
Sub Logger(sType As String, sSource As String, sDetails As String)
    
    Dim sFilename As String
    sFilename = "C:templogging.txt"
    
    ' Archive file at certain size
    If FileLen(sFilename) > 20000 Then
        FileCopy sFilename _
            , Replace(sFilename, ".txt", Format(Now, "ddmmyyyy hhmmss.txt"))
        Kill sFilename
    End If
    
    ' Open the file to write
    Dim filenumber As Variant
    filenumber = FreeFile 
    Open sFilename For Append As #filenumber
    
    Print #filenumber, CStr(Now) & "," & sType & "," & sSource _
                                & "," & sDetails & "," & Application.UserName
    
    Close #filenumber
    
End Sub

You can use it like this

' Create unique error number
' https://excelmacromastery.com/
Public Const ERROR_DATA_MISSING As Long = vbObjectError + 514

Sub CreateReport()

    On Error Goto eh
    
    If Sheet1.Range("A1") = "" Then
       Err.Raise ERROR_DATA_MISSING, "CreateReport", "Data is missing from Cell A1"
    End If

    ' other code here
Done:
    Exit Sub
eh:
    Logger "Error", Err.Source, Err.Description
End Sub

The log is not only for recording errors. You can record other information as the application runs. When an error occurs you can then check the sequence of events before an error occurred.

Below is an example of logging. How you implement logging really depends on the nature of the application and how useful it will be:

' https://excelmacromastery.com/
Sub ReadingData()
    
    Logger "Information", "ReadingData()", "Starting to read data."
       
    Dim coll As New Collection
    ' add data to the collection
    coll.Add "Apple"
    coll.Add "Pear"
    
    If coll.Count < 3 Then
        Logger "Warning", "ReadingData()", "Number of data items is low."
    End If
    Logger "Information", "ReadingData()", "Number of data items is " & coll.Count
    
    Logger "Information", "ReadingData()", "Finished reading data."

End Sub

Having a lot of information when dealing with an error can be very useful. Often the user may not give you accurate information about the error that occurred. By looking at the log you can get more accurate information about the information.

This section covers some of the other Error Handling tools that VBA has. These items are considered obsolete but I have included them as they may exist in legacy code.

Error Function

The Error Function is used to print the error description from a given error number. It is included in VBA for backward compatibility and is not needed because you can use the Err.Description instead.

Below are some examples:

' Print the text "Division by zero"
Debug.Print Error(11)
' Print the text "Type mismatch"
Debug.Print Error(13)
' Print the text "File not found"
Debug.Print Error(53)

Error Statement

The Error statement allows you to simulate an error. It is included in VBA for backward compatibility. You should use Err.Raise instead.

In the following code we simulate a “Divide by zero” error.

' https://excelmacromastery.com/
Sub SimDivError()

    On Error Goto eh
        
    ' This will create a division by zero error
    Error 11
    
    Exit Sub
eh:
    Debug.Print Err.Number, Err.Description
End Sub

This statement is included in VBA for backward compatibility. You should use Err.Raise instead.

A Simple Error Handling Strategy

With all the different options you may be confused about how to use error handling in VBA. In this section, I’m going to show you how to implement a simple error handling strategy that you can use in all your applications.

The Basic Implementation

This is a simple overview of our strategy

  1. Place the On Error GoTo Label line at the start of our topmost sub.
  2. Place the error handling Label at the end of our topmost sub.
  3. If an expected error occurs then handle it and continue.
  4. If the application cannot continue then use Err.Raise to jump to the error handling label.
  5. If an unexpected error occurs the code will automatically jump to the error handling label.

The following image shows an overview of how this looks

error-handling

The following code shows a simple implementation of this strategy:

' https://excelmacromastery.com/
Public Const ERROR_NO_ACCOUNTS As Long = vbObjectError + 514

Sub BuildReport()

    On Error Goto eh
    
    ' If error in ReadAccounts then jump to error
    ReadAccounts
    
    ' Do something with the code
    
Done:
    Exit Sub
eh:
    ' All errors will jump to here
    MsgBox Err.Source & ": The following error occured  " & Err.Description
End Sub

Sub ReadAccounts()
    
    ' EXPECTED ERROR - Can be handled by the code
    ' Application can handle A1 being zero
    If Sheet1.Range("A1") = 0 Then
        Sheet1.Range("A1") = 1
    End If
    
    ' EXPECTED  ERROR - cannot be handled by the code
    ' Application cannot continue if no accounts workbook
    If Dir("C:DocsAccount.xlsx") = "" Then
        Err.Raise ERROR_NO_ACCOUNTS, "UsingErr" _
                , "There are no accounts present for this month."
    End If

    ' UNEXPECTED ERROR - cannot be handled by the code
    ' If cell B3 contains text we will get a type mismatch error
    Dim total As Long
    total = Sheet1.Range("B3")
    
    
    ' continue on and read accounts
    
End Sub

This is a nice way of implementing error handling because

  • We don’t need to add error handling code to every sub.
  • If an error occurs then VBA exits the application gracefully.

A Complete Error Handling Strategy

The above strategy has one major drawback. It doesn’t provide any information about the error. It is better than having no strategy as it prevents the application crashing. But that is the only real benefit.

VBA doesn’t fill Err.Source with anything useful so we have to do this ourselves.

In this section, I am going to introduce a more complete error strategy. I have written two subs that perform all the heavy lifting so all you have to do is add them to your project.

The purpose of this strategy is to provide you with the Stack* and line number when an error exists.

*The Stack is the list of sub/functions that were currently in use when the error occurred.

This is our strategy

  1. Place error handling in all the subs.
  2. When an error occurs, the error handler adds details to the error and raises it again.
  3. When the error reaches the topmost sub it is displayed.

We are simply “bubbling” the error to the top. The following diagram shows a simple visual of what happens when an error occurs in Sub3

Error Handling - bubbling

The only messy part to this is formatting the strings correctly. I have written two subs that handle this, so it is taken care of for you.

There are the two helper subs, RaiseError and DisplayError. You can download the library below:

An Example of using this strategy

Here is a simple coding example that uses these subs. In this strategy, we don’t place any code in the topmost sub. We only call subs from it.

' https://excelmacromastery.com/
Sub Topmost()

    On Error Goto EH
    
    Level1

Done:
    Exit Sub
EH:
    DisplayError Err.source, Err.Description, "Module1.Topmost", Erl
End Sub

Sub Level1()

    On Error Goto EH
    
    Level2

Done:
    Exit Sub
EH:
   RaiseError Err.Number, Err.source, "Module1.Level1", Err.Description, Erl
End Sub

Sub Level2()

    On Error Goto EH
    
    ' Error here
    Dim a As Long
    a = "7 / 0"

Done:
    Exit Sub
EH:
    RaiseError Err.Number, Err.source, "Module1.Level2", Err.Description, Erl
End Sub

The result looks like this:

error handling output

If your project has line numbers the result will include the line number of the error:

error handling output line

Error Handling in a Nutshell

  • Error Handling is used to handle errors that occur when your application is running.
  • You write specific code to handle expected errors. You use the VBA error handling statement On Error GoTo [label] to send VBA to a label when an unexpected error occurs.
  • You can get details of the error from Err.Description.
  • You can create your own error using Err.Raise.
  • Using one On Error statement in the top most sub will catch all errors in subs that are called from here.
  • If you want to record the name of the Sub with the error, you can update the error and rethrow it.
  • You can use a log to record information about the application as it is running.

What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars and all the tutorials.

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)

title keywords f1_keywords ms.prod ms.assetid ms.date ms.localizationpriority

Elements of run-time error handling

vbaac10.chm5186924

vbaac10.chm5186924

access

a0e06a1e-2709-aa51-92d0-340788a31a8a

09/21/2018

medium

Errors and error handling

When you are programming an application, you need to consider what happens when an error occurs. An error can occur in your application for one of two of reasons. First, some condition at the time the application is running makes otherwise valid code fail. For example, if your code attempts to open a table that the user has deleted, an error occurs. Second, your code may contain improper logic that prevents it from doing what you intended. For example, an error occurs if your code attempts to divide a value by zero.

If you’ve implemented no error handling, then Visual Basic halts execution and displays an error message when an error occurs in your code. The user of your application is likely to be confused and frustrated when this happens. You can forestall many problems by including thorough error-handling routines in your code to handle any error that may occur.

When adding error handling to a procedure, you should consider how the procedure will route execution when an error occurs. The first step in routing execution to an error handler is to enable an error handler by including some form of the On Error statement within the procedure. The On Error statement directs execution in event of an error. If there’s no On Error statement, Visual Basic simply halts execution and displays an error message when an error occurs.

When an error occurs in a procedure with an enabled error handler, Visual Basic doesn’t display the normal error message. Instead it routes execution to an error handler, if one exists. When execution passes to an enabled error handler, that error handler becomes active. Within the active error handler, you can determine the type of error that occurred and address it in the manner that you choose. Access provides three objects that contain information about errors that have occurred, the ADO Error object, the Visual Basic Err object, and the DAO Error object.

Routing execution when an error occurs

An error handler specifies what happens within a procedure when an error occurs. For example, you may want the procedure to end if a certain error occurs, or you may want to correct the condition that caused the error and resume execution. The On Error and Resume statements determine how execution proceeds in the event of an error.

On Error statement

The On Error statement enables or disables an error-handling routine. If an error-handling routine is enabled, execution passes to the error-handling routine when an error occurs.

There are three forms of the On Error statement: On Error GoTo label, On Error GoTo 0, and On Error Resume Next. The On Error GoTo label statement enables an error-handling routine, beginning with the line on which the statement is found. You should enable the error-handling routine before the first line at which an error could occur. When the error handler is active and an error occurs, execution passes to the line specified by the label argument.

The line specified by the label argument should be the beginning of the error-handling routine. For example, the following procedure specifies that if an error occurs, execution passes to the line labeled:

Function MayCauseAnError() 
    ' Enable error handler. 
    On Error GoTo Error_MayCauseAnError 
    .            ' Include code here that may generate error. 
    . 
    . 
 
Error_MayCauseAnError: 
    .            ' Include code here to handle error. 
    . 
    . 
End Function

The On Error GoTo 0 statement disables error handling within a procedure. It doesn’t specify line 0 as the start of the error-handling code, even if the procedure contains a line numbered 0. If there’s no On Error GoTo 0 statement in your code, the error handler is automatically disabled when the procedure has run completely. The On Error GoTo 0 statement resets the properties of the Err object, having the same effect as the Clear method of the Err object.

The On Error Resume Next statement ignores the line that causes an error and routes execution to the line following the line that caused the error. Execution isn’t interrupted. Use the On Error Resume Next statement if you want to check the properties of the Err object immediately after a line at which you anticipate an error will occur, and handle the error within the procedure rather than in an error handler.

Resume statement

The Resume statement directs execution back to the body of the procedure from within an error-handling routine. You can include a Resume statement within an error-handling routine if you want execution to continue at a particular point in a procedure. However, a Resume statement isn’t necessary; you can also end the procedure after the error-handling routine.

There are three forms of the Resume statement. The Resume or Resume 0 statement returns execution to the line at which the error occurred. The Resume Next statement returns execution to the line immediately following the line at which the error occurred. The Resume label statement returns execution to the line specified by the label argument. The label argument must indicate either a line label or a line number.

You typically use the Resume or Resume 0 statement when the user must make a correction. For example, if you prompt the user for the name of a table to open, and the user enters the name of a table that doesn’t exist, you can prompt the user again and resume execution with the statement that caused the error.

You use the Resume Next statement when your code corrects for the error within an error handler, and you want to continue execution without rerunning the line that caused the error. You use the Resume label statement when you want to continue execution at another point in the procedure, specified by the label argument. For example, you might want to resume execution at an exit routine, as described in the following section.

Exiting a procedure

When you include an error-handling routine in a procedure, you should also include an exit routine, so that the error-handling routine will run only if an error occurs. You can specify an exit routine with a line label in the same way that you specify an error-handling routine.

For example, you can add an exit routine to the example in the previous section. If an error doesn’t occur, the exit routine runs after the body of the procedure. If an error occurs, then execution passes to the exit routine after the code in the error-handling routine has run. The exit routine contains an Exit statement.

Function MayCauseAnError() 
    ' Enable error handler. 
    On Error GoTo Error_MayCauseAnError 
    .            ' Include code here that may generate error. 
    . 
    . 
 
Exit_MayCauseAnError: 
    Exit Function 
 
Error_MayCauseAnError: 
    .            ' Include code to handle error. 
    . 
    . 
    ' Resume execution with exit routine to exit function. 
    Resume Exit_MayCauseAnError 
End Function

Handling errors in nested procedures

When an error occurs in a nested procedure that doesn’t have an enabled error handler, Visual Basic searches backward through the calls list for an enabled error handler in another procedure, rather than simply halting execution. This provides your code with an opportunity to correct the error within another procedure. For example, suppose Procedure A calls Procedure B, and Procedure B calls Procedure C. If an error occurs in Procedure C and there’s no enabled error handler, Visual Basic checks Procedure B, then Procedure A, for an enabled error handler. If one exists, execution passes to that error handler. If not, execution halts and an error message is displayed.

Visual Basic also searches backward through the calls list for an enabled error handler when an error occurs within an active error handler. You can force Visual Basic to search backward through the calls list by raising an error within an active error handler with the Raise method of the Err object. This is useful for handling errors that you don’t anticipate within an error handler. If an unanticipated error occurs, and you regenerate that error within the error handler, then execution passes back up the calls list to find another error handler, which may be set up to handle the error.

For example, suppose Procedure C has an enabled error handler, but the error handler doesn’t correct for the error that has occurred. Once the error handler has checked for all the errors that you’ve anticipated, it can regenerate the original error. Execution then passes back up the calls list to the error handler in Procedure B, if one exists, providing an opportunity for this error handler to correct the error. If no error handler exists in Procedure B, or if it fails to correct for the error and regenerates it again, then execution passes to the error handler in Procedure A, assuming one exists.

To illustrate this concept in another way, suppose that you have a nested procedure that includes error handling for a type mismatch error, an error which you’ve anticipated. At some point, a division-by-zero error, which you haven’t anticipated, occurs within Procedure C. If you’ve included a statement to regenerate the original error, then execution passes back up the calls list to another enabled error handler, if one exists. If you’ve corrected for a division-by-zero error in another procedure in the calls list, then the error will be corrected. If your code doesn’t regenerate the error, then the procedure continues to run without correcting the division-by-zero error. This in turn may cause other errors within the set of nested procedures.

In summary, Visual Basic searches back up the calls list for an enabled error handler if:

  • An error occurs in a procedure that doesn’t include an enabled error handler.

  • An error occurs within an active error handler. If you use the Raise method of the Err object to raise an error, you can force Visual Basic to search backward through the calls list for an enabled error handler.

Getting information about an error

After execution has passed to the error-handling routine, your code must determine which error has occurred and address it. Visual Basic and Access provide several language elements that you can use to get information about a specific error. Each is suited to different types of errors. Since errors can occur in different parts of your application, you need to determine which to use in your code based on what errors you expect.

The language elements available for error handling include:

  • Err object

  • ADO Error object and Errors collection

  • DAO Error object and Errors collection

  • AccessError method

  • Error event

Err object

The Err object is provided by Visual Basic. When a Visual Basic error occurs, information about that error is stored in the Err object. The Err object maintains information about only one error at a time. When a new error occurs, the Err object is updated to include information about that error instead.

To get information about a particular error, you can use the properties and methods of the Err object:

  • The Number property is the default property of the Err object; it returns the identifying number of the error that occurred.
  • The Err object’s Description property returns the descriptive string associated with a Visual Basic error.
  • The Clear method clears the current error information from the Err object.
  • The Raise method generates a specific error and populates the properties of the Err object with information about that error.

The following example shows how to use the Err object in a procedure that may cause a type mismatch error:

Function MayCauseAnError() 
    ' Declare constant to represent likely error. 
    Const conTypeMismatch As Integer = 13 
 
    On Error GoTo Error_MayCauseAnError 
        .            ' Include code here that may generate error. 
        . 
        . 
 
Exit_MayCauseAnError: 
    Exit Function 
 
Error_MayCauseAnError: 
    ' Check Err object properties. 
    If Err = conTypeMismatch Then 
        .            ' Include code to handle error. 
        . 
        . 
    Else 
        ' Regenerate original error. 
        Dim intErrNum As Integer 
        intErrNum = Err 
        Err.Clear 
        Err.Raise intErrNum 
    End If 
    ' Resume execution with exit routine to exit function. 
    Resume Exit_MayCauseAnError 
End Function

Note that in the preceding example, the Raise method is used to regenerate the original error. If an error other than a type mismatch error occurs, execution will be passed back up the calls list to another enabled error handler, if one exists.

The Err object provides you with all the information you need about Visual Basic errors. However, it doesn’t give you complete information about Access errors or Access database engine errors. Access and Data Access Objects (DAO)) provide additional language elements to assist you with those errors.

Error object and Errors collection

The Error object and Errors collection are provided by ADO and DAO. The Error object represents an ADO or DAO error. A single ADO or DAO operation may cause several errors, especially if you are performing DAO ODBC operations. Each error that occurs during a particular data access operation has an associated Error object. All the Error objects associated with a particular ADO or DAO operation are stored in the Errors collection, the lowest-level error being the first object in the collection and the highest-level error being the last object in the collection.

When a ADO or DAO error occurs, the Visual Basic Err object contains the error number for the first object in the Errors collection. To determine whether additional ADO or DAO errors have occurred, check the Errors collection. The values of the ADO Number or DAO Number properties and the ADO Description or DAO Description properties of the first Error object in the Errors collection should match the values of the Number and Description properties of the Visual Basic Err object.

AccessError method

Use the Raise method of the Err object to generate a Visual Basic error that hasn’t actually occurred and determine the descriptive string associated with that error. However, you can’t use the Raise method to generate a Access error, an ADO error, or a DAO error. To determine the descriptive string associated with an Access error, an ADO error, or a DAO error that hasn’t actually occurred, use the AccessError method.

Error event

Use the Error event to trap errors that occur on an Access form or report. For example, if a user tries to enter text in a field whose data type is Date/Time, the Error event occurs. If you add an Error event procedure to an Employees form, then try to enter a text value in the HireDate field, the Error event procedure runs.

The Error event procedure takes an integer argument, DataErr. When an Error event procedure runs, the DataErr argument contains the number of the Access error that occurred. Checking the value of the DataErr argument within the event procedure is the only way to determine the number of the error that occurred. The Err object isn’t populated with error information after the Error event occurs. Use the value of the DataErr argument together with the AccessError method to determine the number of the error and its descriptive string.

[!NOTE]
The Error statement and Error function are provided for backward compatibility only. When writing new code, use the Err and Error objects, the AccessError function, and the Error event for getting information about an error.

About the contributors

Link provided by Community Member Icon the UtterAccess community.

  • Handling Access Errors with VBA

UtterAccess is the premier Microsoft Access wiki and help forum.

See also

  • Access for developers forum
  • Access help on support.office.com
  • Access forums on UtterAccess
  • Access developer and VBA programming help center (FMS)
  • Access posts on StackOverflow

[!includeSupport and feedback]

Поискав по рунету материал на тему обработки ошибок в VBA, не увидал на первых двух страницах результатов поиска чего-то, что мне понравилось. Может плохо смотрел, но решил написать на эту тему свою статью.

Простите, но — немного словоблудия :)

Ошибки в программе

Ошибки времени исполнения программы возникают, когда среда программирования не может выполнить то, что вы хотите. Таких ситуаций может быть много. Например:

  1. Вы обращаетесь к объекту по имени, а объекта с таким именем в коллекции нет

  2. Вы хотите выделить ячеку на одном листе, а этот лист в данный момент не является активным (типичнейшая ошибка новичков в Excel VBA)

  3. Вы хотите удалить отфильтрованные автофильтром строки, а фильтр вообще не вернул записей и удалять нечего

  4. Вы ссылаетесь на элемент массива, который находится за пределами его границ.

  5. Вы пытаетесь присвоить переменной значение, которое оно не может хранить. Например, переменной типа Long нельзя присвоить строковую константу или переменной типа Integer присвоить знанчение превышающее число 32767.

На любую из этих и сотни других ситуаций среда выполнения реагирует стандартно — прерывает ход выполнения программы на том операторе, где возникла ошибка или, как ещё принято говорить, исключение. На экран выводится информация о возникшей ошибке и предлагаются стандартные варианты для продолжения работы:

  • Continue (продолжить) — этот пункт во время возникновения ошибки всегда не активен. Он активен, когда по ходу выполнения программы вы использовали оператор Stop. Кстати это очень полезный оператор для отладки программы.

  • End (завершить) — завершение исполнения программы

  • Debug (отладка) — переход в режим отладки, в котором можно посмотреть, на каком операторе возникла ошибка, что содержат переменные, можно даже перетащить жёлтую полоску, подсвечивающую текущий оператор, назад, и модифицировать знанчение переменных через окно Immediate window (впрочем это экзотика). В общем случае кнопка Debug позволяет посмотреть, где случилась ошибка и попытаться понять почему так случилось.

Если вы — автор программы, в которой случилась ошибка, то вы, должно быть, в начале будете рады увидеть подобное окно, ибо только так вы сможете отловить основные ошибки, скрытые в вашем коде. Однако, если эту ошибку видит пользователь, то для него это, мягко говоря, безрадостное и малопонятное зрелище. Ещё хуже, если за эту программу вам заплатили деньги. Поэтому в среде худо-бедно профессиональных программистов принято предусматривать обработку ошибок в своих программах.

Почему вообще в коде возникают ошибки?

  1. Много ошибок во время написания кода возникает по невнимательности или не совсем адекватного понимания того, что делаешь. Таких ошибок, как правило, очень много, особенно у начинающих программистов, но эти ошибки довольно легко отловить и исправить, так как, пока вы их не исправите, ничего не работает. Ну, например, вы должны извлечь данные из 5-го столбца, а вы извлекаете из 6-го, а их там банально нет. Ясно, что вы это очень быстро заметите.

  2. Вторая группа ошибок — это ошибки оптимиста. Когда программа написана в целом правильно, но алгоритм не готов к ударам судьбы в виде неожиданных действий со стороны пользователя, ошибок ввода-вывода (вы рассчитывали считать данные из файла, а файла с таким именем не оказалось, либо он заблокирован другим приложением), особенностей конфигурации компьютера (разные версии ОС или офиса, которые в некоторых мелочах отличаются).

  3. Тонкие логические ошибки. Чем сложнее программа, тем больше шансов, что модель задачи в вашей голове, ваша программа и реальность не совсем согласованы между собой. Пока вы не достигните достаточного погружения в задачу вы такие ошибки не найдёте и не исправите. Порой на это уходит много времени. Но это характерно для сложных задач.

  4. Ошибки на стыке вашего приложения и сервисов ОС, приводящие к неожиданным крахам приложения. Такого вообще возникать не должно, но как мы понимаем, и ОС и офис содержат ошибки, да и вы (что более вероятно) можете пользоваться системными вызовами не правильно. Подобные ошибки — сущий кошмар, особенно когда они проявляются лишь на некоторых конфигурациях, при определенных условиях, их трудно поймать и надёжно воспроизвести.

Задачи механизмов обработки ошибок

  1. Обеспечить стабильную работу программы. Возникновение ошибки, появление которой вы не предусмотрели, приведёт в большинстве случаев к аварийному завершению всей программы или её части. При определенном уровне подобных ситуаций это ведёт к тому, что программой пользоваться становится невозможно.

  2. Информирование. Мало обработать ошибку и предотвратить завершение программы. Надо ещё и адекватно проинформировать пользователя о причинах нестандартного поведения программы. Частно причиной ошибок в программе являются некорректные действия пользователя, поэтому важно сообщать ему о них.

  3. Защита данных от повреждения. Программа обязана защищать от непреднамеренных повреждений результаты своей или пользовательской работы. Деструктивные действия должны быть снабжены соответствующими предупредительными диалоговыми окнами. Часто ошибка, не обработанная должным образом может повредить нужные данные.

Файл примера

Скачать

Код без обработки ошибок

Вот простой пример с потолка. Если вызвать Example_00, то она прекрасно отработает без ошибок и вернёт это:

В функцию GetCalories передаётся строка с блюдом, а она должна вернуть его калорийность, сверившись с таблицей в A1:B7.

Давайте поищем слабые места в этом коде. Первое, что должно прийти в голову — если мы ищем, то, что произойдёт, если мы не найдём? А произойдёт, конечно же, ошибка. Её инициирует метод Match.


Ещё одно слабое место этой подпрограммы: функция возвращает вещественный тип Double, и даже, если поиск оказался удачным, то в Cells(intRow, 2) может случайно находиться текстовая строка, а потому, когда вы числовому типу попытаетесь присвоить строковый тип, также произойдёт ошибка. И, если вы второй ошибки сможете избежать за счёт дополнительного оператора if с проверкой через IsNumber(), то избежать первой ошибки таким способом нельзя. Что же делать? А вот тут на сцену выходят операторы обработки ошибок.

Есть 2 подхода к обработке ошибок: автономный подход и выносной. Эти термины я придумал только что, чтобы проще было их обсуждать.

Автономный подход

Смысл автономного подхода в том, чтобы не выносить сор из избы. Если в подпрограмме возникла ошибка, то мы должны предположить, на каком месте она возникнет и поджидать её там с дубиной. С ошибкой, в этом случае, разбираются обычно в операторе, идущем сразу после потенциально опасного места. Давайте смотреть, как это может выглядеть:

Итак, что тут сделано:

  1. Сразу после объявления функции GetCalories_v1 идёт оператор on error resume next, который в случае возникновения в каком-либо месте ошибки, предписывает VBA просто передавать управление на следующий оператор, идущий после ошибочного.

  2. Мы объявили переменные. Необъявленные переменные получают тип Variant и значение по умолчанию Empty. Объявленные переменные числовых типов инициируются нулём, строковые — пустой строкой, то есть я наперёд знаю, что они содержат, а это хорошо для обработки ошибок.

  3. На вызове метода WorksheetFunction.Match у нас возникает ошибка, так как искомого значения в таблице нет. А это, между прочим, был оператор присваивания ( = ). Прежде, чем левой части оператора присваивания (intRow) что-то будет присвоено, необходимо вычислить правую часть оператора присваивания (WorksheetFunction.Match…), а поскольку в процессе этого вычисления возникает ошибка, то переменная intRow остаётся такой, какой была! А, как я уже сказал, VBA автоматически её инициализирует нулём до начала исполнения подпрограммы. Получается, что, если в этом операторе возникнет ошибка, то в intRow будет ноль. Если ошибки во время поиска не возникнет, то ноля там не будет ни при каких раскладах, так как строки на листе нумеруются с единицы.

  4. И вот этот ноль мы и контролируем, добавляя оператор If. Если intRow больше нуля, то WorksheetFunction.Match отработала штатно, а если нет — то работу подпрограммы надо прерывать, но об этом чуть позже.

  5. Далее мы помним, что Cells(intRow, 2) может теоретически вернуть строковое значение, которое вызовет ошибку Type missmatch при присвоении переменной типа Double (GetCalories_v1), поэтому мы вставляем дополнительную проверку промежуточной переменной varTemp тому, что она числовая. И если это так, то присваиваем GetCalories_v1 значение из varTemp.

  6. В случае возникновения любой ошибки внутри GetCalories_v1 она просто вернёт ноль. Почему ноль? Потому что переменная GetCalories_v1 тоже инициализируется нулём и об этом не надо заботиться, а в случае ошибки она останется в неприкосновенности.

  7. Соответственно родительский код (в нашем случае его роль играет процедура Example_01) должен проверить, а не вернёт ли GetCalories_v1 ноль, и быть готовым к этой ситуации.

  8. А вот теперь тонкий момент, который не все понимают. Почему я использовал промежуточные переменные intRow и varTemp? Вроде бы есть очевидный ответ — чтобы не вычислять значение выражений с Match и Cells 2 раза. Отчасти это, конечно, так. Но это, в данном случае, не главная причина. Главная причина в том, что такой код

    вызовет неправильное поведение программы. Если у нас Match вызовет исключение, то VBA передаст управление на СЛЕДУЮЩИЙ оператор, а следующий оператор в данном случае это то, что идёт после Then — присваивание переменной varTemp значения. Таким образом наша проверка на наличие ошибки сработает с точностью до наоборот, передав управление в ту часть кода, которая должна быть защищена от ситуации, когда Match не нашла строку в таблице. Вот почему важно в операторе If не иметь ничего такого, что могло бы вызвать ошибку.

  9. Как видите, в этом подходе мне зачастую даже нет необходимости проверять объект Err, чтобы понять, что произошла ошибка, так как я ориентируюсь на то, что промежуточные переменные остаются неинициализированными, что является показателем наличия ошибки.

Выносной подход

Данный метод основан на том, что, когда возникает ошибка, то VBA передаёт управление на специальный участок кода — обработчик ошибок, который обычно размещают в конце подпрограммы. Это может выглядеть так:

Обратите внимание, что:

  1. Оператор on error теперь в случае ошибки предписывает передавать управление на метку ErrorHandler, которая объявлена в конце кода процедуры GetCalories_v2

  2. В коде мы никак не заботимся о каких-либо проверках. Возникла ошибка? Иди на метку — там разберутся.

  3. Если ошибки не случилось, то, чтобы программа не стала исполнять строчки, предназначенные для обработки ошибок, перед меткой ErrorHandler обычно ставят оператор Exit Sub или Exit Function (в зависимости от типа подпрограммы).

  4. Принципиальный момент — наличие оператора On Error Resume Next сразу после метки ErrorHandler. Дело в том, что после того, как вы перешли на метку ErrorHandler, очень опасно иметь действующим оператор On Error GoTo ErrorHandler, так как, если у вас в обработчике ошибки случится любая ошибка, то управление будет передано опять на метку и, как нетрудно понять, образуется бесконечный цикл. Поэтому сразу после метки мы возможность возникновения цикла ликвидируем оператором On Error Resume Next.

Что лучше?

Какой метод лучше применять зависит от ваших предпочтений и конкретных ситуаций. Грамотную обработку ошибок можно сделать и так и эдак. Вот несколько соображений по преимуществам и недостакам данных подходов:

Автономный подход

Преимущества Недостатки
Есть возможность точно идентифицировать каждую конкретную проблему (если вы её предусмотрели), возникающую во время исполнения, что позволит вам дать самые точные инстркции пользователю для предотвращения появления исключения в будущем. Достаточно трудоёмок, так как подразумевает наличие большого количества проверок в коде. Каждое потенциально опасное действие должно быть снабжено соответствующим оператором If, в котором контролируется значение переменной или код ошибки.
Надо хорошо представлять себе ситуации, где могут возникнуть ошибки, в противном случае ряд ошибок вы просто не заметите на этапе отладки.
Необходимо больше кода, а также требуется опыт и фантазия.
Необходимо больше промежуточных переменных

Выносной подход

Преимущества Недостатки
Ни одна ошибка не проскочит незамеченной. Не смотря на то, что вы перехватите все ошибки, отреагировать на них правильно затруднительно, так как вы, по большому счёту, не знаете, на каком операторе произошла ошибка и почему.
Удобнее организовывать централизованный сбор логов по ошибкам в приложении. Однозначно, фаворит для больших проектов.

Кратко пробежимся по операторам, функциям и объектам VBA, которые предназначены для обработки ошибок времени исполнения программы.

Операторы

On Error { GoTo label | Resume Next | GoTo 0 }

Оператор on error управляет тем, на какой участок вашего кода будет передано управление в случае возникновения ошибки. Данный оператор можно вставить в любое место вашей программы или подпрограммы. Есть 3 варианта:

  1. On error goto label — после того, как этот оператор выполнен, ошибка, возникшая на других операторах программы приведёт к переходу на метку label.

  2. On error resume next — после такого оператора, VBA будет игнорировать возникшую ошибку и передавать управление на следующий оператор, стоящий за тем, в котором возникла ошибка.

  3. On error goto 0 — это режим по-умолчанию. В случае возникновения ошибки данный режим приведёт к появлению на экране стандартного обработчик ошибок VBA с кнопками End и Debug.

Resume { label | Next | [0] }

Данный оператор возобновляет выполнение программы. Применяется в выносном методе обработки ошибок.

  1. resume label— возобновление с метки label

  2. resume next — возобновление со следующего оператора

  3. resume или resume 0 — возобновление с оператора, вызвавшего ошибку. Это имеет смысл, если вы устранили ошибку в своём обработчике. На мой взгляд, на практике такое применяется крайне редко.

Goto label

Переход на метку. Может пригодиться, однако, использование меток в коде для чего-то большего, чем обработка ошибок, считается страшным моветоном.

Exit { Do | For | Function | Sub }

Досрочный выход из циклов (Do или For) и досрочный выход из подпрограмм (функции или процедуры). Могут пригодиться при обработке ошибок, но вообще это операторы и без того чрезвычайно полезны.

Объект Err

  1. Err — глобальный объект (его не надо объявлять, а можно сразу пользоваться), который содержит информацию о последней ошибке, случившейся в вашей программе. Проверяя Err сразу после возникновения исключения или после ситуации, которая могла привести к исключению, вы можете понять, что имело место на самом деле.

  2. Свойство Err.Number — содержит числовой код ошибки, по которому их различают в программе. Поскольку Number — свойство по умолчанию, то вы можете его опускать, то есть Err и Err.Number — это эквиваленты. Значение ноль говорит о том, что ошибки не произошло.

  3. Err.Description — содержит англоязычное краткое описание ошибки

  4. Err.Source — возвращает имя модуля, в котором возникла ошибка

  5. Err.Clear — сбрасывает последнюю ошибку. Err сбрасывается также при выполнении оператором Resume, Exit (любого типа кроме Do и For) и On Error.

  6. Err.Raise — искусственно вызывает исключение указанного в переданном параметре типа. Можно использовать для тестирования вашей подсистемы обработки ошибок.

P.S.

Лично я привык в своих программах использовать автономный подход и, возможно, поэтому я не совсем осознаю все преимущества выносного подхода. Буду рад прочесть в комментариях ваше мнение на этот счёт. Тема обработки ошибок данной статьёй, конечно, быть исчерпана не может, но она послужит вам хорошей стартовой точкой в этом важном деле.

Читайте также:

  • Работа с объектом Range

  • Работа с объектом Range (часть 2)

  • Sheet happens

  • Поиск границ текущей области

  • Массивы в VBA

  • Структуры данных и их эффективность

  • Автоматическое скрытие/показ столбцов и строк

Обработка ошибок

Если вы когда-нибудь учились водить автомобиль, то наверняка вспомните, что при первой посадке на водительское сиденье все внимание было приковано к трем деталям: рулю, педалям и рычагу переключения передач. Происходящее вне автомобиля уходило на второй план, так как вначале нужно было стронуться с места. По мере практики навыки вождения улучшались и эти три детали постепенно уходили на задний план. Как ни странно, но руль и рычаг переключения передач всегда оказывались там, куда вы не смотря протягивали руки, а ноги сами находили педали. Теперь все внимание стало занимать происходящее на дороге. Иными словами, вы стали опытным водителем.

В программировании все абсолютно также. Начинающие программисты больше обращают внимание на первые попавшиеся на глаза операторы, функции и другие элементы языка, а сам алгоритм уходит на задний план. Если программа скомпилировалась без ошибок, то это уже большое счастье, хотя это еще не означает, что программа работает правильно. По мере практики мышление программиста меняется, он начинает обращать внимание на мелочи, на форматирование программы, использует более эффективные алгоритмы и в результате всего этого допускает меньше ошибок. Подводя итоги, можно сказать, что начинающий программист просто пишет программу, а опытный программист пытается найти оптимальный алгоритм и предусмотреть поведение программы в различных ситациях. Однако, от ошибок никто не застрахован, поэтому очень важно знать как быстро найти ошибку.

Типы ошибок

Существуют три типа ошибок в программе:

  • синтаксические — это ошибки в имени оператора или подпрограммы, отсутствие закрывающей или открывающей кавычек и т. д., то есть ошибки в синтаксисе языка. Как правило, компилятор предупредит о наличии ошибки, а программа не будет выполняться совсем;
  • логические — это ошибки в логике работы программы, которые можно выявить только по результатам работы программы. Как правило, компилятор не предупреждает о наличии ошибки, а программа будет выполняться, так как не содержит синтаксических ошибок. Такие ошибки достаточно трудно выявить;
  • ошибки времени выполнения — это ошибки, которые возникают во время работы программы. В одних случаях ошибки времени выполнения являются следствием логических ошибок, а в других случаях причиной являются внешние события, например, нехватка оперативной памяти, отсутствие прав для записи в файл и др.

Инструкция On Error

Ошибки времени выполнения можно перехватывать внутри подпрограммы. Для этого используется инструкция On Error, которая имеет три формата:

  • On Error GoTo <Метка> — при возникновении ошибки управление передается инструкции, помеченной меткой <Метка>. Метка должна быть допустимым идентификатором, к которому предъявляются такие же требования как и к переменным. Внутри подпрограммы метка указывается в самом начале помечаемой строки и после метки ставится двоеточие. В качестве примера создадим функцию для деления двух целых чисел. Внутри функции предусмотрим обработку ошибки деления на 0:
Function Деление(x As Integer, y As Integer) As Double
   On Error GoTo ПриОшибке
   Деление = x / y
   Exit Function
ПриОшибке:
   Деление = 0
End Function

Если при вызове функции во втором параметре передать значение 0, то управление будет передано в строку, помеченную с помощью метки ПриОшибке. Обратите внимание на то, что метка расположена после инструкции Exit Function. В этом случае код после инструкции Exit Function будет выполнен только в том случае, если возникнет ошибка;

  • On Error Resume Next — при возникновении ошибки управление передается следующей инструкции;
  • On Error GoTo 0 — отключает перехват ошибок.

Если внутри подпрограммы не предусмотрен перехват ошибки, то при возникновении ошибки работа программы прерывается и выводится стандартное окно с описанием и несколькими кнопками: Continue (продолжить), End (завершить выполнение программы), Debug (перейти в режим отладки) и Help (вывод справки).

Инструкция Resume

Инструкция Resume позволяет указать куда следует переходить после обработки ошибки. Инструкция имеет несколько форматов:

  • Resume [0] — управление передается инструкции, вызвавшей ошибку;
  • Resume Next — управление передается инструкции, следующей за инструкцией, вызвавшей ошибку;
  • Resume <Метка> — управление передается инструкции, помеченной меткой <Метка>.

Если инструкция Resume не указана, то выполняется только код внутри обработчика ошибки и производится выход из подпрограммы.

Получение информации об ошибке и генерация ошибки

Вся информация о последней ошибке доступна через объект Err. Объект содержит следующие свойства:

  • Number — код ошибки, например, код 11 для ошибки деления на 0. Если ошибки не произошло, то свойство содержит значение 0;
  • Description — описание ошибки, например, строка "Division by zero" для ошибки деления на 0. Пример вывода кода и описания ошибки:
Debug.Print Err.Number; Err.Description
  • Source — название текущего проекта;
  • HelpFile — путь к файлу справки;
  • HelpContext — идентификатор раздела в справочном файле;
  • LastDLLError — системный код ошибки при работе с DLL.

Объект Err содержит следующие методы:

  • Clear() — очищает всю информацию о последней ошибке. Этот метод следует вызвать после успешной обработки ошибки. Информация об ошибке автоматически очищается при выходе из подпрограммы и ряде других случаев;
  • Raise() — позволяет сгенерировать ошибку в программе. Формат метода:
Raise Number[, Source][, Description][, HelpFile][, HelpContext]

В параметре Number указывается код генерируемой ошибки (целое число от 0 до 65 535). Коды от 0 до 512 зарезервированы под системные ошибки, а остальные коды можно использовать под пользовательские ошибки. Чтобы сгенерировать ошибку с пользовательским кодом необходимо сложить код с константой vbObjectError. Остальные параметры являются необязательными и полностью аналогичны одноименным свойствам объекта Err. Пример генерации и обработки пользовательской ошибки:

Sub ГенерацияОшибки()
   On Error GoTo ПриОшибке
   Err.Raise vbObjectError + 513
   Exit Sub
ПриОшибке:
   Debug.Print Err.Number; Err.Description
   ' -2147220991 Automation error
End Sub

Способы поиска ошибок в программе

В предыдущих разделах мы научились обрабатывать ошибки времени выполнения. Однако, наибольшее количество времени программист затрачивает на другой тип ошибок — логические ошибки. В этом случае программа компилируется без ошибок, но результат выполнения программы не соответствует ожидаемому результату. Ситуация еще более осложняется, когда неверный результат проявляется лишь периодически, а не постоянно. Инсценировать такую же ситуацию, чтобы получить этот же неверный результат, бывает крайне сложно и занимает очень много времени. В этом разделе мы рассмотрим лишь «дедовские» (но по прежнему актуальные) способы поиска ошибок, а современные способы отладки приложений, доступные в VBA, изучим в следующем разделе.

Первое, на что следует обратить внимание, — на объявления переменных. Например, рассмотрим простой пример:

Как вы думаете, какое значение отобразится в окне Immediate после выполнения этого кода? Думаете, что число 10? Не факт! Вот тут-то и кроется проблема не видная на первый взгляд. В первой инструкции присваивается значение переменной x, имя которой набрано на английской раскладке клавиатуры, а вот во второй инструкции выводится значение переменной x, имя которой набрано на русской раскладке клавиатуры. В результате значение присваивается одной переменной, а выводится значение другой переменной. Такие ситуации очень часто встречаются в программах на языке VBA, так как объявлять переменную не обязательно. Чтобы избежать такой ситуации следует обязательно объявлять переменные явным образом. Контроль за соблюдением этого правила можно возложить на компилятор, добавив в начале модуля следующую инструкцию:

При наличии инструкции компилятор производит проверку объявления всех переменных. Если переменная не была объявлена явным образом, то компилятор выведет сообщение об ошибке и выполнение программы будет остановлено. Таким образом, код должен выглядеть следующим образом:

Option Explicit
...
Dim x As Integer
x = 10
Debug.Print x ' 10

Далее следует обратить внимание на форматирование кода. Начинающие программисты обычно не обращают на это никакого внимания, считая этот процесс лишним. А на самом деле зря! Компилятору абсолютно все равно, разместите вы все инструкции на одной строке или выполните форматирование кода. Однако, при поиске ошибок форматирование кода позволит найти ошибку гораздо быстрее.

Перед всеми инструкциями внутри блока должно быть расположено одинаковое количество пробелов. Обычно используют три или четыре пробела. От применения символов табуляции лучше отказаться. Если все же используете, то не следует в одном файле совмещать и пробелы и табуляцию. Для вложенных блоков количество пробелов умножают на уровень вложенности. Если для блока первого уровня вложенности использовалось три пробела, то для блока второго уровня вложенности должно использоваться шесть пробелов, для третьего уровня — девять пробелов и т. д. Пример форматирования вложенных блоков приведен в листинге 11.1.

Dim Массив As Variant, i As Integer, j As Integer
Массив = Array(Array(0, 1), Array(2, 3), Array(4, 5))
For i = 0 To 2
   For j = 0 To 1
      Debug.Print Массив(i)(j)
   Next
Next

Длина одной строки не должна содержать более 80 символов. Если количество символов больше, то следует выполнить переход на новую строку. При этом продолжение смещается относительно основной инструкции на величину отступа или выравнивается по какому-либо элементу. Иначе приходится пользоваться горизонтальной полосой прокрутки, а это очень неудобно при поиске ошибок.

Если программа слишком большая, то следует задуматься о разделении программы на отдельные подпрограммы или классы, которые выполняют логически законченные действия. Помните, что отлаживать отдельную подпрограмму гораздо легче, чем «спагетти»-код. Причем прежде чем вставить подпрограмму (или класс) в основную программу ее следует протестировать в отдельном проекте, передавая подпрограмме различные значения и проверяя результат ее выполнения.

Обратите внимание на то, что форматирование кода должно выполняться при написании кода, а не во время поиска ошибок. Этим вы сократите время поиска ошибки и скорее всего заметите ошибку еще на этапе написания. Если все же ошибка возникла, то вначале следует инсценировать ситуацию, при которой ошибка проявляется. После этого можно начать поиск ошибки.

Причиной периодических ошибок чаще всего являются внешние данные. Например, если числа получаются от пользователя, а затем производится деление чисел, то вполне возможна ситуация, при которой пользователь введет число 0. Деление на ноль приведет к ошибке. Следовательно, все данные, которые поступают от пользователей, должны проверяться на соответствие допустимым значениям. Если данные не соответствуют, то нужно вывести сообщение об ошибке, а затем повторно запросить новое число или прервать выполнение всей программы. Кроме того, нужно обработать возможность того, что пользователь может ввести вовсе не число, а строку.

Метод Print() объекта Debug удобно использовать для вывода промежуточных значений. В этом случае значения переменных вначале выводятся в самом начале программы и производится проверка соответствия значений. Если значения соответствуют, то инструкция с методом Print() перемещается на следующую строку программы и опять производится проверка и т. д. Если значения не совпали, то ошибка возникает в инструкции, расположенной перед инструкцией с методом Print(). Если это пользовательская подпрограмма, то проверку значений производят внутри подпрограммы, каждый раз перемещая инструкцию с выводом значений. На одном из этих многочисленных этапов ошибка обычно обнаруживается. В больших программах можно логически догадаться о примерном расположении инструкции с ошибкой и начать поиск ошибки оттуда, а не с самого начала программы.

Инструкции для вывода промежуточных значений можно расставить уже при написании программы, не дожидаясь возникновения ошибки. В этом случае в начале программы объявляется константа с помощью инструкции #Const, а внутри программы производится проверка значения константы:

Проверить значение константы позволяет следующая конструкция:

#If MY_DEBUG Then
   ' Здесь размещаем инструкции вывода значений
#End If

Таким образом, меняя значение константы MY_DEBUG с 1 на 0, можно отлючать вывод всех промежуточных значений.

Сделать поиск ошибок более эффективным позволяет отладчик, встроенный в редактор VBA. С его помощью можно выполнять программу по шагам, при этом контролируя значения переменных на каждом шагу. Отладчик позволяет также проверить, соответствует ли порядок выполнения инструкций разработанному ранее алгоритму.

Прежде чем начать отладку необходимо пометить строки внутри программы с помощью точек останова. Для добавления точки останова делаем строку активной, а затем из меню Debug выбираем пункт Toggle Breakpoint. Слева от строки появится кружок, обозначающий точку останова. Добавить точку останова можно еще быстрее. Для этого достаточно щелкнуть слева от строки левой кнопкой мыши. Повторный щелчок позволяет удалить точку останова. Кроме того, для добавления или удаления точки отстанова можно воспользоваться клавишей <F9>. Чтобы удалить все точки останова следует из меню View выбрать пункт Clear All Breakpoints.

Когда точки останова расставлены можно начать отладку. Для этого запускаем программу на выполнение обычным способом. При достижении точки останова выполнение программы прерывается и отладчик ожидает дальнейших действий программиста. Инструкция, которая будет выполняться на следующем шаге, помечается желтой стрелкой слева от строки.

В режиме прерывания можно посмотреть значения различных переменных в окне Locals. Если окно не отображается, то отобразить его можно выбрав в меню View пункт Locals Window. Посмотреть значение переменной можно также если навести указатель мыши на переменную. Значение переменной отобразится во всплывающей подсказке.

При отладке можно контролировать значения отдельных переменных, а не всех сразу. Для этого следует выделить название переменной и из меню Debug выбрать пункт Add Watch. Можно также выделить название переменной и из контектного меню выбрать пункт Add Watch. В открывшемся окне устанавливаем флажок Watch Expression и нажимаем кнопку OK. Значение переменной будет отображаться в окне Watches. Чтобы отобразить окно Watches из меню View выбираем пункт Watch Window. Чтобы отменить отслеживание нужно выделить строку в окне Watches и нажать клавишу <Delete>.

Для пошагового выполнения программы предназначены следующие пункты в меню Debug или соответствующие кнопки на панели инструментов Debug (View | Toolbars | Debug):

  • Step Into (клавиша <F8>) — выполняет переход к следующей инструкции;
  • Step Over — выполняет одну инструкцию. Если в этой инструкции производится вызов подпрограммы, то подпрограмма выполняется за один шаг и отладчик переходит в режим ожидания после выхода из подпрограммы;
  • Step Out — при заходе в подпрограмму этот пункт позволяет выполнить подпрограмму за один шаг и выйти из нее. Отладчик переходит в режим прерывания после выхода из подпрограммы;
  • Run To Cursor — выполняет переход к инструкции, в которой расположен курсор.

Если необходимо посмотреть последовательность вызова подпрограмм, то следует открыть окно Call Stack, выбрав в меню View пункт Call Stack.

Подача звукового сигнала

При возникновении ошибки или при неправильном вводе данных имеет смысл привлечь внимание пользователя звуковым сигналом. Сгенерировать звуковой сигнал позволяет инструкция Beep. Пример:

Dim Результат
Beep
Результат = InputBox("Необходимо ввести значение")

Visual Basic for Applications (VBA)
Статьи по Visual Basic for Applications (VBA)

Just like any other programming language, VBA has no luck when it comes to errors and you have to deal with them, no matter what. They may come from different sources, like bad coding, impossible operations (like zero division), or unexpected errors.

The best way to deal with it is to have a proper understanding of all the possible outcomes of the result that you could get with code. Look at the below example where we have a VBA code that calculates the square root of the number using the value which you have in the selected cell.

Sub Square_Root()
ActiveCell.Value = ActiveCell.Value ^ (1 / 2)
End Sub

But if the active cell has a value other than a number, you’ll get a run-time error, just like below.

Error Settings in VBA (Error Trapping)

In the VBA option, you can configure the setting to deal with errors before you start writing codes. To open the VBA settings, go to Tools ➤ Options ➤ Generals ➤ Error Trapping. Here you have three options which you can use.

  • Break on All Errors: If you have this option activated, VBA will stop the code for all types of errors even if you have used all kinds of error handling techniques.
  • Break-in Class Module: With this option, VBA will stop all your codes that are not handled by any technique. And if you’re using objects such as Userforms, it will also break within those objects and highlight the exact line where the error is.
  • Break on Unhandled Errors: This is the default setting that helps you to know about all the errors where you are not using any error handling technique and stop the code for all the unhandled errors. (But, if you’re using objects such as Userforms, this will not highlight the line causing the error in the object but will only highlight the line that’s referring to that object).

Types of VBA Errors

To understand VBA errors, you can split them into four categories, and below is the explanation of these types of errors.

1. Syntax Errors

While writing VBA code you need to follow a particular Syntax and when you skip it or don’t write it in the way it should be you can face SYNTAX error (also called Language error). It’s like typos that you do while writing your codes.

Well, VBA helps you by pointing out these errors by showing an error message. You just need to make sure you have “Auto Syntax Check” activated in your VB editor.

Go to the Tool ➤ Options and make sure to tick the “Auto Syntax Check”. With this, whenever you make a SYNTAX error, VBA will show an error message.

But if “Auto Syntax Check” is disabled VBA still highlights the line of code with the error but won’t show the error message.

2. Compile Errors

It comes when you write code to perform an activity, but that activity is not valid or can’t be performed by VBA. The best example is where you have a code using the IF statement but missed to add END IF at the end of the statement and now when you run this VBA will show you a compilation error message.

Apart from this, there are some other examples of compile errors:

  • Using For without Next (For Next).
  • Select without End Select (Select Case).
  • Not Declaring a Variable when you have “Option Explicit” enabled.
  • Calling a Sub/Function that does not exist.

3. Runtime Errors

A runtime error occurs at the time of executing the code. Remember the example, I shared with you above when the code calculated the square root of a number.

When a runtime error occurs while running code, it stops the code and shows you the error dialog box and that error box talks about the nature of the error you have. Let’s say you have written a code that opens a workbook from the location which you have specified but now that workbook is relocated or deleted by someone.

So, when you run the code, VBA will show you a runtime error as it can’t find that file on that location. The message you get in a run time error describes the reason which helps you to understand the reason for the error.

And when a runtime error occurs it stops the execution of the code. If you click on the “Debug” button it shows you the line of code that has that error by highlighting it yellow. Or you can click on the “End” button to stop the code to execute and close the error message.

4. Logical Error

It’s not an error but a mistake while writing code. These types of errors sometimes can give you nuts while finding them and correcting them.

Let’s say you write code and while declaring a variable you used the wrong data type, or you have used the wrong calculation steps. In this case, your code will work fine, and you won’t find this error easily. The best way to deal with this kind of problem is to run each line of code one by one.

Using Debug Tools in VBA

VBA provides you a set of tools to debug your code and remove bugs from your codes.

1. Compile VBA Project

In Visual Basic Editor, there’s an option that you can use instantly after completing your code. These compile options scan each line of your code and show a message box if there is an error in your code.

Note: Compile VBA option only traces Syntax and Compile errors, not runtime errors as these errors only rise when a code is running. To use Compile VBA Project, go to ➤ Debug ➤ Compile VBA Project.

Once you run “Compile VBA Project” and you have no error in your code, the options will be greyed out.

2. Run Each Line of Code One by One

This is how I do it. When I complete a code, I simply run it line by line to check if there’s an error occurring. It may take time, but it helps you to get to about all the errors (Syntax, Compile, and Run-Time).

On the “Debug Toolbar”, there’s a button “Step In” which you can use to execute a code line by line or you can simply press F8 to execute a single line and then press it again to execute the next line in the code.

Using “On ERROR” Statement to Handle VBA Errors

It’s important to check your codes and find possible errors in all the debugging ways you have. But, the best and most effective way is to create error-handling statements that can deal with an error and make your code flawless while executing. Let’s learn about these statements. When an error occurs in a VBA code the best possible ways to handle that error can be:

  • Let the VBA ignore the error and execute the code
  • Let a special set of statements to run when an error occurs.

In both solutions, you can use “On Error” statements. Below four “On Error” statements that you can use. And now, let’s look at each statement one by one.

1. On Error Resume Next

This simple line of code lets VBA continue executing the code despite the occurrence of an error. The IDEA is simple: Move to the next line of the code if there’s an error found somewhere while executing.

In the below code, you have two lines of code:

  • The first line says the value  of cell A1 is 25 divided by 0
  • And the second line says the value cell A2 is 10 divided by 5

Now there’s a problem with the code which you have inline one. As you know if you divide anything with 0 the result will be an error.  So, when you run this code VBA will show an error message “Run-time error ‘11’ Division by Zero” and stop the execution.

But when you add the “On Error Resume Next” at the very beginning of the code and run the code, VBA simply skips that line of code where the error occurs and continues with the second line and add that value in cell A2.

Sub myDivide()
On Error Resume Next
    Range("A1").Value = 25 / 0
    Range("A2").Value = 10 / 5
End Sub

So, whenever you want your code to get executed despite an error occurring anywhere simply use the “On Error Resume Next” statement in your code.

But here’s one more thing you need to note down: It will only skip errors that occur after it.

Let’s say if an error occurs at line 5 and you have added “On Error Resume Next” on line 8 then it would not skip that error.  So, the best way is to add it as the first line of the code in the procedure.

2. On Error GoTo 0

It’s the default behavior of VBA that when an error occurred it stops the execution of the code.

Well, using “On Error GoTo 0” make no difference in your code. VBA will simply stop the code and show a message with a description of the error. Then why would I bother to use it? Smart Question. Let’s use the example you have used above in “On Error Resume Next”.

In this code whenever an error will occur VBA will resume to the next line of code and run it and you won’t see any error message. But let’s say you have more lines in your code and you don’t want to surpass those lines if there’s an error in the code.

So, if you enter “On Error GoTo 0” after the second line of code it will restore the VBA’s default error handler which shows error messages each time an error occurs.

3. On Error GoTo [Label]

Think about a place in a building where you can head up in an emergency. In the same way, using “On Error GoTo [Label]”, you can simply create a separate block of code in your main code to deal with an error.

Actually, “On Error GoTo [Label]” is a far better and more convenient way to deal with errors. In the below code, you have “On Error GoTo Oh!Error” now in this line statement the word “Oh!Error” is the label.

If you look at the end of the code where you have a specific starting with the label name and then a code for a message box with a message about the code.

Now, what happens if an error occurs the VBA will jump to the label “Oh!Error” and run the block of code which you have after that label.

But there’s one thing you need to take care of: If an error doesn’t occur even then the label you have in your code will get executed. There are two things you need to do:

  • First, make sure to add your Error label at the end of the code.
  • Second, add an “Exit Sub” before the error label.

With this, you’ll benefit in both situations. Let’s say if an error occurs and VBA jumps to the label you specified there would only be code from the label itself to code. And if an error doesn’t occur “Exit Sub” statement which you have before the label will exit the procedure without executing the error label.

4. On Error GoTo -1

Before we get into this, let me share something with you.  When an error occurs in a code VBA stores that error log in its memory and only clears it when the routine ends.

O VBA! Live in Present

To deal with the second error in a VBA code you need to clear the first error from VBA’s memory. In the below code, you have two “On Error GoTo [Label]” statements that deal with errors from two different blocks of code.

But if you run this code, when the second error VBA won’t jump to the label which you have defined and instead show the error message “Type Mismatch”.

Sub Square_Root()
On Error GoTo myError1
Range("A1").Value = Range("A1").Value ^ (1 / 2)
myError1:
MsgBox "There's some problem with the value you have in the cell A1."
On Error GoTo myError2
Range("A2").Value = Range("A2").Value ^ (1 / 2)
myError2:
MsgBox "There's some problem with the value you have in the cell A2."
End Sub

To fix this problem you can use “On Error GoTo -1” which makes VBA remove the current error from its storage memory.

Sub Square_Root()
 On Error GoTo myError1
 Range("A1").Value = Range("A1").Value ^ (1 / 2)
 myError1:
 MsgBox "There's some problem with the value you have in the cell A1."
 On Error GoTo -1
 On Error GoTo myError2
 Range("A2").Value = Range("A2").Value ^ (1 / 2)
 myError2:
 MsgBox "There's some problem with the value you have in the cell A2."
 End Sub

Now when you run this code, “On Error GoTo -1” removes the error from the memory and VBA deals with the error in the second statement as you want.

What Else Do I Need to Know to Handle Errors in VBA?

Apart from using error handling techniques, there are a few other things that you can use to deal with errors in a better way.

Err Object

When an error occurred while executing of code, you can use the Err object to get details about that error. There are a few properties and methods which you can use with Err object. Let’s learn them one by one.

Properties

Below are the properties which you can use with the Err object:

  • Err.Number: With an error occurred there’s a number stored in the Err Object. In the below code, when occurred the message box will show the error number.  
  • Err.Description: This property shows the description of the error which can help you to understand the reason for the error.
  • Err.Source: This property shows you in which project the error has occurred.
  • Err.HelpContext: This property returns the help context id for the error in the help file.
  • Err.HelpContext: This is a string value for the location of the help file.

Normally when you are dealing with errors using error handling techniques you won’t be using the Err Object that much in your codes. But below is a simple example to use it.

Sub Square_Root()
On Error GoTo myError1
    Range("A1").Value = Sqr(Range("A1").Value)
Exit Sub
myError1:
    MsgBox "There's some problem with the value you have in the cell A1." & vbCrLf & _
                "Error Number: " & Err.Number  & vbCrLf & _
                "Error Description: " & Err.Description
End Sub

When you run the above code, and if an error occurred, it will show a message box with the error number and description of the error.

Methods

With Err Object there are two methods that you can also use.

  • Err.Clear: This method clears the error number and error description from VBA’s memory (It’s different from “On Error GoTo -1” as it doesn’t completely reset the error).
  • Err.Raise: With this method, you can generate a run time error in your code intentionally, and below is the syntax which needs to follow:

Err.Raise [number], [source], [description], [helpfile], [helpcontext]

Quick Tips on Error Handling

Here are a few quick tips which you can use to deal with VBA errors in a better way.

  • Use “On Error Resume Next” only when you know for sure about an error to occur and it’s OK to skip the line of code with an error and it’s safe to skip to the next line.
  • The best way to deal with run-time errors is by using “Error Handler” with “On Error GoTo [Label]”. This ensures that whenever the error occurs you will know about it, but it won’t show that nasty error message.
  • Whenever you use the error handler make sure to use “Exit Sub” before it.

There’s More

Below are some of the links which could be useful for you to learn more about error handling in VBA, make sure to check out all of these:

Type Mismatch (Error 13) | Runtime (Error 1004) | Object Required (Error 424) | Out of Memory (Error 7) | Object Doesn’t Support this Property or Method (Error 438) | Invalid Procedure Call or Argument (Error 5) | Overflow (Error 6) | Automation error (Error 440) | VBA Error 400 | VBA Subscript Out of Range Runtime Error 9

No matter how experienced you’re with VBA coding, errors are always going to be a part of it.

The difference between a novice and an expert VBA programmer is that the expert programmers know how to effectively handle and use errors.

In this tutorial, I will show you various ways you can use to handle errors effectively in Excel VBA.

Before we get into VBA error handling, let’s first understand the different types of errors you are likely to encounter when programming in Excel VBA.

Types of VBA Errors in Excel

There are four types of errors in Excel VBA:

  1. Syntax errors
  2. Compilation errors
  3. Runtime errors
  4. Logical Errors

Let’s quickly understand what these errors are and when you’re likely to encounter these.

Syntax Error

A syntax error, as the name suggests, occurs when VBA finds something wrong with the syntax in the code.

For example, if you forget a part of the statement/syntax that is needed, then you will see the compile error.

In the below code, as soon as I hit enter after the second line, I see a compile error. This is because the IF statement needs to have the ‘Then‘ command, which is missing in the below code.

Excel VBA Compile Error - Expected Then or Goto

Note: When you are typing a code in Excel VBA, it checks for each sentence as soon as you hit enter. If VBA finds something missing in the syntax, it instantly shows a message with some text that can help you understand the missing part.

To make sure you see the syntax error whenever there is something missing, you need to make sure Autosyntax check is enabled. To do this, click on ‘Tools’ and then click on ‘Options’. In the options dialog box, make sure that the ‘Auto Syntax Check’ option is enabled.

AutoSyntax Option to be checked

If the ‘Auto Syntax Check’ option is disabled, VBA will still highlight the line with the syntax error in red, but it will not show the error dialog box.

Compile Error

Compile errors occur when something is missing that is needed for the code to run.

For example, in the below code, as soon as I try to run the code, it will show the following error. This happens as I have used the IF Then statement without closing it with the mandatory ‘End If’.

Excel VBA Error - Block If Without End If

A syntax error is also a type of compile error. A syntax error occurs as soon as you hit enter and VBA identifies that something is missing. A compilation error can also occur when VBA doesn’t find anything missing while typing the code, but it does when the code is compiled or executed.

VBA checks each line as you’re typing the code and highlights the syntax error as soon as the line is incorrect and you hit enter. Compile errors, on the other hand, are only identified when the entire code is analyzed by VBA.

Below are some scenarios where you’ll encounter the compile error:

  1. Using an IF Statement without the End IF
  2. Using For statement with the Next
  3. Using Select statement without using the End Select
  4. Not declaring the variable (this works only when Option Explicit is enabled)
  5. Calling a Sub/Function that does not exist (or with wrong parameters)

Note about ‘Option Explicit’: When you add ‘Option Explicit’, you will be required to declare all the variables before running the code. If there is any variable that has not been declared, VBA would show an error. This is a good practice as it shows an error in case you have a misspelled variable. You can read more about Option Explicit here.

Run Time Errors

Runtime errors are those that occur when the code is running.

Run time errors will occur only when all the syntax and compile errors are being taken care of.

For example, if you run code that is supposed to open an Excel workbook, but that workbook is unavailable (either deleted or name changed), your code would give you a runtime error.

Runtime error in Excel VBA

When a runtime error occurs, it will stop the code and show you the error dialog box.

The message in the Run-time error dialog box is a little more helpful. It tries to explain the problem that can help you correct it.

If you click on the Debug button, it will highlight the part of the code that is leading to the error.

Click on Debug to Highlight the code

If you have corrected the error, you can click on the Run button in the toolbar (or press F5) to continue running the code from where it left.

Or you can also click on the End button to come out of the code.

Important: In case you click the End button in the dialog box, it will stop the code at the line at which is encountered. However, all the lines of code before that would have been executed.

Logical Errors

Logical errors would not make your code stop but can lead to wrong results. These could also be the most difficult types of errors to troubleshoot.

These errors are not highlighted by the compiler and need to be manually tackled.

One example of logical error (that I often find myself stuck with) is running into an endless loop.

Another example could be when it gives a result which is wrong. For example, you may end up using a wrong variable in the code or add two variables where one is incorrect.

There are a few ways I use to tackle logical errors:

  1. Insert Message Box at some place in the code and highlight values/data that can help understand if eberything is going as expected.
  2. Instead of running the code at one go, go through each line one by one. To do this, click anywhere in the code and press F8. you would notice that each time you press F8, one line gets executed. This allows you to go through the code one line at a time and identify the logical errors.

Using Debug to Find Compile/Syntax Errors

Once you’re done with the code, it’s a good practice to first compile it before running.

To compile a code, click on the Debug option in the toolbar and click on Compile VBAProject.

Debug Compile VBAProject

When you compile a VBA project, it goes through the code and identifies errors (if any).

In case it finds an error, it will show you a dialog box with the error. It finds errors one by one. So if it finds an error and you have corrected it, you need to run compile again to find other errors (if there are).

When you’re code is free of errors, the Compile VBAProject option will be greyed out.

Note that Compiling will only find ‘Syntax’ errors and ‘Compile’ errors. It will NOT find the run-time errors.

When you’re writing VBA code, you don’t want the errors to crop up. To avoid this, there are many error-handling methods you can use.

In the next few sections of this article, I will be covering the methods you can use for VBA error handling in Excel.

Configure Error Settings (Handled Vs Unhandled Errors)

Before you start working with your code, you need to check for one setting in Excel VBA.

Go to the VBA toolbar and click on Tools and then click on Options.

In the Options dialog box, click on the General tab and make sure that within the ‘Error Trapping’ group, ‘Break on Unhandled Errors’ is checked.

Let me explain the three options:

  1. Break on All Errors: This will stop your code on all types of errors, even when you have used the techniques to handle these errors.
  2. Break in Class Module: This will stop your code on all unhandled errors, and at the same time, if you’re using objects such as Userforms, it will also break within those objects and highlight the exact line causing the error.
  3. Break on Unhandled Errors: This will stop your code only for those errors that are not handled. This is the default setting as it ensures any unhandled errors are brought to your notice. If you’re using objects such as Userforms, this will not highlight the line causing the error in the object, but will only highlight the line that’s referring to that object.

Note: If you work with objects such as Userforms, you can change this setting to ‘Break on Class Modules’. The difference between #2 and #3 is that when you use Break in Class Module, it will take you to the specific line in the object that is causing the error. You can also choose to go with this instead of ‘Break on Unhandled Errors’.

So in a nutshell – if you’re just starting with Excel VBA, ensure ‘Break on Unhandled Errors’ is checked.

VBA Error Handling with ‘On Error’ Statements

When your code encounters an error, there are a few things you can do:

  1. Ignore the error and let the code continue
  2. Have an error handling code in place and run it when an error occurs

Both of these error handling methods ensures that the end user will not get to see an error.

There are a few ‘On Error’ statements that you can use to get these done.

On Error Resume Next

When you use ‘On Error Resume Next’ in your code, any encountered error will be ignored and the code will continue to run.

This error handling method is used quite often, but you need to be cautious when using it. Since it completely ignores any error that may occur, you may not be able to identify the errors that need to be corrected.

For example, if the below code is run, it will return an error.

Sub AssignValues()
x = 20 / 4
y = 30 / 0
End Sub

This happens because you can not divide a number by zero.

But if I use the ‘On Error Resume Next’ statement in this code (as shown below), it will ignore the error and I will not know that there is an issue that needs to be corrected.

Sub AssignValues()
On Error Resume Next
x = 20 / 4
y = 30 / 0
End Sub

On Error Resume Next should be used only when you clearly know the kind of errors your VBA code is expected to throw and it’s alright to ignore it.

For example, below is the VBA event code that would instantly add the date and time value in cell A1 of a newly inserted sheet (this code is added in the worksheet and not in a module).

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Sh.Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss")
End Sub

While this works great in most cases, it would show an error if I add a chart sheet instead of a worksheet. Since a chart sheet does not have cells, the code would throw an error.

So, if I use the ‘On Error Resume Next’ statement in this code, it will work as expected with worksheets and do nothing with chart sheets.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error Resume Next
Sh.Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss")
End Sub

Note: On Error Resume Next Statement is best used when you know what kind of errors you’re likely to encounter. And then if you think it’s safe to ignore these errors, you can use it.

You can take this code to the next level by analyzing if there was an error, and displaying a relevant message for it.

The below code would show a message box that would inform the user that a worksheet has not been inserted.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
On Error Resume Next
Sh.Range("A1") = Format(Now, "dd-mmm-yyyy hh:mm:ss")
If Err.Number <> 0 Then
MsgBox "Looks like you inserted a chart sheet" & vbCrLf & "Error - " & Err.Description
End If
End Sub

‘Err.Number’ is used to get the error number and ‘Err.Description’ is used to get the error description. These will be covered later in this tutorial.

On Error GoTo 0

‘On Error GoTo 0’ will stop the code on the line that causes the error and shows a message box that describes the error.

In simple terms, it enables the default error checking behavior and shows the default error message.

Then why even use it?

Normally, you don’t need to use ‘On Error Goto 0’, but it can be useful when you use it in conjunction with ‘On Error Resume Next’

Let me explain!

The below code would select all the blank cells in the selection.

Sub SelectFormulaCells()
Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub

But it would show an error when there are no blank cells in the selected cells.

So to avoid showing the error, you can use On Error Resume next’

Now, it will also show any error when you run the below code:

Sub SelectFormulaCells()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
End Sub

So far, so good!

The problem arises when there is a part of the code where error can occur, and since you’re using ‘On Error Resume Next’, the code would simply ignore it and move to the next line.

For example, in the below code, there would no error prompt:

Sub SelectFormulaCells()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
' .. more code that can contain error
End Sub

In the above code, there are two places where an error can occur. The first place is where we are selecting all blank cells (using Selection.SpecialCells) and the second is in the remaining code.

While the first error is expected, any error after that is not.

This is where On Error Goto 0 comes to rescue.

When you use it, you reset the error setting to default, where it will start showing errors when it encounters it.

For example, in the below code, there would be no error in case there are no blank cells, but there would be an error prompt because of ’10/0′

Sub SelectFormulaCells()
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
On Error GoTo 0
' .. more code that can contain error
End Sub

Error Checking restored with GoTo 0

On Error Goto [Label]

The above two methods – ‘On Error Resume Next’ and ‘On Error Goto 0’ – doesn’t allow us to truly handle the error. One makes the code ignore the error and the second one resume error checking.

On Error Go [Label] is a way with which you can specify what you want to do in case your code has an error.

Below is the code structure that uses this error handler:

Sub Test()

On Error GoTo Label:
X = 10 / 0    'this line causes an error
' ....your remaining code goes here
Exit Sub

Label:
    ' code to handle the error
End Sub

Note that before the Error handling ‘Label’, there is an Exit Sub. This ensures that in case there are no errors, the sub is exited and the ‘Label’ code is not executed. In case you don’t use Exit Sub, it will always execute the ‘Label’ code.

In the example code below, when an error occurs, the code jumps and executes the code in the handler section (and shows a message box).

Sub Errorhandler()
On Error GoTo ErrMsg
X = 12
Y = 20 / 0
Z = 30
Exit Sub
ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
End Sub

Note that when an error occurs, the code has already run and executed the lines before the line causing the error. In the above example, the code sets the value of X as 12, but since the error occurs in the next line, it doesn’t set the values for Y and Z.

Once the code jumps to the error handler code (ErrMsg in this example), it will continue to execute all the lines in and below the error handler code and the exit the sub.

On Error Goto -1

This one is a bit complicated, and in most cases, you’re unlikely to use this.

But I will still cover this as I have faced a situation where this was needed (feel free to ignore and jump to the next section if you’re only looking for basics).

Before I get into the mechanics of it, let me try and explain where can it be useful.

Suppose you have a code where an error is encountered. But all is good as you have one error handler in place. But what happens when there is another error in the error handler code (yeah.. somewhat like the inception movie).

In such a case, you can not use the second handler as the first error has not been cleared. So while you have handled the first error, in VBA’s memory it still exists. And the VBA memory only has a place for one error – not two or more than that.

In this scenario, you can use On Error Goto -1.

It clears the error and frees up VBA memory to handle the next error.

Enough talk!

Let’s me explain now by using examples.

Suppose I have the below code. This will throw an error as there is division by zero.

Sub Errorhandler()
X = 12
Y = 20 / 0
Z = 30
End Sub

So to handle it, I use an error handler code (with the name ErrMsg) as shown below:

Sub Errorhandler()

On Error GoTo ErrMsg

X = 12
Y = 20 / 0
Z = 30
Exit Sub

ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
End Sub

Division by 0 error message

All is good now again. As soon as the error occurs, the error handler is used and shows a message box as shown below.

Now, I expand the code so that I have more code in or after the error handler.

Sub Errorhandler()

On Error GoTo ErrMsg

X = 12
Y = 20 / 0
Z = 30
Exit Sub

ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
A = 10 / 2
B = 35 / 0
End Sub

Since the first error has been handled but the second has not been, I again see an error as shown below.

Second Division by error code

Still all good. The code is behaving in the way we expected it to.

So to handle the second error, I use another error handler (ErrMsg2).

Sub Errorhandler()

On Error GoTo ErrMsg

X = 12
Y = 20 / 0
Z = 30
Exit Sub

ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
On Error GoTo ErrMsg2
A = 10 / 2
B = 35 / 0
Exit Sub

ErrMsg2:
MsgBox "There seems to be an error again" & vbCrLf & Err.Description

End Sub

And this is where it doesn’t work as expected.

If you run the above code, it will still give you a run-time error, even after having the second error handler in place.

This happens as we didn’t clear the first error from VBA’s memory.

Yes, we handled it! But it still remains in the memory.

And when VBA encounters another error, it’s still stuck with the first error, and hence the second error handler is not used. The code stops at the line that caused the error and shows the error prompt.

To clear VBA’s memory and clear the previous error, you need to use the ‘On Error Goto -1’.

So if you add this line in the below code and run it, it will work as expected.

Sub Errorhandler()

On Error GoTo ErrMsg
X = 12
Y = 20 / 0
Z = 30
Exit Sub

ErrMsg:
MsgBox "There seems to be an error" & vbCrLf & Err.Description
On Error GoTo -1
On Error GoTo ErrMsg2
A = 10 / 2
B = 35 / 0
Exit Sub

ErrMsg2:
MsgBox "There seems to be an error again" & vbCrLf & Err.Description

End Sub

Note: The error automatically gets cleared when a subroutine ends. So, ‘On Error Goto -1’ can be useful when you’re getting two or more than two errors in the same subroutine.

The Err Object

Whenever an error occurs with a code, it’s the Err object that is used to get the details about the error (such as the error number or the description).

Err Object Properties

The Err Object has the following properties:

Property Description
Number A number that represents the type of error. When there is no error, this value is 0
Description A short description of the error
Source Project name in which the error has occurred
HelpContext The help context id for the error in the help file
HelpFile A string that represents the folder location and the file name of the help file

While in most cases you don’t need to use Err object, it can sometimes be useful while handling errors in Excel.

For example, suppose you have a dataset as shown below and for each number, in the selection, you want to calculate the square root in the adjacent cell.

Data for Square root in Excel

The below code can do it, but since there is a text string in cell A5, it shows an error as soon as this occurs.

Sub FindSqrRoot()
Dim rng As Range
Set rng = Selection

For Each cell In rng
    cell.Offset(0, 1).Value = Sqr(cell.Value)
Next cell

End Sub

Type Mismatch Error when finding square root

The problem with this type of error message is that it gives you nothing about what has gone wrong and where the issue occurred.

You can use the Err object to make these error messages more meaningful.

For example, if I now use the below VBA code, it will stop the code as soon as the error occurs and show a message box with the cell address of the cell where there is an issue.

Sub FindSqrRoot()
Dim rng As Range
Set rng = Selection

For Each cell In rng
    On Error GoTo ErrHandler
    cell.Offset(0, 1).Value = Sqr(cell.Value)
Next cell

ErrHandler:
MsgBox "Error Number:" & Err.Number & vbCrLf & _
    "Error Description: " & Err.Description & vbCrLf & _
    "Error at: " & cell.Address

End Sub

The above code would give you a lot more information than the simple ‘Type Mismatch’, especially the cell address so that you know where the error occurred.

More description Error when finding the square root

You can further refine this code to make sure your code runs until the end (instead of breaking at each error) and then gives you a list of cell address where the error occurs.

The below code would do this:

Sub FindSqrRoot2()
Dim ErrorCells As String
Dim rng As Range

On Error Resume Next

Set rng = Selection
For Each cell In rng
cell.Offset(0, 1).Value = Sqr(cell.Value)

If Err.Number <> 0 Then
ErrorCells = ErrorCells & vbCrLf & cell.Address
On Error GoTo -1
End If
Next cell
MsgBox "Error in the following cells" & ErrorCells
Exit Sub

End Sub

The above code runs until the end and gives the square root of all the cells that have numbers in it (in the adjacent column). It then shows a message that lists all the cells where there was an error (as shown below):

Shows a message with cell address that have errors

Err Object Methods

While the Err properties are useful to show useful information about the errors, there are two Err methods as well that can help you with error handling.

Method Description
Clear Clears all the property settings of the Err object
Raise Generates a run-time error

Let’s quickly learn what these are and how/why to use these with VBA in Excel.

Err Clear Method

Suppose you have a dataset as shown below and you want to get the square root of all these numbers in the adjacent column.

Data for Square root in Excel - 10 cells

The following code will get the square roots of all the numbers in the adjacent column and show a message that an error occurred for cell A5 and A9 (as these have text in it).

Sub FindSqrRoot2()
Dim ErrorCells As String
Dim rng As Range

On Error Resume Next

Set rng = Selection
For Each cell In rng
cell.Offset(0, 1).Value = Sqr(cell.Value)

If Err.Number <> 0 Then
ErrorCells = ErrorCells & vbCrLf & cell.Address
Err.Clear
End If
Next cell
MsgBox "Error in the following cells" & ErrorCells

End Sub

Note that I have used the Err.Clear method within the If Then statement.

Once an error has occurred and trapped by the If condition, Err.Clear method resets the error number back to 0. This ensures that IF condition only trap the errors for cells where it is raised.

Had I not used the Err.Clear method, once the error occurs, it would always be true in the IF condition, and the error number has not been reset.

Another way of making this work is by using the On Error Goto -1, which resets the error completely.

Note: Err.Clear is different from On Error Goto -1. Err.Clear only clears the error description and the error number. it doesn’t completely reset it. This means that if there is another instance of error in the same code, you won’t be able to handle it before resetting it (which can be done with ‘On Error Goto -1’ and not by ‘Err.Clear’).

Err Raise Method

The Err.Raise method allows you to raise a run-time error.

Below is the syntax of using the Err.Raise method:

Err.Raise [number], [source], [description], [helpfile], [helpcontext]

All these arguments are optional and you can use these to make your error message more meaningful.

But why would you ever want to raise an error yourself?

Good question!

You can use this method when there is an instance of an error (which means that there is going to an error anyway) and then you use this method to tell the user more about the error (instead of the less helpful error message that VBA shows by default).

For example, suppose you have a dataset as shown below and you want all the cells to have numeric values only.

Sub RaiseError()
Dim rng As Range
Set rng = Selection

On Error GoTo ErrHandler

For Each Cell In rng
If Not (IsNumeric(Cell.Value)) Then
Err.Raise vbObjectError + 513, Cell.Address, "Not a number", "Test.html"
End If
Next Cell

ErrHandler:
MsgBox Err.Description & vbCrLf & Err.HelpFile
End Sub

The above code would show an error message that has the specified description and the context file.

Personally, I have never used Err.Raise as I mostly work with Excel only. But for someone who uses VBA to work with Excel along with other applications such as Outlook, Word or PowerPoint, this can be useful.

Here is a detailed article on Err.Raise method in case you want to learn more.

VBA Error Handling Best Practices

No matter how skilled you get a writing VBA code, errors are always going to be a part of it. The best coders are those who have the skills to handle these errors properly.

Here are some best practices you can use when it comes to error handling in Excel VBA.

  1. Use ‘On Error Go [Label]’ at the beginning of the code. This will make sure any error that can happen from there is handled.
  2. Use ‘On Error Resume Next’ ONLY when you’re sure about the errors that can occur. Use it with expected error only. In case you use it with unexpected errors, it will simply ignore it and move forward. You can use ‘On Error Resume Next’ with ‘Err.Raise’ if you want to ignore a certain type of error and catch the rest.
  3. When using error handlers, make sure you’re using Exit Sub before the handlers. This will ensure that the error handler code is executed only when there is an error (else it will always be executed).
  4. Use multiple error handlers to trap different kinds of errors. Having multiple error handler ensures that an error is properly addressed. For example, you would want to handle a ‘type mismatch’ error differently than a ‘Division by 0’ run-time error.

Hope you found this Excel article useful!

Here are some more Excel VBA Tutorials that you may like:

  • Excel VBA Data Types – A Complete Guide
  • Excel VBA Loops – For Next, Do While, Do Until, For Each
  • Excel VBA Events – An Easy (and Complete) Guide
  • Excel Visual Basic Editor – How to Open and Use it in Excel

Excel VBA Errors & Error Handling, On Error & Resume Satements, Exit Statement, Err Object

————————————————————————————————-

Contents:

VBA Erros & Error Handling

Error Handling Setting, in VBE

Error Handler

On Error Statements

Using an Exit Statement

Error Handling in Nested Procedures & The Resume Statement

Get Information from the Error Object

Raise Method of the Err Object: Generate a Run-time error

————————————————————————————————-

Error Handling determines what is to be done next on the occurrence of an error. On encountering a run-time error, an On Error statement enables or disables an error-handling routine within a procedure. A Resume statement can only be used in an error-handling routine — it resumes execution at a sepcified point after the error-handline routine finishes. You can get information on the error from the properties of the Error object — this object is the Err Object. In this section, we cover:

VBA Erros & Error Handling

In vba programming you can have Syntax Errors or Run-time Errors. An error handler determines what action is to be taken within a procedure, on the occurrence of a run-time error.

A syntax error occurs when you enter a line of code which is not allowed or recognized by Visual Basic. You will encounter a syntax error on misspelling a keyword or a named argument, for incorrect punctuation (ex. not specifying a comma as a placeholder for the omitted argument), use an undefined procedure, and so on. These errors are easier to locate as the Code Editor points them out at the time you are writing your code.

A run-time error occurs at the time during which your code is running, that is after you have created your macro. There could be an error in your programming due to incorrect logic used in your code that prevents it from doing what you intended and may stop code execution, for example, if your code attempts to divide a value by zero. Another reason for an error which may cause even a valid code to crash could be a condition not being met, say, a reference to a worksheet in your code which has been deleted by the user. Other examples when a run-time error can occur are: on using incorrect variable names or variable types; if your code goes into an infinite loop; using a value or reference outside the allowable range; and so on. If you dont implement error handling in your macro, on encountering a run-time error your code will stop execution and go into Break Mode and display an error message, thereby confusing and frustrating the user.

Using Error Handling in VBA is an important tool for developers to trap & handle run-time errors in a vba procedure for seamless execution. It is important to set error handling in every procedure else your macro might just crash or stop executing on encountering a run-time error and vba will display a default error message and go into Break Mode allowing the user to debug your code.

Basic Error Handling hides the fault in the macro when it crashes and exits gracefully, and informs the users accordingly. Advanced Error Handling techniques involve saving information on the error cause and the point of the error, and attempt to resolve the error and provide information to the user on the next step.

Error Handling determines what is to be done next on the occurrence of an error. At a basic level, Error Handling involves two parts — (i) enables an error-handling routine with an On Error Statement on encountering an error, and (ii) an error-handling routine which is the section where the procedure flow is directed to on encountering an error. It is to be noted that an error-handling routine is not a procedure (Sub or Function) but a section of code marked by a line label or a line number. An On Error statement enables or disables an error-handling routine within a procedure.

Error Handling Setting, in VBE

You can determine how errors are handled in VBE, by selecting the appropriate option. In VBE, click Options on the Tools Menu, select the General tab in the dialog box. In the Error Trapping Section, you can select from 3 options.

Break on All Errors: Selecting this will stop your code execution and enter Break Mode on every error, even if you are using an error handler (including the On Error Resume Next statement), hence it is not advised to select this option.

Break on Unhandled Errors: This works well in that an error is trapped when an error handler is active else the error causes the macro to go into Break Mode, except for debugging class modules wherein on encountering an error the debugger stops on the line which calls the class and not on the actual error line in the class, thus making it difficult to spot the error and fixing it.

Break in Class Module: Selecting this option will cause an unhandled error in a class module to stop code execution and go into Break Mode at the actual error causing line of code in that class module, but does not work if you use raise errors in your classes via the Err.Raise command which will actually cause an “error”.

Error Handler

An error handler determines what action is to be taken within a procedure, on the occurrence of an error. An ‘enabled’ error handler is the one which is enabled by the On Error Statement; an ‘active’ error handler is the ‘enabled’ error handler which is in the process of handling an error. On encountering an error you may decide to exit the procedure, or else you may want to rectify the error and resume execution. For this you will use On Error statements or Resume statements. A Resume statement can only be used in an error-handling routine — it resumes execution after the error-handline routine finishes.

On Error Statements

On encountering a run-time error, an On Error statement enables or disables an error-handling routine within a procedure. If an error-handling routine is enabled, procedure flow is directed to the error-handling routine which handles the error.

On Error GoTo line

The On Error GoTo line Statement enables the error-handling routine, which is the section starting at the line specified in the line argument. The line argument is required to be specified, and it can be any line label or line number in the same procedure as the On Error statement. A compile-time error will occur if the specified line argument is not in the same procedure as as the On Error statement. The On Error GoTo statement traps all errors, without exceptions.

On Error Resume Next

This Statement specifies that on the occurrence of a run-time error, the procedure flow is directed to the next statement which follows the statement where the error occurred. This effectively skips the error and continues with the macro execution.

An On Error Resume Next statement becomes inactive on calling another procedure is called, hence it needs to be specified in every procedure that is called to use this error handling therein. Note that the properties of the Error object (Err Object) get cleared automatically when Resume Next is used in an error-handling routine, but not on using the Resume Next statement otherwise. Using the On Error Resume Next statement only defers error trapping & handling, whereas an error-handling routine handles the error and using the Resume Next statement therein resumes execution at same line that caused the error.

On Error GoTo 0

On Error GoTo 0 statement Disables any enabled error handler in the current procedure — it means as having no error handler in the code. This statement does not specify 0 as the start of the error-handling routine even though a line numbered 0 may be present in the procedure. An error handler is automatically disabled when a procedure is exited or if it has has run completely, if the procedure does not have an On Error GoTo 0 statement.

Using an Exit Statement

Placing an Exit Sub, Exit Function or Exit Property statement

For a procedure containing an error-handling routine, you will also need to include an exit routine within the procedure to ensure that the error-handling routine runs only if an error is encountered. This can be done by placing an Exit Sub, Exit Function or Exit Property statement immediately above the error-handling routine, if you don’t want it to execute when there is no error.

Single Exit Point

It may be preferable, not necessary, to have a single exit point in your procedure, so that after the Error Handler has handled the error, the procedure flow is directed to a point within the procedure so that the procedure exit is the same under all circumstances. This can be done by placing a Resume statement — Resume <Label> — the Label determines the point at which procedure flow is resumed after the Error Handler has handled the error. This Label has no effect on code execution if no error has occurred. It is preferable to have a single exit point because usually some type of clean up is required before the procedure exits, ex. you often enter Application.EnableEvents = False at the beginning of the code  for a worksheet_change event and because EnableEvents is not automatically changed back to True you add Application.EnableEvents = True at the end of the code before exit. A single exit point will obviate the need to duplicate this clean up code in the error-handling routine.

Error Handling in Nested Procedures & The Resume Statement

Using a Resume Statement

A Resume statement can only be used in an error-handling routine — it resumes execution at a sepcified point after the error-handline routine finishes. You can aslo exit or end the procedure after the error-handling routine finishes and not necessarily use the Resume statement. We discuss below three types of syntax used for the Resume statement, and how the control transfers (ie. code execution resumes) by these Resume statements. You should always use a Resume statement instead of a GoTo statement within an error-handling routine, because using a «GoTo line» statement apparantly deactivates subsequent Error Handling — even though both Resume & GoTo statements direct procedure flow out of the error-handling routine.

Resume or Resume 0: Where the error occurrs in the same procedure as the error handler, control is returned to the statement that caused the error and execution resumes at this line. Where the error occurrs in a called procedure, control is returned to the last calling statement in the procedure containing the error handler.

Resume Next: Where the error occurrs in the same procedure as the error handler, control is returned to  the next statement which follows the statement that caused the error and execution resumes at this line. Where the error occurrs in a called procedure, control is returned to the next statement which follows the last calling statement in the procedure containing the error handler.

Resume line: When an error occurrs in a procedure, control transfers to the line specified in the line argument. The line argument is a line label or line number and should be in the same procedure as the error handler.

Which Resume Statement to use:

The Resume or Resume 0 statement is used when it is necessary for the macro to correct the error and re-execute the corrected code — say when the user enters an invalid value and you want to prompt the user to enter a valid value and resume at the same line and re-excecute. The Resume Next statement is used when the error handler corrects the error and it is not required to re-execute the error code but to continue execution at the next line. The Resume line statement is used when you want to continue execution at another point in the procedure, which could also be an exit routine.

Given below are 2 Examples which illustrate using On Error Statements & Error Handler in a Procedure

Example 1: 

Sub OnErrorResNxtSt()
‘using an On Error Resume Next Statement in a procedure for handling errors
‘a run-time error 1004 occurs while naming another sheet with the same name

‘execution flows to the next statement which follows the statement that caused the error

On Error Resume Next

Dim strNewName As String, ws As Worksheet, response As Integer

‘add a new worksheet at the end

ActiveWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)

WsName:

‘enter name for worksheet

strNewName = InputBox(«Enter Worksheet Name»)

‘StrPtr — String Pointer — function used to determine if Cancel button has been pressed.

If StrPtr(strNewName) = 0 Then

MsgBox «You have pressed Cancel, Exiting Procedure without changing Worksheet Name»

Exit Sub

End If

‘rename the new worksheet — if name already exists, a run-time error 1004 will occur

ActiveSheet.Name = strNewName

‘Check Err object Number property if it corresponds to the error no. 1004.

‘Error No. 1004 occurs if worksheet with the same name already exists

If Err = 1004 Then

response = MsgBox(«Name already Exists, do you want to retry?», vbYesNo + vbQuestion)

If response = vbYes Then

‘clear error

Err.Clear

‘if worksheet name already exists, enter a new name

GoTo WsName

Else

MsgBox «Exiting Procedure without changing Worksheet Name»

Exit Sub

End If

End If

‘returns 0 — either no error occurred or error was cleared

MsgBox Err.Number

End Sub

Example 2: 

Sub OnErrorGoToSt()
‘using an On Error GoTo Statement in a procedure for handling errors, and a Resume statement within an error-handling routine
‘a run-time error 1004 occurs while naming another sheet with the same name

On Error GoTo ErrHandler

Dim strNewName As String, ws As Worksheet

‘add a new worksheet at the end

ActiveWorkbook.Worksheets.Add After:=Worksheets(Sheets.Count)

WsName:

‘enter name for worksheet

strNewName = InputBox(«Enter Worksheet Name«)

‘StrPtr — String Pointer — function used to determine if Cancel button has been pressed.

If StrPtr(strNewName) = 0 Then

MsgBox «You have pressed Cancel, Exiting Procedure»

GoTo exit_proc

End If

‘rename the new worksheet — if name already exists, a run-time error 1004 will occur

ActiveSheet.Name = strNewName

‘returns 0 — either no error occurred or error was cleared (using Resume statement in an error-handling routine, automatically clears the error)

MsgBox Err.Number

‘exit routine to skip error handler

exit_proc:

Exit Sub

ErrHandler:

‘Error No. 1004 occurs in this case if worksheet with the same name already exists

If Err = 1004 Then

MsgBox «Name already exists»

‘resumes execution at this point (WsName)

Resume WsName

Else

‘resumes execution at exit routine and exits sub

Resume exit_proc

End If

End Sub

Error Handling in a Called Procedure

If the called procedure in which an error has occurred does not have an error handler, VBA searches backward in the calling procedures for an enabled error handler, and if found the control is transferred to that error handler in the calling procedure. If an enabled error handler is not found in the backward search, then execution will stop in the current procedure displaying an error message.

Example 3:  Error in Nested Procedures — in the absence of an error handler in the called procedure in which an error has occurred, VBA searches backward in the calling procedures and control is transferred to an enabled error handler, if present, in the calling procedure.

Sub CallMarksGrades()
‘this is the calling procedure, with an error handler and Resume statements — the error handler is capable of correcting Type Mismatch, Overflow & Division by Zero errors.

On Error GoTo ErrHandler

‘Declare constants to indicate likely errors

Dim iMarks As Integer, iTotalMarks As Integer, dPcnt As Double, response As Integer

Const conErrorTypeMismatch As Long = 13

Const conErrorDivZero As Long = 11

Const conErrorOverflow As Long = 6

M:

iMarks = InputBox(«Enter Marks«)

TM:

iTotalMarks = InputBox(«Enter Total Marks«)

‘call the MarksPercent function and the result is assgined to the local variable dPcnt

dPcnt = MarksPercent(iMarks, iTotalMarks)

MsgBox «Percentage is » & dPcnt & «%»

‘exit routine to skip error handler

exit_proc:

Exit Sub

ErrHandler:

MsgBox Err

‘Check Err object Number property if it corresponds to the Type Mismatch error

‘a Type Mismatch error will occur if you have entered a non-numerical or pressed Cancel in the Input Box

If Err = conErrorTypeMismatch Then

response = MsgBox(«You may have entered a non-numerical value or pressed Cancel, do you want to Exit procedure?», vbYesNo + vbQuestion)

If response = vbYes Then

Resume exit_proc

Else

‘after correcting the error, resume execution at the same line which caused the error ie. Input Box is re-generated for making a valid entry

Resume

End If

‘Check Err object Number property if it corresponds to the Overflow error (where values exceed limitations or allowable range)

ElseIf Err = conErrorOverflow Then

MsgBox «Overflow error — also possible if both Marks & Total Marks are zero»

‘after correcting an Overflow error, resume execution at the specified line ie. M, which generates the Input Boxes afresh

Resume M

‘Check Err object Number property if it corresponds to the Division by Zero error

ElseIf Err = conErrorDivZero Then

MsgBox «Division by Zero error — Total Marks cannot be zero»

‘after correcting a division by zero error, resume execution at the specified line ie. TM, which generates the Input Box for iTotalMarks

Resume TM

Else

‘control is returned to  the next statement which follows the statement that caused the error

Resume Next

End If

End Sub

Function MarksPercent(Marks As Integer, TotalMarks As Integer) As Double
‘this is the called procedure — in case of an error in this procedure, say a division by zero run-time error no. 11,  VBA searches backward in the calling procedures for an enabled error handler, and if found the control is transferred  to that error handler in the calling procedure.

MarksPercent = Marks / TotalMarks * 100

MarksPercent = Round(MarksPercent, 2)

End Function

If an error occurs in a called procedure within an active error handler which does not correct for that error, using the Raise method to regenerate the original error will force Visual Basic to search backward through the calling procedures hierarchy for an enabled error handler. The Err object’s Raise method is useful to regenerate an original error in a vba procedure — refer the section on Error Object for details on the Raise Method. This is useful in cases where the called procedure’s error handler is not equipped to correct the error either because this type of error was not expected to occur in the called procedure or for any other reason. In this scenario the sequence will be that an error occurrs in a called procedure — the called procedure has an enabled error handler which does not correct the error, and the original error is regenerated using the Raise Method — Visual Basic is forced to do a backward search and execution flows to the error handler (if present) of the immediately preceding calling procedure, which may or may not correct the error — if the immediately preceding calling procedure does not have an error handler or its error handler is not capable of correcting the error and the error is regenerated then the backward search continues. If you do not regenerate the error in the called procedure whose enabled error handler is incapable of handling the error, the error may cause the macro to stop or continue with the error causing other errors.

Example 4:  Error in Nested Procedures — for an error in a called procedure with an active error handler which does not correct for that error, on Regenerating the error with the Raise Method, VBA searches backward in the calling procedures and control is transferred to an enabled error handler, if present, in the calling procedure.

Sub Calling_Proc()
‘calling procedure — this handles any error, and corrects in case it is a File Not Found error no. 53

Const conFileNotFound As Integer = 53

On Error GoTo ErrHandler

‘call another procedure

Call Called_Proc

‘exit routine to skip error handler

exit_routine:

Exit Sub

‘error handling routine of the calling procedure

ErrHandler:

‘error is corrected if it is a File Not Found error no. 53

If Err = conFileNotFound Then

MsgBox «Error No 53 — File Not Found, will create a New File to Copy»

Dim strFileToCopy As String, strFileCopied As String

strFileToCopy = «Book11.xlsx«

strFileCopied = «Book22.xlsx«

‘create a new workbook:

Workbooks.Add

‘save as .xlsx file, the default Excel 2007 file format, using the FileFormat Enumeration xlOpenXMLWorkbook (value 51):

ActiveWorkbook.SaveAs fileName:=strFileToCopy, FileFormat:=xlOpenXMLWorkbook

‘close workbook after saving changes:

ActiveWorkbook.Close SaveChanges:=True

‘this will copy the file

FileCopy strFileToCopy, strFileCopied

MsgBox «File Created & Copied»

‘if error is other than a File Not Found error no. 53

Else

MsgBox «Unresolved Error, Exiting Procedure»

End If

‘resumes execution at exit routine and exits sub

Resume exit_routine

End Sub

Sub Called_Proc()
‘this is a called procedure with an error handler that will correct only a Path not found error no. 76
‘for any other error in this procedure, say if the file is not found in the path/directory, the Raise method is used to regenerate the original error and execution flows to an enabled error handler (if present) in the calling procedure

‘Declare constant to indicate anticipated error

Const conPathNotFound As Integer = 76

Dim strFilePath As String, intOrigErrNum As Integer

Dim strFileToCopy As String, strFileCopied As String

strFileToCopy = «Book11.xlsx«

strFileCopied = «Book22.xlsx«

On Error GoTo ErrHandler76

‘specify file path: PATH NOT FOUND ERROR OCCURS HERE

‘not finding the specified path will give a run-time error ’76’ — Path not found

strFilePath = «C:ExcelFiles«

ChDir strFilePath

‘OTHER ERROR OCCURS HERE

‘attempting to Copy a file which does not exist will give a run-time error ’53’ — File not found

FileCopy strFileToCopy, strFileCopied

MsgBox «File Copied»

‘exit routine to skip error handler

exit_proc:

Exit Sub

ErrHandler76:

‘Check Err object Number property if it corresponds to the Path not found error.

If Err = conPathNotFound Then

‘correcting the Path in the Error Handler

strFilePath = ThisWorkbook.Path

MsgBox «Correcting Error No 76 — Path changed to ThisWorkbook path»

‘after correcting the Path, resume execution at the same line which caused the error

Resume

Else

‘for an error other than error no. 76, determine error number.

intOrigErrNum = Err.Number

‘clear error

Err.Clear

MsgBox «Error is other than error no. 76 — will Search Backward in Calling Procedures for an Error Handler to correct this error»

‘Regenerate original error — this will search backward in the calling procedures for an Error Handler if its exists

Err.Raise intOrigErrNum

‘resumes execution at exit routine and exits sub — execution flows to an enabled error handler in the calling procedure if it exists

Resume exit_proc

End If

End Sub

Get Information from the Error Object

Err Object & Properties: On the occurrence of a run-time error, you can get information on the error from the properties of the Error object (this object is the Err Object), which will help you in managing the error and to determine what is to be done next. The Number Property (Err.Number) returns a numeric value specifying the error with a value of zero meaning no error — this is the error’s number. The Number Property is the default property of the Err object. The Description Property (Err.Description) returns a short description of the error but this may not exist at times — if no Visual Basic error corresponds to the Number property, the «Application-defined or object-defined error» message is used. The Description property returns a zero-length string («») if no run-time error has occurred or ErrorNumber is 0. The property settings of the Err object relate to the most recent run-time error, so it is important that your error-handling routine saves these values before the occurrence of another error.

Use the Clear Method (Err.Clear) to to explicitly clear all property settings of the Err object. Using an Exit Sub, Exit Function or Exit Property statement, or using Resume Next statement in an error-handling routine, automatically calls the Clear Method and resets the numeric properties (viz. Number property) of the Err object to zero and the string properties (viz. Description property) to zero-length strings («»). However, the properties of the Err object are not reset when you use any Resume statement outside of an error-handling routine. Err.Clear is used to clear the properties of the Err object properties after the error is handled — using the On Error Resume Next statement defers error handling, whereas an error-handling routine handles the error and using the Resume Next statement therein resumes execution at same line that caused the error. Note that setting the error number to zero (Err.Number = 0) is not the same as using the Clear method because this does not reset the description property.

Using the Source property (Err.Source) lets you know what generated the error — Source returns the name of the object or application that generated the error. Source contains the project name for an error in a standard module. Source is the programmatic ID of your application if an error is generated by your application from code. Source contains a name with the project.class form, for an error in a class module. Source can be specifically defined by the user while using the Raise Method to generate an error. This property may not be very useful in providing information on vba run-time erros as it basically returns the name of the project in which the error occurred.

For Error Handling within a procedure it is usual to programmatically use only the Number property of the Err object, while other properties of the Err object are useful to provide additional information to the user on the cause of the error. Many times in your code it may be preferable to use the On Error Resume Next statement over On Error GoTo statement, because by checking the Err object’s properties after each interaction with an object (line of code) you can determine which object or statement originally generated what error — refer Example 1.

Example 5: Illustrating some common run-time errors in vba, with their respective Error.Number & Error.Description

Sub RunTimeErrorsInVba()
‘Illustrating some common run-time errors in vba, with their respective Error.Number & Error.Description
‘Err.Source in all cases below is «VBAProject», except in 2 instances of Run-time error ‘1004’ wherein the Source is «Microsoft Office Excel»

‘Run-time error ’11’: Division by zero (dividing a number by zero will give this error)

MsgBox 2 / 0

‘Run-time error ‘9’: Subscript out of range (This error occurs if you attempt to access array elements & members of collections beyond their defined ranges. In this case Sheet does not exist — active Workbook contains only 3 sheets)

MsgBox Sheets(7).Name

‘Run-time error ‘1004’: Application-defined or object-defined error (invalid reference). Err.Source returns ‘VBAProject’

Cells(1, 1).Offset(-1, 0) = 5

‘Run-time error ‘1004’: Select method of Range class failed (Sheet1 is not the active sheet whereas Select Method is valid for active sheet only). Err.Source returns ‘Microsoft Office Excel’

Sheets(«Sheet1»).Cells(1, 1).Select

‘Run-time error ‘1004’: Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic (renaming the active sheet wherein sheet with the same name already exists).

Err.Source returns ‘Microsoft Office Excel’

ActiveSheet.Name = «Sheet1«

‘Run-time error ’76’: Path not found (the specified path is not found)

ChDir «C:ExcelClients»

‘Run-time error ’68’: Device unavailable (drive does not exist)

ChDrive «H»

‘run-time error ’53’ — File not found (copy or delete a file which does not exist viz. Book1.xlsx)

FileCopy ActiveWorkbook.Path & «» & «Book1.xlsx», ActiveWorkbook.Path & «» & «Book2.xlsx»

Kill ActiveWorkbook.Path & «» & «Book1.xlsx»

‘Run-time error ’91’: Object variable or With block variable not set (using an object variable that does not yet reference a valid object: an error is generated on the reference to ws because the Set statement is omitted viz. Set ws =

ActiveSheet)

Dim ws As Worksheet

ws = ActiveSheet

MsgBox ws.Name

‘Run-time error ‘424’: Object required (sheet name is not a valid object)

Dim ws As Worksheet

Set ws = ActiveSheet.Name

‘Run-time error ’13’: Type mismatch (variable is of incorrect type — reference is to a range object & not worksheet — variable should be declared as — Dim ws As Range)

Dim ws As Worksheet

Set ws = ActiveSheet.Cells(1, 1)

‘entering a string value in the input box below will give a Run-time error ’13’: Type mismatch

Dim result As Single

result = InputBox(«Enter a number»)

‘Run-time error ‘6’: Overflow (this error occurs if you attempt to assign values exceeding the assigned target’s limit — in the present case the value of i is larger than an integer because an integer can hold whole numbers in the range -32,768

to 32,767)

Dim i As Integer

i = 100 * 20000

MsgBox i

End Sub

Raise Method of the Err Object: Generate a Run-time error

Raise Method is used to generate a run-time error. You can raise either a pre-defined error using its corresponding error number, or generate a custom (user-defined) error. Though Raise can be used in place of the Error statement, but because errors generated by using the Error statement give richer information in the Err object, Raise is useful to generate run-time errors for system errors and class modules. Syntax: Err.Raise(Number, Source, Description, HelpFile, HelpContext). The Raise method generates a specific error and the Err object properties are populated with information on that error. Only the Number argument is necessary to specify in the Raise Method, and all other arguments are optional. If optional arguments are omitted and the Err object properties contain uncleared values, those values are assumed for your error values. The Err object’s Raise method is useful to regenerate an original error in a vba procedure — if an error occurs within an active error handler which does not correct for that error, using the Raise method to regenerate the original error will force Visual Basic to search backward through the calling procedures for an enabled error handler. This has been explained & illustrated in Example 4 above.

Arguments of Raise Method: The Number argument is the error’s number. The Source argument represents the source of the error. The Description argument describes the error providing additional information about it. The HelpFile and HelpContext arguments represent the help file and help context ID used to link help to the error message box.

Raise Custom Errors (user-defined errors) using the Err Object’s Raise Method:

You can deliberately generate custom (run-time) errors in vba code, using the Raise Method of the Err Object. You may want to generate a custom error when your code does something you don’t want, for example, to prevent a user from inputting data which may be outside an acceptable range, you can stop the macro by raising an error, complete with an error code and the source of occurrence. To enable this, use the Err object’s Raise method.

The arguments of the Raise Method correspond to the properties of the Err object, and all arguments except the Number argument are optional. While raising a custom error you can set your own custom arguments in the Raise Method. You can raise pre-defined errors using their respective error numbers, but for a custom error you cannot use an error number which is in conflict with any Office built-in error number. To set Err.Number for your custom error, add the number you select as an error code to the vbObjectError constant (-2147221504) to ensure your custom error number is not in conflict with any built-in error numbers, for ex. to return the number -2147220504 as an error code, assign vbObjectError + 1000 to the Err.Number property — Err.Raise vbObjectError + 1000. One option is to set the Source argument as the name of the procedure in which the error occurs.

Example 6: Raise a custom error using Raise Method of the Err object, if length of name is less than 4 characters

Sub RaiseCustomError_1()
‘raise a custom error using Raise Method of the Err object, if length of name is less than 4 characters

   
On Error GoTo CustomError_Err

Dim strName As String

strName = «Jo«

If Len(strName) < 4 Then

‘an error occurs if name length < 4 characters — raise a custom error

Err.Raise Number:=vbObjectError + 1000, Description:=»Minimum Length of Name is 4 characters«

Else

‘macro executes if no error

MsgBox «Name: » & strName

End If

    
‘if no error, display message is «Program Executed»

MsgBox «Program Executed»

CustomError_End:

Exit Sub

    
CustomError_Err:

‘Display error number and description in message box: «Error Number: -2147220504, Description: Minimum Length of Name is 4 characters»

MsgBox «Error Number: » & Err.Number & «, Description: » & Err.Description

‘display message is «Exit Program without Executing»

MsgBox «Exit Program without Executing»

‘resume execution at this point after error handler has handled an error

Resume CustomError_End

End Sub

Понравилась статья? Поделить с друзьями:
  • Vba error clear
  • Vba error 6124
  • Vba error 5854
  • Vba error 430
  • Vba custom error