error resume next vba

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Resume statement

• 7 contributors

Resumes execution after an error-handling routine is finished.

Resume [ 0 ] Resume Next Resume line

The Resume statement syntax can have any of the following forms:

If you use a Resume statement anywhere except in an error-handling routine, an error occurs.

This example uses the Resume statement to end error handling in a procedure, and then resume execution with the statement that caused the error. Error number 55 is generated to illustrate using the Resume statement.

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

How To Use VBA On Error Resume Next Effectively

Handling errors in VBA? "On Error Resume Next" is a versatile tool in a programmer's toolkit, allowing for smoother error management in scripts. Dive into its syntax, benefits, and best practices to optimize your coding flow and tackle potential pitfalls head-on.

💡 KEY INSIGHTS

• Using `On Error Resume Next` in VBA allows the program to continue execution after an error, making it crucial for robust error handling in unpredictable environments.
• Error suppression with `On Error Resume Next` can lead to uncaught errors, necessitating careful use and subsequent error checking mechanisms.
• `On Error Resume Next` is particularly useful in iterative processes or external data interactions, where error predictability is low and program continuity is essential.
• While enabling smoother run-time experiences, overreliance on `On Error Resume Next` can obscure underlying issues , emphasizing the need for strategic, limited use.

Handling errors is a crucial part of programming in VBA. The "On Error Resume Next" statement allows you to manage errors more gracefully by bypassing the standard error-trapping process. This technique can make your code more robust and less prone to crashing when executed.

What Is VBA On Error Resume Next

Why use on error resume next, basic syntax and usage, common use cases, best practices, common pitfalls and how to avoid them, frequently asked questions.

VBA (Visual Basic for Applications) has a variety of error-handling tools. One of these is On Error Resume Next . This statement suppresses runtime errors, allowing the code to continue executing the next line after the error occurs.

On Error Resume Next is particularly useful in scenarios where you expect errors but don't want them to halt execution. For instance, you might be looping through an object collection where some objects could be null.

Basic Syntax

Practical example.

The syntax for implementing On Error Resume Next is straightforward:

Consider the case where you want to divide a range of numbers by another number. If the divisor is zero, a divide-by-zero error will occur. Using On Error Resume Next allows the code to continue running.

By understanding the practical application of On Error Resume Next , you can write more robust VBA code. It allows for error bypassing, which is especially useful when dealing with uncertain data or object statuses.

On Error Resume Next allows for a more fluid execution flow. It helps skip non-critical errors that would otherwise stop your program dead in its tracks. This statement can be beneficial for batch processing , where an error in one operation shouldn't terminate the entire sequence.

In applications where user interaction is limited or not available, letting your code continue despite minor errors can be advantageous. It enhances the end-user experience by avoiding unnecessary error messages or program halts.

Syntax In A Loop

Handling file operations.

Here's how you might use On Error Resume Next in a loop to read from an array and handle errors seamlessly.

On Error Resume Next can also be handy in file operations where a file might not exist or be inaccessible, but you still want to continue with other tasks.

Using On Error Resume Next wisely can make your code both flexible and user-friendly. However, indiscriminate usage can lead to unforeseen issues, so understanding when to use it is crucial.

The Basic Syntax of using On Error Resume Next is straightforward. It's a single line that you place before the segment of code where you anticipate errors may occur. By adding this line, you instruct VBA to move past errors and continue with the next line of code.

Basic Usage

Reverting error handling, checking errors manually.

The syntax looks like this:

It's crucial to revert back to normal error handling after using On Error Resume Next . Use On Error GoTo 0 for this.

You can use the Err object to manually check if an error has occurred after using On Error Resume Next .

Getting the syntax and usage correct for On Error Resume Next sets the stage for more robust error handling in your VBA projects. Always remember to reset the error handler and manually check for errors when necessary.

Iterating Through Collections

File operations, working with external data.

One common use case for On Error Resume Next is iterating through collections where some objects might not be initialized. For example, looping through worksheets in a workbook where some may be hidden.

File operations are prone to errors like 'File Not Found' or 'Access Denied'. On Error Resume Next can be a lifesaver here.

When working with external databases or APIs , it's not uncommon to encounter missing or malformed data. With On Error Resume Next , you can proceed without interruption.

Understanding these common use cases helps you decide when employing On Error Resume Next is most effective. Just remember, it's a tool that should be used with care to prevent unforeseen issues.

Limit Scope

Use error object, avoid silencing all errors, reset error handler.

First and foremost, limit the scope of where you use On Error Resume Next . Apply it only around the lines of code where you actually expect errors. Prolonged usage can make debugging a nightmare.

Always use the Err object to inspect the nature of an error when using On Error Resume Next . This helps in custom error handling based on the error number or description.

Never use On Error Resume Next as a way to silence all errors in your code. You might bypass not just expected errors but also genuine bugs, making them harder to trace.

Don't forget to reset the error handler using On Error GoTo 0 after the specific code segment. Failing to do so leaves the relaxed error handling in place, potentially causing issues down the line.

By following these best practices, you ensure that you're using On Error Resume Next effectively while minimizing potential risks. Always use this statement judiciously to maintain the integrity and debuggability of your VBA code.

Overuse Of On Error Resume Next

Not resetting the error handler, ignoring the err object, neglecting proper scope.

The most common pitfall is the overuse of On Error Resume Next . Using it liberally throughout your code makes debugging difficult, as it masks both expected and unexpected errors.

Another frequent mistake is not resetting the error handler after using On Error Resume Next . This could result in suppressed errors even where you don’t want them.

Ignoring the Err object is another pitfall. Without checking the error details, you might bypass important issues or handle different errors in the same way, leading to incorrect logic.

Failing to limit the scope of On Error Resume Next can cause it to affect other parts of your code where error suppression is not desired. This broadens the error-suppression area, which is not advised.

Being aware of these common pitfalls helps you implement On Error Resume Next in a more reliable and safe manner. Limit its scope, reset it timely, and always inspect errors through the Err object to maintain a healthy codebase.

Can I Use 'On Error Resume Next' for All Errors?

Technically, you can, but it's not recommended. Using it indiscriminately can make debugging difficult and may lead to unexpected behaviors in your program.

When Should I Use 'On Error Resume Next'?

Use it in specific scenarios where you anticipate an error and believe that the error should not stop the entire program. It's especially useful in batch processing or when interacting with external data sources.

How Do I Revert to Standard Error Handling?

To revert to standard error handling, you can use the statement On Error GoTo 0. This resets the error handling mechanism in VBA to its default behavior.

Is It Safe to Ignore All Errors with 'On Error Resume Next'?

Ignoring all errors is generally not safe and can lead to undefined behavior or bugs that are hard to trace. Always use On Error Resume Next judiciously and revert to normal error handling as soon as possible.

Let’s test your knowledge!

What does 'On Error Resume Next' do in VBA?

Continue learning with these vba guides.

• How To Use VBA Comment In Your Code
• How To Use VBA Else If In Conditional Logic
• How To Work With VBA Array In Excel
• How To Use VBA Split Function Effectively
• How To Work With VBA List Effectively

Subscribe to our newsletter

Subscribe to be notified of new content on marketsplash..

Troubleshooting Excel VBA On Error Resume Next

Did you know that the use of On Error Resume Next in Excel VBA is often plagued with issues, with the code not resuming at the next line as expected? This surprising fact highlights the importance of understanding the common problems associated with this statement and finding solutions to ensure smooth execution of your VBA code.

Key Takeaways:

• On Error Resume Next is used to ignore runtime errors and continue code execution in VBA.
• Common issues with On Error Resume Next include nested error handling and improper closure of error handling blocks.
• Proper error handling practices, such as specific error handling blocks and informative error messages, are recommended.
• Troubleshoot On Error Resume Next issues by checking for nested error handling blocks and verifying VBA settings.
• Consider alternative error handling approaches based on the complexity and requirements of your code.

Understanding On Error Resume Next in VBA

The On Error Resume Next statement is a powerful tool for handling errors in VBA code. When an error occurs, this statement allows the code execution to continue from the next line instead of halting and displaying an error message. This can be particularly useful in situations where you want to bypass errors and proceed with the execution of your code.

With On Error Resume Next, you can effectively suppress errors and prevent abrupt termination of your program. Instead of stopping at the point of error, the code moves on to the next line, allowing you to gracefully handle errors and maintain the workflow of your program.

However, it is important to exercise caution when using On Error Resume Next. While it can be a convenient way to handle errors, it can also lead to unexpected behavior if used incorrectly. It is essential to understand its limitations and implement proper error handling techniques to ensure the stability and reliability of your VBA code.

Benefits and Use Cases

The On Error Resume Next statement offers several benefits and can be used in various scenarios:

• Error Ignoring: By using this statement, you can ignore specific errors and allow the code execution to continue without interruptions. This can be helpful when dealing with non-critical errors or when error handling is not necessary for a particular task.
• Bulk Operations: When performing large-scale operations, such as data processing or file operations, using On Error Resume Next can help you handle errors gracefully without disrupting the entire operation. This allows you to handle errors on a case-by-case basis and proceed with the operation.
• Legacy Code: In some cases, you may be working with legacy code or code that has evolved over time. Introducing proper error handling throughout the codebase may be time-consuming or impractical. On Error Resume Next can act as a temporary solution to keep the code running until a more comprehensive error handling strategy can be implemented.

While On Error Resume Next can be a useful feature, it is important to remember that it is not a substitute for proper error handling. It should be used as a complement to well-defined error handling routines to maintain code robustness and minimize unexpected behavior.

Common Issues with On Error Resume Next

While the On Error Resume Next statement in Excel VBA is a powerful tool for handling errors, there are some common issues that may arise when using it. Understanding these issues can help you troubleshoot and resolve any problems you encounter.

Nested Error Handling

One common issue is when there is nested error handling, where one error handling block is inside another. In this case, the error handler inside the inner block will take precedence and the code will not resume at the next line. This can lead to unexpected behavior and make it difficult to identify and fix errors. It is important to carefully review your code and ensure that the error handling blocks are properly structured and nested.

Improper Error Handling Closure

Another issue occurs when the error handling is not properly closed off before the next line of code. The On Error Resume Next statement should always be followed by an On Error GoTo 0 statement to reset the error handling. If this is not done, the code may not resume at the next line and errors may not be handled as intended.

To fix this issue, make sure that every On Error Resume Next statement is followed by an On Error GoTo 0 statement to properly reset the error handling.

By addressing these common issues and ensuring proper error handling in your code, you can make the most of the On Error Resume Next statement and effectively handle errors in your Excel VBA applications.

Best Practices for Error Handling in VBA

While utilizing the On Error Resume Next statement can be advantageous in specific scenarios, it is generally recommended to exercise caution and adhere to best practices for error handling in VBA . By following these best practices, you can ensure robust and reliable error handling in your code. Some key practices to consider include:

• Implementing specific error handling blocks for different types of errors: It is crucial to identify and handle different types of errors separately to provide appropriate responses and actions.
• Displaying informative error messages: Clear and concise error messages help users understand the issue, facilitating troubleshooting and resolving errors effectively.
• Logging errors for troubleshooting purposes: By logging errors, you can capture valuable information about the occurrence of errors, aiding in identifying patterns and potential areas for code improvement.
• Properly handling errors within functions and subroutines: Errors that occur within functions and subroutines should be handled explicitly to prevent any unexpected behavior or issues.

By embracing these best practices, you can enhance the error handling capabilities of your VBA code, leading to more reliable and maintainable solutions.

Error Handling Examples

Let’s take a look at a few examples to demonstrate how to implement best practices for error handling in VBA :

These examples showcase how specific error handling blocks can be implemented to handle various types of errors, providing informative feedback to users and facilitating seamless execution of code.

Troubleshooting On Error Resume Next Issues

If you are experiencing issues with the On Error Resume Next statement in your Excel VBA code not working as expected, there are a few troubleshooting steps you can take to resolve the problem.

Step 1: Check for Nested Error Handling Blocks

One common reason why On Error Resume Next may not work is when there are nested error handling blocks within your code. Ensure that there are no inner error handling blocks that take precedence over the outer one. If there are, make sure to properly close off the innermost error handling block before the next line of code.

Step 2: Verify VBA Error Handling Configuration

Another potential issue could be the VBA setting for error handling. In some cases, a specific setting can cause On Error statements to be ignored, resulting in the display of a dialog box instead. To use On Error Resume Next, make sure this setting is disabled.

By following these troubleshooting steps, you can identify and resolve any issues you may be facing with the On Error Resume Next statement in Excel VBA.

Alternatives to On Error Resume Next

While On Error Resume Next can be useful in certain situations, there are alternative approaches to error handling in VBA that may be more appropriate depending on the specific requirements of your code. Here are some alternatives to consider:

• Structured Error Handling with On Error GoTo: This approach allows you to specify a specific label or line number to jump to when an error occurs. By using the On Error GoTo statement, you can have more control over how errors are handled and take appropriate action based on the specific error that occurred.
• Implementing Custom Error Handling Routines: Instead of relying solely on the default error handling behavior, you can create custom error handling routines to handle different types of errors in a more specific and controlled manner. This allows you to provide user-friendly error messages and perform additional actions, such as logging the error or notifying the user.
• Using Specific Error Handling Functions or Methods: Excel VBA provides various built-in functions and methods that can be used for error handling. For example, you can use the Err object to retrieve information about the most recent error that occurred, or the Err.Raise method to raise a specific error manually. These functions and methods offer more flexibility in handling errors based on your specific needs.

When choosing an error handling approach, it’s important to consider the complexity and requirements of your code. Analyze the specific scenarios where errors can occur and determine the most suitable alternative that provides sufficient error handling and control.

The On Error Resume Next statement is a powerful tool in Excel VBA that allows you to bypass runtime errors and continue with the execution of your code. However, it is crucial to use this statement judiciously and understand its limitations to avoid unexpected behavior in your code.

If you are encountering issues with On Error Resume Next not working as expected, it is recommended to follow the troubleshooting steps outlined in the previous sections. By identifying and resolving any underlying problems, you can ensure that your code resumes execution at the desired line.

In some cases, it may be beneficial to explore alternative error handling approaches that better align with your specific requirements. These alternatives, such as structured error handling with On Error GoTo or custom error handling routines, can provide more control and flexibility in managing errors in your VBA code.

By implementing effective error handling techniques and choosing the appropriate approach for your code, you can enhance the robustness and reliability of your Excel VBA projects. Remember, error handling is a critical aspect of software development, and investing time in proper error handling can save you valuable time in the long run.

Why is On Error Resume Next not working in Excel VBA?

How can i troubleshoot issues with on error resume next in excel vba, is on error resume next the best approach for error handling in vba, what are some best practices for error handling in vba, how can i ensure the robustness and reliability of my vba code.

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA. Join her on this journey to become an Excel ninja and revolutionize your workflow.

Similar Posts

Check if autofilter is on in excel with this vba trick.

Discover how to quickly ascertain the status of AutoFilter in Excel using this practical VBA technique. Simplify your spreadsheet tasks today.

Excel VBA: Locate First Row Post-Filter

Discover how to use Excel VBA to efficiently identify the first row of your data range after applying a filter, streamlining your data analysis tasks.

Find Columns by Header Name in Excel VBA – The Ultimate Hack

Master Excel VBA with ease! Learn how to quickly locate columns using header names for streamlined data management.

How to Bold Text in Concatenate Formula Without VBA in Excel

Learn how to bold text in concatenate formula in Excel without VBA for enhanced spreadsheet visuals. Discover simple, effective formatting tricks!

Get Filenames Without Extensions in Excel VBA – A Timesaver!

Learn how to efficiently use Excel VBA to obtain filenames without extensions, streamlining your data management and workflow.

Excel VBA Chart Y Axis Format Tips – Quick Guide

Master the nuances of Excel VBA chart Y axis number format with our focused guide. Gain clarity and enhance your data presentations effectively.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

VBA Error Handling with Resume Next, GoTo 0

The vba tutorials blog.

Jan 1, 2021

  Looking for More Excel Tutorials ?   

This tutorial focuses on the meaning and implementation of Resume Next , GoTo 0 , and GoTo -1 . These critical components add structure to the VBA On Error statement and any explicit handlers using GoTo [label] .

In brief, Resume Next goes to the next executable line, GoTo 0 turns off the currently enabled error handler, and GoTo -1 turns off the current exception. Of course, these things need a bit more explanation, otherwise we wouldn’t have written an entire tutorial about them!

Basic Error Handling Overview

Defer handling, return execution to the main block, hidden role, enable and disable handlers, clearing exceptions.

For a more thorough exploration of basic error handling techniques, read our article dedicated to the On Error GoTo statement.

Error handling in VBA is disabled by default, so it must be turned on with the On Error statement. If you want to explicitly handle errors, label a section of code and direct the execution flow to that section:

The first line in the snippet turns on (enables) the fixErrors handler, and if an error occurs in that section, execution flow moves to the part of the code with the label fixErrors: .

To shut off (disable) the active handler, use On Error GoTo 0 . Doing so will close off the code block that uses that handler. Alternatively, exit the subroutine using Exit Sub , which automatically turns off the handler.

Calling another subroutine does not exit the current subroutine so any active handlers will be stacked in the new subroutine!

Error handling code must be placed before the End Sub statement, so in order to avoid it running with the regular code, it should come after an Exit Sub statement.

It’s wise to check inputs for the correct data types, formatting, and common issues like a divisor of zero. This reduces the prevalence of runtime errors before they can even arise.

How to Use Resume Next

Resume Next plays a dual role:

• as a stand-in for a custom error handler that is marked by a label
• to return execution flow to the main body of the code after a custom handler completes

We’re going to talk about each of these roles in the next two sections.

You can defer handling errors by using On Error Resume Next , like in this snippet:

If you replace GoTo [label] with Resume Next , you can defer error handling. If an error is raised, execution simply skips that line of code and goes to the next one. If the next line raises an error, execution just skips that line, too. This definitely has some benefits, and we use it quite a lot on this site, like when checking if a file exists , but you need to be careful when applying it. If used to ignore a specific deficiency you know exists, you could mistakenly skip over an entire section of code without realizing it.

Using Resume Next to defer handling has its purposes, such as eventually running code that forcibly rectifies the offending variables and objects. But I’d strongly recommend either explicitly handling errors with GoTo [label] or avoiding errors by checking inputs with IF statements, where appropriate.

The main problem with the “ignore the errors” method is that users will not notice that the code is broken. It will run as long as deferment is active. However, some expected result will simply be incorrect or not be displayed at all. This leads to frustrated users trying to figure out what they did wrong when it was really a runtime problem with your code. Even worse, they may just accept the erroneous output, no matter how outlandish.

Resume Next to defer errors has its place, but use it wisely. A good habit is to warn others (including your future self) that you’re using it. Clearly state this in the comments at the top module and describe why you’re doing it. This habit at least makes others aware that unnoticed errors may be lurking in the code.

Resume Next is not, however, without a positive side. The same statement can return control to the main code block after entering a custom handler. This means you don’t have to end a subroutine every time a custom handler is employed.

This code snippet predicts that a divide by zero error may be triggered and fixes it with some user interaction. Once the input is received and the new result calculated, the Resume Next statement returns execution to the line immediately following the line that triggered the error.

If you’d like to return to the same line that triggered the error, you can simply use Resume , like this:

Using Resume instead of Resume Next has the added benefit of naturally looping until the user inputs a valid divisor for num_rooms .

Resume and Resume Next actually play a third role when in the error handling block: once executed, they reset the current exception to Nothing , meaning the active error handler can be used again.

Think of the error handler as a single-use tool, which must be reset before it can be used again. We know that ending and exiting the subroutine resets the handler. What this hidden role means is that Resume and Resume Next also resets the error handler.

Using GoTo 0 and GoTo -1

With VBA, only one error handler can be active at a time. This makes sense, as an error will immediately trigger the active handler, and VBA cannot guess between multiple handlers. To VBA, an error is an error is an error. You can distinguish errors using the Err object and a Select Case statement, but that’s for our next tutorial.

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Use On Error GoTo 0 to completely turn off error handling in a subroutine. This effectively resets the error handling in that sub to the default, which is no handling at all. You may decide to do this when you only have one handler, like fixErrors above, which would not make sense with a different type of error. Alternatively, you may want VBA to warn the user so they can relay error codes to you.

Note that you do not need to disable a handler before enabling another. The new one simply takes over. However, for clarity, I like to turn off a handler when its section of code ends. In this code snippet, it’s very clear which handlers go with which parts of the code.

A particular error instance is called an exception. In the following code, there is one type of error but two exceptions , because each instance of the error throws an exception:

When the first error is raised, VBA directs flow immediately to the active error handler.

The single error handler rule applies even in the error code! Once an error has been raised, the error handler is completely “full”. In order to handle another error, the handler must be emptied first. Normally this is done by exiting the handler with End Sub or Resume Next . However, there is another method, which uses On Error GoTo -1 .

Let’s return to our divide by zero handler fixErrors . You could try some calculations in the handler:

But what happens if the user inputs zero again? The full error handler cannot take another exception and leads to a fatal error, killing the program.

You can reset the handler - that is, remove the current exception - by using On Error GoTo -1 :

However, there is a flaw here! The GoTo -1 clears the current exception, and the Resume Next statement sets off an infinite loop. Execution does NOT return to the original code section.

Since using GoTo -1 can be extremely confusing, I’d recommend not using it at all. If you are adamant about GoTo -1 , use it as a last resort and finish the error handling code with End Sub .

Now you can use On Error GoTo 0 and Resume or Resume Next effectively. Unless absolutely necessary, I’d recommend steering clear of On Error GoTo -1 , as it can greatly complicate the logic of your program. I’d also recommend judiciously employing On Error Resume Next deferment, because it can mean segments of your code just don’t do anything at all.

Ready to do more with VBA? We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we'll send you a copy along with our VBA Developer Kit , loaded with VBA tips, tricks and shortcuts.

Before we go, I want to let you know we designed a suite of VBA Cheat Sheets to make it easier for you to write better macros. We included over 200 tips and 140 macro examples so they have everything you need to know to become a better VBA programmer.

Get your cheat sheets

This article was written by Cory Sarver, a contributing writer for The VBA Tutorials Blog. Visit him on LinkedIn and his personal page .

I want to help you learn VBA this year

We wrote the Big Book of Excel VBA Macros and built a VBA Developer Kit designed to help you write better macros in half the time.

Popular VBA Tutorials

Latest VBA Tutorials

• Skip to main content
• Skip to header right navigation
• Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Error Handling in VBA

If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you'll see a default error message like this:

But you can change this and instruct Excel to allow your code to deal with run-time errors.

NOTE : I’m going to use the terms sub, function and procedure interchangeably. For the purposes of this article they all mean the same thing – a chunk of code written to do a particular thing.

The On Error Statement

To instruct Excel what to do when an error occurs, you use the On Error statement. You can use On Error in four ways:

On Error GoTo 0

This is the default mode and is already turned on when you start writing your code. You don’t need to use an On Error GoTo 0 statement at the start of your VBA.

In this mode VBA displays the standard style error message box, and gives you the choice to Debug the code (enter VBA editor and use debugging tools) or End code execution.

You would use On Error GoTo 0 to turn default error handling back on if you have previously told VBA to deal with errors in some other way e.g. by using On Error Resume Next.

On Error Resume Next

On Error Resume Next tells VBA to continue executing statements immediately after the statement that generated the error.

On Error Resume Next allows your code to continue running even if an error occurs. Resume Next does not fix an error, it just ignores it. This can be good and bad.

If you know that your code could generate an error, then using Resume Next can prevent an interruption in code execution.

For example, we want to create a file, but I want to make sure a file with the same name doesn’t already exist. To do this, I will attempt to delete the file, and of course if it doesn’t already exist, an error will occur.

I don’t care if an error occurs. If it does, the file doesn’t exist and that’s fine for what I want to do. So before I attempt to delete the file, I instruct VBA to ignore the error.

But hang on. What if the file I am trying to delete is read only? If it is I will get an error when I try to delete it. I’ve assumed that I will only get an error if the file isn’t there. So an error caused by trying to delete a read only file will get missed.

And If I then try to create a new file with the same name, or open it for writing data to it, I will generate more errors and they will be missed too.

If you do use On Error Resume Next you should immediately turn default error handling back on (or turn on your custom error handler – see below)

Be careful when using On Error Resume Next. You are better off seeing if an error occurred by checking the Err object (see below). Doing this can tell you the error number and help you figure out exactly what happened.

On Error GoTo [LABEL]

If an error occurs, this transfers code execution to the line following the label. This is typically used to specify your own error handling code.

None of the code between the line generating the error and the label is executed.

Error Handlers

So you write your own error handling code and use On Error GoTo [LABEL] to instruct VBA to use your code to deal with errors.

You can place your error-handling code anywhere in a procedure, but typically it is placed at the end.

Your error handler should either fix the error and resume code execution, or terminate the routine gracefully.

As VBA will execute each line of code in turn going from top to bottom, if no error is generated then it will execute your error handling code when it gets to that point in your sub.

To prevent this happening, use an Exit Sub, Exit Function, or Exit Property statement before your error handling routine.

In the example above, if the value assigned to num was valid e.g. num = 1/1, then we don’t want the code beneath ErrHandler: executed. So just before the ErrHandler: label, I've used an Exit Sub statement.

Multiple Error Handlers

You can have more than one error handler in a routine, but only one of them can be active at any time.

You could have something like:

If an error occurs between On Error GoTo ErrHandler1 and On Error GoTo ErrHandler2 then the ErrHandler1 code is executed.

If an error occurs after On Error GoTo ErrHandler2 then the ErrHandler2 code is executed.

NOTE: Notice that at the end of each error handling routine is an Exit Sub statement. If I didn’t use these, when the ErrHandler1 code is finished executing, VBA could just continue on down to the next line and execute the ErrHandler2 code as well.

Strictly speaking I don’t need the Exit Sub at the end of the ErrHandler2 code, as it is the last line in the sub, but it is a good habit to get into.

When an error occurs the Err object contains information about the error like the error number and a description of the error.

As any given line of code can generate multiple errors it’s a good idea to examine the Err object to determine what you want to do in your code.

Err.Number gives you the error number, and Err.Description gives you a description of the error.

The Resume statement tells VBA to resume executing code at a specified point. Resume can only be used in an error handling routine, any other use will generate an error.

Resume takes three forms:

Using just Resume causes execution to resume at the same line of code that caused the error. If you haven’t fixed the error, your code will begin an infinite loop as it switches between the line of code generating the error and the error handling routine.

If you look at the example sub Resume_Next() which is below, num = 1 / 0 causes a Divide by 0 error. I’ve instructed VBA to use my error handler, called ErrHandler.

In ErrHandler I’ve attempted to fix the error by assigning num the value 1. If I then used only Resume, all that would happen is that VBA would go back to num = 1 / 0 and another Divide by 0 error would be generated.

Instead, I use Resume Next to carry on executing code at the line after the one causing the error.

In doing so I have handled the error by assigning the value 1 to num, and execution will continue without another error at the line result = num / 1

Resume [label] passes code execution to the line with that label.

Whenever you use Resume it clears the Err object.

Error Handling With Multiple Procedures

Every sub/function doesn’t have to have an error handler. If an error occurs, VBA will use the last On Error statement to determine what happens.

If an On Error statement has been used in a procedure and an error occurs in that procedure, then that error is handled as I've just described.

But if an error occurs in a sub that hasn’t used an On Error statement, VBA goes back through procedure calls until it finds an On Error directive.

Let’s look at an example with three subs.

SubOne() calls SubTwo(). SubTwo calls SubThree(), and has some code of its own to execute. SubThree() carries out a calculation.

SubOne() has an error handler routine, ErrHandler, and has instructed VBA to use it.

SubTwo() will display a message on screen after it’s call to SubThree() has finished.

However SubThree() generates a Divide by 0 error.

SubThree() hasn’t used an On Error statement to tell VBA what to do if an error occurs, so VBA goes back to SubTwo(). That also doesn’t have an On Error statement so VBA goes back to SubOne().

Here we have our error handler and the code in it is executed.

Note that the message "No errors here" in SubTwo() is not displayed because that line of code is not executed.

When SubThree() generated an error, code execution went back to the error handler in SubOne() and any code in SubTwo() after the call to SubThree() is missed out.

On Error GoTo -1

This resets the current error. It’s the equivalent of using Err.Clear.

You can see that in this sub, after the Divide By 0 error is generated, after On Error GoTo -1 is used, Err.Number is 0 (no error) and Err.Description is empty.

Sample Code

Enter your email address below to download the sample workbook.

Download this workbook for all the sample code used here plus several other examples.

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub , Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User .

More Excel VBA Posts

Checking Values in Range Objects With VBA

Static Variables in VBA

Save Chart as Image

Clear Downstream Dependent Data Validation Lists

Excel Status Bar

Excel Progress Bar for VBA

Finding File Meta Data Using FileSystemObject

Automatically Add Items to Data Validation List

Excel End of Period Dates

Add Data to Combo Box Drop Down List in Excel Form

Center Across Selection Macro

Display All Matches from Search in Userform ListBox

Animating Excel Charts

Dynamic Data Validation Lists in Userforms

Show Report Filter Pages for Power Pivot PivotTables

Charting Real Time Data in Excel

Select Multiple Items from Drop Down (Data Validation) List

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Automating and Emailing Pivot Table Reports

Searching for Data With a User Form

Reader Interactions

December 7, 2022 at 4:53 am

Nice explanation! Good clear examples! Thanks

Philip Treacy

December 7, 2022 at 8:13 am

Thanks John, glad you found it useful.

Alan Elston

January 13, 2021 at 1:05 am

Hi Philip Error handling in VBA doesn’t seems to be fully understood by almost anyone, and there are not many Blogs on it, so this Blog of yours is a useful Blog to have.

I am just slightly put off by your last bit about the On Error GoTo -1 In particular, your statement …”… This resets the current error. It’s the equivalent of using Err.Clear….” I expect you didn’t mean to say they are similar things, but it might be read like that by the unwary….

Here is my take on it, mostly what I have got from experimenting….

On Error GoTo -1 takes Excel out of the so called “exception state”. It also does clear the Err object registers, (equivalent of using Err.Clear ). But the later is secondary to its main function of “clearing the exception”. The following other error things also , in addition to their main function, clear the Err object registers – _ On Error GoTo 0 , _ Resume, ( Resume; Resume Next; Resume [label] ) , _ changing the error handler So those 3(5) things have the same effect on, and the same relevance to, Err.Clear as On Error GoTo -1 does/ is. They all do like Err.Clear as a secondary thing in addition to their main thing.

It is a common mistake to see Err.Clear being regarded as the same as, or similar to, On Error GoTo -1

On Error GoTo -1 is not equivalent of using Err.Clear. It does ( also ) clear the error object, (equivalent of using Err.Clear ).

It seems that every time I answer a forum question or make a comment or read a Blog on error handling in VBA, I find some other small feature of it that I had previously missed. It seems that to fully understand error handling in VBA, it needs an explanation that is too long for a Blog, but possibly too small for a book. I am not sure yet if anyone fully understands the subject. Chip Pearson and a few others who followed his examples have completely ignored the On Error GoTo -1 and exception state issue, although he did touch on the exception state in his comment to your Blog…. There is an awful lot of similar mistakes in coding caused by not understanding all the issues, in particular caused by not considering the effects of the “exception state”. Missing that last bit of important understanding seems to cause almost everybody to trip up, and end up getting something wrong, even some of the best experts.

January 13, 2021 at 9:46 pm

P.S. I can go a long way in explaining what I was getting at with a very simple example. This following example has solved a similar Forum question that I must have answered now about a hundred times. The mistake it illustrates is made by newbies and experienced professionals alike: The code snipped below simulates a typical coding problem whereby somebody suddenly notices that it’s not handling an error more than once. At first glance one might expect that this macro will loop 10 times, thereby handling an error 10 times.. It doesn’t. On the second loop, the default VBA error handler pops up and code execution stops…

Sub OnlyTrapOneError() Dim Rslt As Double, Cnt As Long For Cnt = 1 To 10 ‘ Some coding not expected to error ‘ On Error GoTo sKip ‘ I might be expecting the next code line to error, so I have introduced my error handling to handle it Let Rslt = 1 / 0 ‘ ‘ sKip: On Error GoTo 0 ‘ I am not expecting any more errors , so good practice is to remove my error handling ‘ ‘ Some other coding not expected to error Next Cnt End Sub

That simple macro is using good coding practice in that it only uses ( enables ) the error handler when it might need it. In other words we “turn it on” with On Error GoTo sKip and “turn it off” ( return to default VBA error handling) with On Error GoTo 0

So far so good. But the problem is that mostly when an error occurs, VBA coding goes into what is referred to as an “exception state”. The two main characteristics of the “exception state” are: _ In this state further errors are handled by the default VBA error handler _ Any attempts to enable another error handler are ignored. ( We can say those two things in a more simple way: VBA is busy in a state handling the first error and cant handle more than one at a time…)

One solution to the problem is to also include an On Error GoTo -1 A good place for it would be before ,or after, the On Error GoTo 0 It makes no difference if it’s before or after: those two On Error statements don’t effect each other in any way***.

An alternative solution would be just to replace the On Error GoTo 0 with On Error GoTo -1. But that is slightly less good practice, since we have are error handler active for more of the coding than we probably need it. BTW, if we did chose to just replace the On Error GoTo 0 with On Error GoTo -1, then we could move the On Error GoTo Skip to outside the Loop, so it’s only done once. This last solution would work because On Error GoTo -1 does not disable the error handler: On Error GoTo 0 disables the error handler, ( and BTW, On Error GoTo 0 does this in the normal or “exception state” ***) _._______ In a recent forum post, the OP who I explained this all to came up with a simple summary I liked: on error goto 0 — disables error trapping but does not reset the error state. Any new error trapping is ignored until the state is reset on error goto -1 — resets the error state, restoring error trapping as it was before the error occurred _.______

I should say finally that I agree with most people that one should try to avoid using error handling in coding to solve a possible expected problem , and instead use, if at all possible some other way that does not invlove error handling. Run time error handling in VBA should be used intelligently. That is rather difficult in the practice since very few people, not even some the most experienced professionals, fully understand it.

Chip Pearson

December 18, 2015 at 5:51 pm

This is a fine article on error handling in VBA. Most people just put “On Error Resume Next” at the top of the procedure and assume that any errors will be fixed automatically. A very bad assumption, but I’ve seen it more times than I like to think. There are a few minor points that I would add to your article. The first is that when code execution drops into an error handling block (e.,g “On Error Goto ErrHandler”), ALL error handling within the error handler is turned off, and if an run-time error occurs within the error handling block, you get an untrappable run-time error. For example, examine the following code.

Dim X As Long Dim Y As Long

On Error GoTo ErrHandler

X = 10 Y = 0 10: Debug.Print X / Y

20: ErrHandler: Debug.Print "Last successful label: " & Erl On Error Resume Next 30: Debug.Print X / Y

The code will raise an Div/0 error at line 10, at which execution transfers to ErrHandler: . VBA is now running in “error mode” and the Div/0 error at line 30 cannot be trapped. It will always raise an untrappable error. The code above also illustrates the use of the little-known Erl statement. Its value is updated every time a numeric line label is encountered, and can be read in an error handling block to indicate the last successful line label. In the code above, the debug message will show that line 10 was that last successfully labeled line. I put line labels to separate logically connected blocks of code, so I can easily see more or less where the error arose. There are any number of free add-ins that will put a line number on every line of code, and Erl will then identify the exact line of code. I think this is overkill. I put in a line label every 20 or so lines, depending on the structure of the code. Note that the line labels must be numeric (e.g, 10: . Erl will not recognize alphabetic labels (e.g,. ErrHandler: .

Next, should ALWAYS ALWAYS ALWAYS set error handling in the Options dialog to Break In Class Module . Suppose you have code to show a user form:

UserForm1.Show

If a run time error occurs within UserForm1’s object module, the debugger will highlight the line above as the source of the error. Clearly, this line is not an error, and no matter how many hours you stare at that line, you won’t find an error. But if you set error handling to Break In Class Module, the debugger will take you inside UserForm1’s object module directly to the line of code that really is the source of the error.

Another technique I use when the code calls procedures that call other procedures and so on, whose error handling is indecipherable, is to put an error handler in the procedure that initiates then entire chain of procedures. For example, Sub AAA() On Error GoTo ErrHandler: BBB Exit Sub ErrHandler: Debug.Print "Last Chance Handler" End Sub

Sub BBB() CCC End Sub

Sub CCC() DDD End Sub

Sub DDD() Debug.Print 1 / 0 End Sub

Here, the error handler in proc AAA will take over if there is no other error handling. This is especially useful if you have to use code that you did not write yourself. All it does is provide a last chance error handler to prevent an untrapped error dialog. It doesn’t do anything except prevent the untrapped error dialog from popping up. An untrappped error dialog tells the world you don’t know what you are doing, and if you are a consultant, you’ll likely lose the client over that one dialog box. It provides a graceful and non-threatening way to handle an error whose source over which you have no control.

I just thought you would find this interesting and I think it would be a worthwhile addition to your fine article on error handling. I usually code in VBNET or C# and use Try/Catch/Finally structures for error handling, and when I have to go into VBA, On Error in all its variations seems extremely primitive.

December 21, 2015 at 8:59 pm

Thanks for reading the article and leaving such a long and detailed reply, much appreciated.

You make good points about both the un-trappable errors and enabling Break In Class Module. Nothing worse than being shown a line of code that hasn’t actually generated the error.

In the section ‘Error Handling With Multiple Procedures’ I tried to convey what you are saying about having a ‘last chance’ error handler, but I like your example of doing this if using code you didn’t write yourself.

I write a fair bit of PHP so I am familiar with Try/Catch and having that would indeed be a big improvement for VBA.

April 23, 2020 at 1:38 am

Great article. It helped me a lot with handling errors in my code.

I am a newbie and don’t have much knowledge about VBA. I am working on a module that throws an error inside the error handler. Can you guys please elaborate on how to handle if an run-time error occurs within the error handling block. I thought another error handler inside the first error handler would help but as Chip said it’s not going to work.

I am trying to scrape prices and there are more than 2 condition for scraping it. 1st one is taken care by the ‘ErrHandler1’ but I can’t get though how to make my code skip the error in ‘ErrHandler1’ and execute the code in ‘ErrHandler2’. It would be great if you guys can help me out.

April 23, 2020 at 2:30 pm

Hard to give you advice without seeing the code and recreating the error.

Please post a topic on our forum and attach your workbook with a description of how the error is generated.

Joyce Swensson

December 12, 2015 at 5:51 am

Thank you for this. I am working on improving error handling in my code. It seems to me it might be helpful to have an “error log” created that captures the values of various variables, the error number and description etc. Hopefully, I can figure this out.

December 14, 2015 at 12:15 pm

I guess in an ideal world you’d be able to anticipate any errors and deal with them in your error handling routines. But for unforeseen circumstances, you could write the values of variables and any error data to a file and use that as your log.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Current ye@r *

Leave this field empty

Excel VBA: Turn Off the “On Error Resume Next”

One of the most important jobs in Microsoft Excel VBA is error handling. If you’re a programmer, you’re well aware of the necessity of error handling in creating a flawless application. Any error in a statement can wreak havoc on your VBA code in a variety of ways. As a result, you must exercise caution when handling errors in the VBA code. Today, In this tutorial, we will learn very quickly and the most time-saving way to VBA On Error Resume Next turn off in Excel with appropriate illustrations.

On Error Statements in VBA

While working with Microsoft Excel VBA, we will face a lot of errors in our sub-procedure. When VBA can not run a statement, it shows a run-time error. To handle run time errors, we instruct Excel with the On Error statement. It decides what kind of operations we want to do next immediately. Basically, we disable these errors by these kinds of error handling.

We use three kinds of On Error statements(syntax) in Excel VBA. The description of these On Error statements is given below.

Introduction to the On Error Resume Next Statement in Excel VBA

Now, the On Error Resume Next statement tells VBA to ignore any lines of code having errors and proceed immediately to the following line of code. After that, the Excel VBA codes will skip the line or lines that contain errors in them, and move on to the following sequence of code. When your code is needed to run even if an error happens, the On Error Resume Next statement allows it. The On Error Resume Next statement can not correct an error, but this statement just ignores the error.

The On Error Resume Nex t statement becomes idle when your code calls another procedure. So, when you need a matched error handling in that routine, you have to run an On Error Resume Next command in each named pattern. It is reasonable when the line of code that you can skip is not necessary to the flourishing running of the macro. But remember, it can be detrimental if you use it incorrectly as it may provide unintended results.

Now, look at the following VBA code.

We tried to divide 10 with 0 and 5. Let’s run the code. It will show the following output:

It produces a run-time error. We can not divide a number by 0. When you debug the code, you will see the following:

When the VBA program finds an error, it immediately stops the procedure. It doesn’t execute the following line.

Now, let’s implement the On Error Resume Next statement before the error statement:

Hence, run the code, you will be able to see the following:

As you can see, VBA ignores the line that produces the error and proceeds immediately to the following line of code. In this way, you can use the On Error Resume Next statement to handle the error in Excel VBA.

Let’s say, we have a dataset that contains information about several students of Cantonment School and College. The names of the students and their identification number and their securing marks in Physics and Chemistry are given in columns B, C, D, and E respectively. We will turn off the On Error Resume Next statement by applying a VBA code . Our article is about how to turn off the On Error Resume Next . You can solve this problem in one simple way. The way is On Error Goto 0 statement. Here’s an overview of the dataset for today’s task.

Now, you may be in a situation where you want to ignore errors for a particular segment of the VBA code. Remember, if you use the On Error Resume Next statement in a VBA code, it will skip all the errors after that. Now, if you want to turn that off and enable the error handling for another segment, use On Error GoTo 0 . It will enable error handling again. Follow the instructions below to turn off the On Error Resume Next statement!

Read More: Excel VBA Error Handling in Loop

Step 1: Open the Visual Basic Window

• Firstly, from your Developer ribbon, go to,

Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications will instantly appear in front of you. From that window, we will insert a module for applying our VBA code . To do that, go to,

Insert → Module

Step 2: Run the VBA Code to Turn Off the On Error Resume Next Statement

• Now we will write down the VBA code. Type the VBA Code below in your module.
• Will create a sub-procedure named
• This code will declare dimension M as long type data.
• The VBA code will ignore error in this portion.
• As we can not divide any number by zero, the VBA code will display error in this portion.
• After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

• While running the code, a warning message will appear in front of you, and press Debug to get the error code.

• After completing the above process you will be able to turn off the On Error Resume Next statement that has been given in the below screenshot.

Things to Remember

• While ignoring the known error, we will use the “On Error Resume Next”
• You can turn off the On Error Resume Next statement in your VBA code in Excel by adding the On Error GoTo 0
• You also can turn off the On Error Resume Next statement by applying the On Error GoTo -1 statement.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

I hope all of the suitable methods mentioned above to turn off the On Error Resume Next statement will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Related Articles

• How to Solve Overflow Error in VBA
• Excel VBA Error Handling Best Practice
• [Fixed]: Can’t Find Project or Library Error in Excel

<< Go Back To Errors in Excel | Learn Excel

What is ExcelDemy?

Tags: Errors in Excel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

How to copy file with VBA in Excel (9 examples)

VBA in Excel gives us access to parts of the Windows environment. Therefore, there are many file-based activities we can achieve with VBA Macrs in Excel. One common method is using VBA to copy files from one folder to another.

In this post, we look at 9 examples of how to achieve this.

• BasicSyntax
• Example 1: Copy a file

Example 2: Copy a file using variables

Example 3: copy a file based on cell values, example 4: check file existence before vba copy, example 5: avoiding errors when copying files.

• Example 6: Selecting file to copy

Example 7: Copy file with function

Example 8: moving vs copying, example 9: alternative method – fso, possible errors, notes on copying files.

Download the example file:  Click the button below to join the Insiders program and gain access to the example file used for this post.

File name: 0205 VBA Copy File.zip

Basic Syntax

In this post, we use the basic FileCopy method. For more advanced options, we can use the File Systems Options.

FileCopy is a VBA function that accepts 2 arguments.

• source – Text string specifying the file to be copied. The argument is required.
• destination – Text string specifying the target file name. This argument is required.

In this section, we work through 9 examples to demonstrate how to use the FileCopy statement.

Example 1: Copy file

The example below copies Example File.xlsx from the C:\Users\marks\Documents\ folder to the C:\Users\marks\Documents\Test\ folder.

Change the file paths to make this code work in your environment.

Note: In this example, the file location has changed, but the file name is unchanged.

In Example 1, the file names were included within the FileCopy statement. However, they can also be provided as variables.

In the code below:

• The variables are declared (i.e., created)
• Values are assigned to the variables
• The variables are used in the FileCopy statement

Note: In this example, the file is not copied to a new location, but is renamed from Example File.xlsx to Example File Copied.xlsx

In this example, we copy a file using file paths contained in cell values.

Look at the screenshot below. Cell C3 contains the current file path and Cell C5 contains the path to which the file is to be copied.

We can run the following macro to rename a file using these cell values.

Note: In this example, the file is copied to a new folder and also renamed from Example File.xlsx to Example File Copied.xlsx

The FileCopy command will overwrite an existing file without showing any errors. Therefore, it is a good idea to check if a file already exists before copying it.

The code below checks for the existence of a file in the target location. If a file exists, a message box with a Yes/No options appears. Clicking No exists the macro.

Copying files can trigger errors; the errors are outlined in the section below. The following code builds on Example 4 and also provides an error message box if any errors occur.

Example 6: Select the file to copy

Sometimes we want the ability to select a file, so let’s take a look at how we can achieve this.

In this example:

• A user selects a file path in a cell on the worksheet, this will be the destination (Cell C3 )
• When the macro runs, a user selects the file using the File Open dialog box, this file will be the source.
• The source file is copied to the destination file path.

With VBA we can create worksheet functions. Therefore it is possible to copy a file each time a formula executes.

The following is a simple VBA function to copy a file.

The screenshot below shows an example of using the function:

The formula in cell C7 is:

Whenever the function calculates, it uses the values in cells C3 and C5 as the source and destination.

If the file copies, the value returned is File Copied ; if the file is not copied the value returned is File Not Copied .

There are many occasions when we want to move a file rather than copy it. In these cases, instead of the FileCopy statement, we use the Name statement.

The Name statement does not just rename a file, but it also changes the file path. Therefore Name will move a file.

The File System Object (known as FSO) is a reference library that provides additional features for working with files.

While this post is not about the FSO method, it is useful to compare the code.

The CopyFile statement within FSO cannot be called by itself, it requires a reference to the FSO library to be created first.

The syntax of the FSO CopyFile method is:

• overwrite – Boolean value specifying if the destination file should be overwritten. True = overwrite, False = do not overwrite. The argument is optional if excluded, the default is false.

FSO Example :

Note : FSO can be used in two forms, late binding or early binding. The code above uses late binding, so there is no need to create an explicit reference to the FSO library.

Learn more about the File System Object Method here: https://wellsr.com/vba/2018/excel/introduction-to-the-vba-filesystemobject/

Copying a file during an automation can cause errors. Below are some of the common errors from the VBA FileCopy method.

Copying a file that does not exist triggers an error: Run-time error’53’: File not found .

Copying a file to a file location that is locked (i.e., another user has the file open) triggers an error: Run-time error ’70’: Permission denied

To finish off this post, there are just a few things to make you aware of:

• We have used Excel workbooks in the examples, but we can use any file type.
• FileCopy cannot copy complete folders, only files.
• The FileCopy command is core Visual Basic code. We find this code in other applications windows applications such as Word and PowerPoint .

Also, check the VBA code library for hundreds of reusable code snippets.

Related Posts:

• How to create multiple folders at once with Excel
• VBA code to loop through files in a folder (and sub folders)
• VBA code to copy, move, delete and manage files

Discover how you can automate your work with our Excel courses and tools.

Excel Academy The complete program for saving time by automating Excel.

Excel Automation Secrets Discover the 7-step framework for automating Excel.

Office Scripts: Automate Excel Everywhere Start using Office Scripts and Power Automate to automate Excel in new ways.

Leave a Comment Cancel reply