Vba custom error - Исправление ошибок и поиск оптимальных решений проблем

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

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

— Brian W. Kernighan

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

The Mouse Trap Analogy

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

So what is our mouse trap when speaking about VBA error handling? The On Error do this statement!

Using VBA On Error

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

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

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

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

On Error

Goto – or
```
On Error GoTo [ lineLabel | 0 | -1 ]
```
if preffered. The Goto instruction in VBA let’s you do a jump to a specific VBA code line number to follow through with error handling or simply to let code execution move on. It comes in three flavors:
- lineLabel – will jump to a specific line number label
- – will disable any previously set error handling within the current procedure
Resume Next – Simply ignore any error raised and move code execution to the next line. VBA error handling for the lazy, although beware in case of recurring errors (error overflow) – an error will still be raised

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

On Error Resume Next
Skip any raised errors

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

On Error Goto 0
Disable any previous VBA error handling

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

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

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

The VBA Err Object

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

Err Object functions and properties

The Err object facilitates the following functions and properties:

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

Dim errMsg As String
On Error Resume Next
'7 = Out of Memory
Err.Raise (7)

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

This is the expected output:

VBA Error Handling Best Practices

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

Single VBA error handler

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

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

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

Multiple VBA error handler

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

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

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

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

Custom VBA errors

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

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

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

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

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

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

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

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

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

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

The Resume Next statement

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

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

VBA Error Log

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

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

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

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

Источник

# Avoiding error conditions

When a runtime error occurs, good code should handle it. The best error handling strategy is to write code that checks for error conditions and simply avoids executing code that results in a runtime error.

One key element in reducing runtime errors, is writing small procedures that do one thing. The fewer reasons procedures have to fail, the easier the code as a whole is to debug.

Avoiding runtime error 91 — Object or With block variable not set:

This error will be raised when an object is used before its reference is assigned. One might have a procedure that receives an object parameter:

If target isn’t assigned a reference, the above code will raise an error that is easily avoided by checking if the object contains an actual object reference:

If target isn’t assigned a reference, then the unassigned reference is never used, and no error occurs.

This way of early-exiting a procedure when one or more parameter isn’t valid, is called a guard clause.

Avoiding runtime error 9 — Subscript out of range:

This error is raised when an array is accessed outside of its boundaries.

Given an index greater than the number of worksheets in the ActiveWorkbook, the above code will raise a runtime error. A simple guard clause can avoid that:

Most runtime errors can be avoided by carefully verifying the values we’re using before we use them, and branching on another execution path accordingly using a simple If statement — in guard clauses that makes no assumptions and validates a procedure’s parameters, or even in the body of larger procedures.

# Custom Errors

Often when writing a specialized class, you’ll want it to raise its own specific errors, and you’ll want a clean way for user/calling code to handle these custom errors. A neat way to achieve this is by defining a dedicated Enum type:

Using the vbObjectError built-in constant ensures the custom error codes don’t overlap with reserved/existing error codes. Only the first enum value needs to be explicitly specified, for the underlying value of each Enum member is 1 greater than the previous member, so the underlying value of Err_BarNotInitialized is implicitly vbObjectError + 1025.

# Raising your own runtime errors

A runtime error can be raised using the Err.Raise statement, so the custom Err_FooWasNotBarred error can be raised as follows:

The Err.Raise method can also take custom Description and Source parameters — for this reason it’s a good idea to also define constants to hold each custom error’s description:

And then create a dedicated private method to raise each error:

The class’ implementation can then simply call these specialized procedures to raise the error:

The client code can then handle Err_BarNotInitialized as it would any other error, inside its own error-handling subroutine.

Note: the legacy Error keyword can also be used in place of Err.Raise, but it’s obsolete/deprecated.

# Resume keyword

An error-handling subroutine will either:

run to the end of the procedure, in which case execution resumes in the calling procedure.
or, use the Resume keyword to resume execution inside the same procedure.

The Resume keyword should only ever be used inside an error handling subroutine, because if VBA encounters Resume without being in an error state, runtime error 20 «Resume without error» is raised.

There are several ways an error-handling subroutine may use the Resume keyword:

Resume used alone, execution continues on the statement that caused the error. If the error isn’t actually handled before doing that, then the same error will be raised again, and execution might enter an infinite loop.
Resume Next continues execution on the statement immediately following the statement that caused the error. If the error isn’t actually handled before doing that, then execution is permitted to continue with potentially invalid data, which may result in logical errors and unexpected behavior.
Resume [line label] continues execution at the specified line label (or line number, if you’re using legacy-style line numbers). This would typically allow executing some cleanup code before cleanly exiting the procedure, such as ensuring a database connection is closed before returning to the caller.

# On Error Resume Next

The On Error statement itself can use the Resume keyword to instruct the VBA runtime to effectively ignore all errors.

If the error isn’t actually handled before doing that, then execution is permitted to continue with potentially invalid data, which may result in logical errors and unexpected behavior.

The emphasis above cannot be emphasized enough. On Error Resume Next effectively ignores all errors and shoves them under the carpet. A program that blows up with a runtime error given invalid input is a better program than one that keeps running with unknown/unintended data — be it only because the bug is much more easily identifiable. On Error Resume Next can easily hide bugs.

The On Error statement is procedure-scoped — that’s why there should normally be only one, single such On Error statement in a given procedure.

However sometimes an error condition can’t quite be avoided, and jumping to an error-handling subroutine only to Resume Next just doesn’t feel right. In this specific case, the known-to-possibly-fail statement can be wrapped between two On Error statements:

The On Error GoTo 0 instruction resets error handling in the current procedure, such that any further instruction causing a runtime error would be unhandled within that procedure and instead passed up the call stack until it is caught by an active error handler. If there is no active error handler in the call stack, it will be treated as an unhandled exception.

# On Error statement

Even with guard clauses, one cannot realistically always account for all possible error conditions that could be raised in the body of a procedure. The On Error GoTo statement instructs VBA to jump to a line label and enter «error handling mode» whenever an unexpected error occurs at runtime. After handling an error, code can resume back into «normal» execution using the Resume keyword.

Line labels denote subroutines: because subroutines originate from legacy BASIC code and uses GoTo and GoSub jumps and Return statements to jump back to the «main» routine, it’s fairly easy to write hard-to-follow spaghetti code if things aren’t rigorously structured. For this reason, it’s best that:

a procedure has one and only one error-handling subroutine
the error-handling subroutine only ever runs in an error state

This means a procedure that handles its errors, should be structured like this:

# Error Handling Strategies

Sometimes you want to handle different errors with different actions. In that case you will inspect the global Err object, which will contain information about the error that was raised — and act accordingly:

As a general guideline, consider turning on the error handling for entire subroutine or function, and handle all the errors that may occur within its scope. If you need to only handle errors in the small section section of the code — turn error handling on and off a the same level:

# Line numbers

VBA supports legacy-style (e.g. QBASIC) line numbers. The Erl hidden property can be used to identify the line number that raised the last error. If you’re not using line numbers, Erl will only ever return 0.

If you are using line numbers, but not consistently, then Erl will return the last line number before the instruction that raised the error.

Keep in mind that Erl also only has Integer precision, and will silently overflow. This means that line numbers outside of the integer range (opens new window) will give incorrect results:

The line number isn’t quite as relevant as the statement that caused the error, and numbering lines quickly becomes tedious and not quite maintenance-friendly.

Источник

Example

Option Explicit
Public Enum FoobarError
    Err_FooWasNotBarred = vbObjectError + 1024
    Err_BarNotInitialized
    Err_SomethingElseHappened
End Enum

Raising your own runtime errors

A runtime error can be raised using the Err.Raise statement, so the custom Err_FooWasNotBarred error can be raised as follows:

Err.Raise Err_FooWasNotBarred

The Err.Raise method can also take custom Description and Source parameters — for this reason it’s a good idea to also define constants to hold each custom error’s description:

Private Const Msg_FooWasNotBarred As String = "The foo was not barred."
Private Const Msg_BarNotInitialized As String = "The bar was not initialized."

And then create a dedicated private method to raise each error:

Private Sub OnFooWasNotBarredError(ByVal source As String)
    Err.Raise Err_FooWasNotBarred, source, Msg_FooWasNotBarred
End Sub

Private Sub OnBarNotInitializedError(ByVal source As String)
    Err.Raise Err_BarNotInitialized, source, Msg_BarNotInitialized
End Sub

The class’ implementation can then simply call these specialized procedures to raise the error:

Public Sub DoSomething()
    'raises the custom 'BarNotInitialized' error with "DoSomething" as the source:
    If Me.Bar Is Nothing Then OnBarNotInitializedError "DoSomething"
    '...
End Sub

The client code can then handle Err_BarNotInitialized as it would any other error, inside its own error-handling subroutine.

Note: the legacy Error keyword can also be used in place of Err.Raise, but it’s obsolete/deprecated.

Источник

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

‘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

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

Источник