resume function 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?

Additional resources

120+ Professional Project Management Templates!

Save Up to 85% LIMITED TIME OFFER

Vba resume statement.

Share This Post

VBA Projects

Full Access with Source Code

Designed and Developed by PNRao

Full Access with VBA Source Code

Well Commented Codes Lines

Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ pm templates includes:.

50+ Excel Templates

50+ PowerPoint Templates

25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

The Resume statement in VBA is a crucial tool for handling errors in programs and macros. It allows the program to continue executing after an error has occurred, by redirecting the control flow to a specified label or line of code. In this blog post, we will explore the purpose, syntax, top 5 examples, important notes and remarks of VBA Resume statement and its usefulness in error handling.

The main purpose of using Resume statement in VBA is to handle runtime errors efficiently. It enables the programmer to anticipate any potential errors and handle them gracefully, without disrupting the execution of the program. By using the Resume statement, the program can resume where it left off and allow the user to take appropriate actions to resolve the error.

The basic syntax of Resume statement is as follows:

The ‘On Error Resume Next’ statement tells the program to continue with the next line of code if an error occurs. If the error is not handled, the program will continue to run until it encounters another error or reaches the end of the code.

Examples of using VBA Resume Statement

Ignoring specific errors.

If you want to ignore a specific error and continue with the execution of the code, you can use the ‘Resume Next’ statement. For example:

Resuming at a specific line

If you want to resume the execution of the code at a specific line when an error occurs, you can use the Resume statement followed by a label or line number. For example:

Handling different types of errors

The Resume statement is also useful when you need to handle different types of errors in a code. It allows you to specify different actions for each error type. For example:

Resuming after a ‘Try-Catch’ block

If you are using a ‘Try-Catch’ block in your code, you can use the Resume statement to specify where the program should resume if an error occurs. For example:

Using Resume to handle unexpected errors

There might be cases where you are not sure which line of code will cause an error. In such cases, you can use the Resume statement with an error handling routine to handle any unexpected errors. For example:

Important Notes & Remarks

• The Resume statement can only be used after an ‘On Error’ statement.
• If you use the Resume statement without specifying a label or line number, it will resume at the next line.
• It is important to handle errors appropriately using ‘If…ElseIf…Else’ statements to avoid infinite looping.
• The ‘Err’ object stores information about the last error that occurred. It has properties like ErrorNumber, Description, HelpContext, etc.
• Always test your code thoroughly to make sure it runs without any errors.

Concluded Post

VBA Resume statement is an essential tool for handling runtime errors in programs and macros. It allows the program to continue executing after an error occurs, by redirecting the control flow to a specified label or line of code. The Resume statement can help you handle different types of errors, resume at a specific line, and ignore specific errors. It is important to use this statement correctly and handle errors appropriately to avoid any unexpected results.

Thank you for reading this blog post on VBA Resume statement. We hope you found it informative and helpful in understanding the importance and usage of this statement in error handling.

We would love to hear your thoughts and feedback on this post. Do share your views in the comment section below.

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

All-in-One Pack 120+ Project Management Templates

Essential pack 50+ pm templates, excel pack 50+ excel pm templates, powerpoint pack 50+ excel pm templates, ms word pack 25+ word pm templates, ultimate project management template, ultimate resource management template, project portfolio management templates, related posts.

VBA Error Statement

VBA Function Statement

VBA Date Statement

Leave a comment cancel reply.

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

Effectively Manage Your Projects and  Resources

ANALYSISTABS.COM provides free and premium project management tools, templates and dashboards for effectively managing the projects and analyzing the data.

We’re a crew of professionals expertise in Excel VBA, Business Analysis, Project Management. We’re Sharing our map to Project success with innovative tools, templates, tutorials and tips.

Project Management

Download Free Excel 2007, 2010, 2013, 2016, 2019 + Microsoft Office 365 Templates, Dashboards, Tools for Project Management, Data Mining, Analysis, Visualization and VBA Automation. Learn Project Management and VBA for Microsoft Excel, Word, PowerPoint, Access, Outlook to develop applications for retail, insurance, banking, finance, telecom, healthcare domains.

© 2023 Analysistabs | Sitemap | Your Privacy | Terms

• Forgotten Your Password?

• Mark Forums Read
• Quick Links :
• What's New?
• Members List

Forum Rules

• Commercial Services
• Advanced Search

• Microsoft Office Application Help - Excel Help forum
• Excel Programming / VBA / Macros
• [SOLVED] Resume vs GoTo statement

Resume vs GoTo statement

Thread tools.

• Show Printable Version
• Subscribe to this Thread…

Rate This Thread

• Current Rating
• ‎ Excellent
• ‎ Average
• ‎ Terrible

Hi, I found a post which said: Using Goto doesn't clear the Err object (so Err.Clear is necessary if you use Goto) and it leaves your error handler disabled. If an error occurs after the Cleanup label, it won't be handled at ErrorHandler. Using Resume clears the Err object and it switches your error handler back on (it is disabled while it is handling errors). If an error occurs after the Cleanup lable, it will be handled at ErroHandler The link to this one is below: HTML Code:  I am new to VBA and still learning. Are the above statements correct? Following that principle, the Err.Clear statement in the code block is not needed because the Resume statement automatically clears it. Is this right? Please Login or Register to view this content. new Clipboard(".copy2clipboard",{target:function(a){for(;a ? a.getAttribute?a.getAttribute?!/bbcode_description/.test(a.getAttribute("class")):null:null:null;)a=a.parentNode;for(var b=a.nextElementSibling;b?!b.classList.contains("bbcode_code"):null;)b=b.nextElementSibling;return b}});

Resume vs GoTo statement. Error Handling in VBA ;) WALOFB ;) WTF is he talking about ;)

billj ( that would not be Bill Jelly , my Old mate would it ?? ) Error handling in VBA cannot easily and quickly be explained. You will need to carefully read up on it, for example here: http://excelmatters.com/2015/03/17/on-error-wtf/ and here: https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo But let me try to help a bit with emphasis the part of it you have asked about I think you are possibly mixing up two different things: _1) the Error situation, that is to say what is actually going on after an Error and / or the Error handling of that situation and _2) Err Object. Err Object has two Methods and a few Properties Err is not directly related to the actual Error The main purpose of Err is to hold information about the last Error which occurred. Err is an Object that you could think of as a Form or questionnaire that has not yet been filled in. Or think of it as a register of information about the last error that occured When an error occurs it is updated with various information about the Error. For example, the_.. Err.Description _..Property will return you a string of text to describe the Error. So you often see this at an error handling Code section: MsgBox "Your error was " & Err.Description & "." 'Use Description Property of Object Err to indicate Error You can use the Err.Clear Method to clear the registers, that is to say remove all the information of the error held within Err Object. But note_... http://excelmatters.com/2015/03/17/o...comment-188401 _.. your information is incorrect . In fact the Err Object is cleared by an error statement such as On Error GoTo some_label/line_number I expect you were referring to the Error situation. That is not cleared by such an Error Handling Statement The Error situation is much more difficult to explain. That Error situation has nothing directly to do with the Err Object. At the occurrence or an error, the Err Object registers are filled with string information about the Error which occurred. That is all. ( You do have the Err Method Err.Raise , which allows you to fool VBA into thinking an error has occurred when it has not ) _................................ _......_____________________ Briefly, or as briefly as I can try to explain a lot ... An “Error Situation” , sometimes called an Exception, or Error exception , or an Erected Exceptional VBA state, etc..etc.. is a situation in which VBA is “thinking” something is wrong. It behaves in such a situation differently. For example it ignores any further attempts by you to define a new Error Handler. It also ignores your attempts to “switch off” or “switch back” to the default error handler. On Error Resume Next is a user defined Error handler which overrides the default error handler. It actually suppresses an error situation being raised, and instructs the program to continue just after the line which would have raised an “Error Situation”. In the case of using On Error Resume Next Error handler Statement, VBA has been prevented from being aroused to its Erected exceptional state of being. Because of this suppression, you will often see the Error Handler Statement On Error Resume Next being accompanied close by ** a further Error Handling Statement , On Error GoTo 0( One might use also an On Error GoTo some_label/line_number user defined error handler instead here, assigning a different user defined error handler. - Again this is possible because VBA has been prevented from being aroused to its Erected exceptional state of being. ) On Error GoTo 0 This Error handler Statement “goes” nowhere. It “switches off” the current user defined or “switches back” to the default error handler. It would normally only work if no Error had been encountered. But in the case of using On Error Resume Next it will work. This is because, as noted, On Error Resume Next has surprised the Arousal On Error GoTo some_label/line_number For example like On Error GoTo ErrLabelAreaCodeSection42WonkGoodEveningHowAreYouSirBollox On Error GoTo 200 These Error Handling Statements do “go somewhere!”. They “goes” to the code section labelled in such a way some_label: ( One can also replace the label with a code line, like 200 in the example whether or not you have code lines in your code ( You can put any number anywhere in any order in VBA ) ). In this case there is no suppression of the aroused Erected Exceptional VBA state. VBA is now “thinking” something is wrong. It behaves in such a situation differently. For example it ignores any further attempts by you to define a new Error Handler. It also ignores your attempts to “switch off” or “switch back” to the default error handler. However, this error handler allows the code to continue, ( rather than stopping as is the case by the default error handler ) . It continues at the point that the code “goes to”. Typically things are done there that should be done on the case of an error. Resume or Resume Next You would typically have this at the end of your error handling code section sent to by an On Error GoTo some_label/line_number . You would use this after you have changed what may have caused the Error. You had better be sure you change what caused the error, as the error Erected Exceptional VBA state is cleared by these Error handling Statements and then the code goes back and tries again at the code line that errored. So you cause an infinite loop if you do not sort out at the Error Handling code section what caused your error!!! _-------------- Now back to our Err Object Object Err There is an Object Err available to us. This has a couple of Methods and some Properties. These Properties are strings of information about the last type of error that was encountered. So this can be used, for example, as an indication of if an error occurred after using On Error Resume Next . Note however, If one is wanting to utilize this information in the case of a On Error Resume Next , then one should do that before an On Error GoTo some_label/line_number or a On Error GoTo 0 . ** This is because, these two Error Handling Statements Clear that information. ( This clearing can also be done by a code line using the Err.Cle ar Method ) Err.Clear has no effect on the current error situation. It simply clears some strings of information. Alan ' http://www.excelfox.com/forum/f23/di...-894/#post9376 ' http://excelmatters.com/2015/03/17/o...comment-188401 ' https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo ' http://www.eileenslounge.com/viewtop...192781#p192781 ' http://www.excelforum.com/excel-prog...ml#post4493107 ' http://www.excelforum.com/excel-prog...ml#post4529827

Last edited by Doc.AElstein; 11-26-2016 at 10:33 AM .

'_- Google first, like this _ site:ExcelForum.com Gamut Use Code Tags: Highlight code; click on the # icon above, Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE http://www.excelforum.com/the-water-...ml#post4109080 https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

Re: Resume vs GoTo statement

Hi, Doc.AElstein, really good answer with the useful link to the sources too. Thank you so much.

Hi billj, Thanks for the feedback and Rep thingy_ ... _ ... I am adding a few notes here, mostly for my own later reference to tidy up some notes of mine on Error handling in VBA. _... ( ( BTW, Initially I thought you might have been asking the difference between On Error Resume Next and On Error GoTo __Label_/-Line number ( although after checking here http://stackoverflow.com/questions/2...handling-block it is more clear that you were not asking that ). You got some good answers there.. it is a bit debateable whether one should cross post. Sometimes it can confuse things a bit. But as you gave the Link it is OK ) _... _....So.. my explanations may not have quite been tailored to what you were asking . But no harm done. Often ., like me, people first hit this tricky area of VBA when a On Error GoTo Error Handle only works once.. On Error Resume Next works. On Error GoTo only works once: http://www.excelforum.com/excel-new-...err-clear.html The answer should then be clear now that the On Error GoTo does not clear this weird “aroused exceptional error state” in which amongst other things it no longer responds any attempt to assign an Error handler. So [color=Blue]On Error GoTo ___ only works once[/U]. _...So, anyways... I am posting a bit more for future reference as it helps me to get clear an almost infinite combination of these things error things.... I post some codes here: http://www.excelforum.com/showthread...79#post4530379 http://www.excelforum.com/showthread...=1#post4530384 I have ‘Commented them a bit, but it is a bit much to go through until you have a good idea of the whole business. I updated my Word Doc link also ( and the codes are in it ) https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo ( Also I added some new notes as I learned a bit more about the Resume – I saw you using it with a following ( Label ) Argument.. I had only used it as Resume or Resume Next ) Along these sort of lines..... So I see we have: Resume 'Will go back and try again ( For an error in a Call ed procedure, the procedure will be recalled from where previously Call ed ) Or Resume Next 'Will go to the Line after that where the error occurred . ( For an error in a Call ed procedure, the code will continue as if the Call ed procedure had been ignored ( skipped over ) ) Or Resume __Label_/-Line_number 'Will go where specified All the above 3 will either reset or probably rather leave the previous error handler enabled. I am not too sure on this. The corresponding Error Handling Statement which “ Resume s without going anywhere” is On Error GoTo -1. I believe this does not change the Previous error handler, but instead leaves it as it was, On. ( An Error arousal Erection Exception prevented it from working ). The difference is academic and probably no one knows for sure. Err Object Err.Raise _______ A last point to remind me on this. I believe VBA in its workings actually pseudo uses this after an error occurs. So if you yourself use it, then after this usage VBA does not know the difference. That is to say as far as VBA is occurred an exceptional error situation has been raised. We have an Erection. The minimum you need to give is a valid Error Number. The other registers are not cleared. If you had a previous error , before your use of Err.Raise , and you only give an Error Number, then the Last Description will be held still. If, before your use of Err.Raise with just an Error Number, the registers are empty ( either because the Err Object had not previously been used or you did a Err.Clear to clear the registers ), then in that case VBA will fill the registers to match the Error Number. You can give any the string registers any text. Alan

Similar Threads

[solved] i must get rid of on error resume next statement, [solved] need help avoiding goto statement, vba job resume statement (quick question), on error goto statement ignored, if and goto statement not working., if then - goto statement, goto statement in an if function, posting permissions.

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is Off
• HTML code is Off
• Trackbacks are Off
• Pingbacks are Off
• Refbacks are Off
• ExcelForum.com

On Error Resume Next: Handling Error in Excel VBA

Method 1 – On Error Resume Next’ Statement to Hide Worksheets in Excel VBA

This is a VBA code that will hide all the worksheets of your active workbook.

Look at the following screenshot:

Hide all of them using the following VBA code:

When you execute the following code, you will see this run-time error:

Excel shows this error because you can’t hide all the sheets in a workbook. Ignore this error. You must implement the On Error Resume Next statement in your line of code.

After completing the execution of the VBA code, you will see the following output:

After execution, you won’t see any errors. This On Error Resume Next statement worked fine in the VBA code.

Method 2 – Applying Excel VLOOKUP Function with ‘On Error Resume Next’ in VBA

This is an example of the VLOOKUP function in VBA . This VBA code also includes the On Error Resume Next statement.

Take a look at the following screenshot:

You can see some people’s names and ages. In the adjacent table, use the VLOOKUP to find the person’s name and age.

Type the following code to do this:

Run the macro. You will see the following error:

This is a run-time error.

There is no data for “Aaron” and “Emma”. That’s why it only executes the VLOOKUP for the first entry. It stops the execution. If you want to ignore the error and proceed to find the rest of the ages, use the On Error Resume Next statement.

After running the VBA code, you will see the following output:

By applying the On Error Resume Next command, we ignored the error and found the rest of the persons’ ages. The VBA code didn’t find any data on Aaron and Emma, so it ignored those values and returned the rest in the Excel worksheet.

Turn Off ‘On Error Resume Next’ with Excel VBA

You may be in a situation where you want to ignore errors for a particular segment of the VBA code. 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.

Generic usage:

Sub error_handling()

To ignore errors

On Error Resume Next

// lines of codes

To turn on Error handling

On Error GoTo 0

//lines of codes

Take a look at the following VBA code:

We used the code previously for the VLOOKUP function. Our code will ignore errors while performing the VLOOKUP but will activate error handling after the On Error GoTo 0 statement.

VBA ‘On Error GoTo’ Statement

Method 1 – vba on error goto 0.

The On Error GoTo 0 statement is Excel’s built-in setting if your codes don’t have an error handler . It basically implies that when VBA finds an error with On Error GoTo 0 , it will halt running the code and show its traditional error message box.

On Error GoTo 0 statement turns off the error handling in the present procedure. It doesn’t define line 0 as the beginning of the error-handling code, even if the method incorporates a line numbered 0.

Take a look at the following code:

This code hides all the worksheets in your current workbook. We have an extra piece of code with On Error GoTo 0 to show the error. If you run the code, you will see the following:

This error is caused by the fact that sheets with the same name cannot be in the active workbook.

Method 2 – VBA On Error GoTo line

You can also instruct Excel to run another code segment if it finds any error using the On Error GoTo line . It tells Excel to execute something after finding an error.

The line argument is any line tag or line number. If our code causes any run-time error, it will move to the line, causing the error handler to be active in the execution. Your defined line must follow the exact procedure for the On Error statement; otherwise, it will cause a compile error.

We used On Error GoTo 0 , which caused an error. We replaced that with the On Error GoTo line statement.

Run the code, and you will see the following:

It doesn’t show the standard error dialog box. Instead, it shows the custom message box we created in the error_handler segment. When Excel finds an error, it jumps to the  error_handler segment and shows us the message box.

We also used an Exit Sub in the procedure. If there is no ” VLOOKUP ” sheet, our VBA code will rename the active sheet. We must finish the execution here because we do not need to carry on to the error handler and display the message box.

VBA ‘On Error’ Not Working in Excel

• Press Alt+F11 on your keyboard to open the VBA editor.
• Click on Tools > Options.

• Click on the General tab in the Options  dialog box.

• When “ Break on All Errors ” is checked, it prevents you from handling the errors.
• To change it, select “ Break on Unhandled Errors ” and click OK .

I hope it will fix your problem of VBA “On Error” not working in Excel.

Things to Remember

✎ On Error Resume Next, it doesn’t fix the errors. It ignores the error and proceeds to the next statement.

✎ Excel traps and stores the run-time errors in the Err object. When we use the On Error Resume Next statement, it clears Err object properties.

✎ You can turn off the On Error Resume Next statement in your VBA code in Excel by adding the On Error GoTo 0 statement.

Download Practice Workbook

VBA On Error Resume Next.xlsm

Related Articles

• How to Solve Overflow Error in VBA
• Excel VBA Error Handling in Loop

<< Go Back To Errors in Excel | Learn Excel

What is ExcelDemy?

Tags: Errors in Excel

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... 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.

See Our Reviews at

• 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

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

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

VBA TUTORIAL LOGIN

Cond statements in vba test.

• If…Then…Else Statements
• Select…Case Statements

Text Strings in VBA

• VBA String Functions – Left, Right, Mid, Len, Replace, InStr & InStrRev
• VBA String Functions – Split, Join, Concatenate

Custom Number Formats

• Custom Number Formats in VBA
• Excel VBA Built-in Events
• Worksheet Change Event
• Worksheet Selection Change Event

ActiveX & Form Controls

• ActiveX & Form Controls, AutoShapes

Application.OnTime Method

• Application.OnTime – Schedule Macros
• MsgBox & InputBox in VBA
• Ebook of Excel Formulas
• Unique List, Sorted in Ascending Order, Appearing in One Column and Not in Other
• Count Rows containing Multiple specified Values
• SUM AND / OR Criteria for Multiple Columns
• Position of First Number in a String
• Extract Unique sub-strings of 1st 5 Characters from each column cell
• Count Maximum Number of Consecutive Occurrences of a Value
• SUMIF with Date Range, Multiple Month Names / Numbers & Year
• Extract Text After Last Occurrence of a Number in a String
• Extract Values which appear ONLY once, from a Column
• Excel Text and String Functions: TRIM & CLEAN
• ASCII Code, Extended ASCII characters (8-bit system) and ANSI Code.
• Excel CODE & CHAR Functions, VBA Asc & Chr Functions
• Excel Text and String Functions: LEFT, RIGHT, MID, LEN, FIND, SEARCH, REPLACE, SUBSTITUTE
• CHOOSE Function in Excel
• Excel IF Function and IF Statements
• Excel VLOOKUP Function
• Excel OFFSET Function
• Excel INDIRECT Function
• Excel SUMPRODUCT Function
• Printing a Pivot Table Report – Excel
• Create a Pivot Chart in Excel – graphical display of a Pivot Table
• Excel Pivot Table Design & Layout, Pivot Table Styles
• Excel Pivot Table Report – Sort Data in Row & Column Labels & in Values Area, use Custom Lists
• Excel Pivot Tables: Filter Data, Items, Values & Dates
• Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas
• Excel Pivot Table Report – Summary Functions & Custom Calculations, Insert Calculated Fields or Calculated Items
• Excel Pivot Table Report – Group Items, Group Date and Time Values
• Excel Pivot Table Report – Field Settings, Expand or Collapse Fields & Items, Refresh Data, Change Data Source & Show or Hide options
• Excel Pivot Table Report – Clear All, Remove Filters, Select Mutliple Cells or Items, Move a Pivot Table
• Case Sensitive Vlookup in Excel; Finding the 1st, 2nd, nth or last occurrence of the Lookup Value
• Vlookup Multiple Values – Return MULTIPLE corresponding values for ONE Lookup Value
• Excel Date and Time Functions and Formulas
• Find ‘Smallest’, ‘Largest’, ‘K-th Smallest’ and ‘K-th Largest’ Numbers in a Range
• Remove Duplicates in a range, using “Remove duplicates” button in Data Tools
• Remove Duplicates or Create a List of Unique Records using Excel Formula
• Remove Duplicates or Create a List of Unique Records using Advanced Data Filter
• Count Number of Unique Values in a Range (with Excel Functions)
• Shade Alternate Rows – Conditional Formatting
• Left Lookup with Vlookup Excel Function
• Excel VBA: ActiveX Controls, Form Controls & AutoShapes on a Worksheet
• ChartFormat object – line, fill & effect formatting for chart elements: FillFormat object, LineFormat object, ShadowFormat object, GlowFormat object, SoftEdgeFormat object, ThreeDFormat object
• Child Objects common for many chart elements: Border Object, ChartFillFormat Object, Interior Object, Font Object
• Chart Elements in Excel VBA (Part 2) – Chart Series, Data Labels, Chart Legend
• Chart Elements in Excel VBA (Part 1) – Chart Title, Chart Area, Plot Area, Chart Axes
• Charts in Excel VBA – Add a Chart, the Chart object & the ChartObject object
• Create Charts in Excel VBA: Embedded Charts – Line with Markers; Clustered Stacked Column Chart; Clustered Stacked Bar Chart displaying Variance; Pie chart; XY Scatter chart & Bubble chart
• Excel VBA Debugging Tools in Visual Basic Editor – Breakpoints & Break Mode, Stepping Through Code, Debugging Views
• Excel VBA Date & Time Functions; Year, Month, Week & Day Functions
• Website Use Policy
• Excel VBA Online Tutorial
• Testimonials GlobaliConnect.com
• About ExcelAnytime.com
• Excel VBA Online Tutorial – learn Excel VBA programming & access examples, illustrations, live codes and downloadable files
• Excel and VBA Services
• Getting Started
• Article Categories Module
• Search Module
• Authentication
• Articles Category Module
• Statistics Module
• Happy Orange Orchard
• Wonderful Watermelon
• Banner Module
• Professionals
• Editors-xtd
• Custom HTML Module
• Sample Sites
• Feed Display
• The Joomla! Community
• Menu Module
• Related Items Module
• Breadcrumbs Module
• Login Module
• Who’s Online
• Administrator Components
• Most Read Content
• Wrapper Module
• Latest Users Module
• Archive Module
• Latest Articles Module
• Random Image Module
• Weblinks Module
• Footer Module
• Language Switcher
• Syndicate Module
• Phyllopteryx
• Spotted Quoll
• Cradle Mountain
• Blue Mountain Rain Forest
• Ormiston Pound
• Australian Parks
• First Blog Post
• Second Blog Post
• VBATutorial - Cond Stmnts

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

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

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:

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.

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

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

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.

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

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

‘add a new worksheet at the end

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

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

‘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

‘if worksheet name already exists, enter a new name

GoTo WsName

MsgBox “Exiting Procedure without changing Worksheet Name”

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

MsgBox Err.Number

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

strNewName = InputBox(“ Enter Worksheet Name “)

MsgBox “You have pressed Cancel, Exiting Procedure”

GoTo exit_proc

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

‘exit routine to skip error handler

ErrHandler:

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

MsgBox “Name already exists”

‘resumes execution at this point (WsName)

Resume WsName

‘resumes execution at exit routine and exits sub

Resume exit_proc

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.

‘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 “)

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 & “%”

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

‘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

‘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

‘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

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

Resume Next

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

‘call another procedure

Call Called_Proc

exit_routine:

‘error handling routine of the calling procedure

‘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

MsgBox “Unresolved Error, Exiting Procedure”

Resume exit_routine

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

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

MsgBox “File Copied”

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

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

intOrigErrNum = Err.Number

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

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)

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)

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

Dim i As Integer

i = 100 * 20000

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 “

‘macro executes if no error

MsgBox “Name: ” & strName

     ‘if no error, display message is “Program Executed”

MsgBox “Program Executed”

CustomError_End:

     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

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.

Excel Top Contributors: HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Riny_van_Eekelen  ✅

December 13, 2024

Excel Top Contributors:

HansV MVP  -  Andreas Killer  -  Ashish Mathur  -  Jim_ Gordon  -  Riny_van_Eekelen  ✅

• Search the community and support articles
• Microsoft 365 and Office
• Search Community member

Ask a new question

ON BLANK (or EMPTY) RESUME NEXT

I have many tools that take data from an excel spreadsheet and write the values to SAP so that we can automate creation of master data... I don't want to write literally 100's of if functions with 100's of label statements... i.e. if cell(x,y).value = "" then goto line 2 etc...

VBA has an "on error resume next" function and I was wondering if one could create a "On Blank Resume next" function... my vba / programming is nowhere good enough to create one myself so need some help please

[Moved from: Office / Excel / Windows 10 / Office 2013]

Report abuse

Reported content has been submitted​

• Volunteer Moderator |
• Article Author

Use a subroutine like this:

Sub FieldToSAP(ByVal From As Range, ByVal FieldName As String)   If IsEmpty(From) Then Exit Sub   SAP.Field(FieldName) = From.Value End Sub

And if you have many non-contiguous cells something like this:

Sub Main()   Dim C As New Collection   Dim Item     C.Add Array(Range("F5"), "Surname")   C.Add Array(Range("H5"), "Forename")   C.Add Array(Range("F6"), "Street")     For Each Item In C     FieldToSAP Item(0), Item(1)   Next End Sub

1 person found this reply helpful

Was this reply helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

Thanks so much for your help... I resolved it

I'm pleased to hear that it works.

A few notes to optimize your code:

a) Please never use SELECT, SELECTION, ACTIVECELL, it is slow and error prone. Always refer to the objects directly.

b) You don't need to cache the items in a collection if you loop though that list. c) If you span a range from A2 to the last cell, search from the bottom upwards.   Otherwise you miss some data if there is a gap. d) If you refer to a sheet, use the Name, not the Codename.

  Because if the user copy a sheet, rename the old to "backup" and the new to the old name, your code use the Backup sheet.

Option Explicit Sub WriteToSAP(ByVal From As Range, ByVal FieldName As String)   If IsEmpty(From) Then Exit Sub   Debug.Print From.Value    'reference SAP.Field(FieldName) = From.Value End Sub Sub Main()   Dim r As Range   With Sheets("Sheet1")     For Each r In .Range("A2", .Range("A" & Rows.Count).End(xlUp))       WriteToSAP r, "Name"       WriteToSAP r.Offset(, 1), "Surname"       WriteToSAP r.Offset(, 2), "Nickname"     Next   End With End Sub

Replies (7) 

Question info.

• Norsk Bokmål
• Ελληνικά
• Русский
• עברית
• العربية
• ไทย
• 한국어
• 中文(简体)
• 中文(繁體)
• 日本語