Fix Runtime Error in VBA




To fix runtime errors is VBA, use the following three statements – On Error, Resume and Debug. The first one traps a runtime VBA error when it occurs, the second one tells the application the point from which to resume code execution when the error has been caught (if you decide to catch the error) and the third one informs the user where (and which) runtime error took place.

A runtime Error in VBA can occur for a plethora of reasons. The most frequent ones occur when one tries to execute the wrong operations. These can be trying to access a non-existent object, property or a function. The application in these cases will throw the typical “Invalid procedure call or argument” (run-time error 5) or the “Object doesn’t support this property or method” (run-time error 438) errors. Another set of frequent errors ones by what what I call “overdoing” – carrying out an iteration beyond the outermost bound of an array of objects. This happens when we try accessing a row number which is greater than the total number of rows for that range (“Application-define or object-defined error” run-time error 1004) or say when we force an object well beyond its capacity (“Overflow” run-time error 6).

vba-runtime-error-fix-dilbert

Here are a few examples of statements that will cause a runtime error to occur in VBA:

runtime-error-438-in-vba

Once a unhandled runtime error occurs, a VBA program stops executing and hence it is critical for any dashboard or application which uses VBA code to have error handling abilities.

Catch the error when it occurs in VBA

The primary means of catching an error when it occurs in VBA is to simply put the “On Error do_something” statement before the piece of code that can generate an error. The On Error statement has two forms – On Error Resume Next and On Error GoTo Label.

On Error Resume Next

When you place an On Error Resume Next statement in your VBA code, it will ensure that whenever an error occurs, code execution will resume from the line immediately following the line that generated the error. In the code shown below, an error occurs when we try to divide 1 by 0. However due to error handling and the resume next statement, code execution resumes from the next line (which is i = 1/5). The output in this case is 0.2

On Error GoTo Label

The second form of the error handling is the “On Error Goto Label” statement. When an error takes, this statement causes the code execution to skip all lines and move directly to the place marked with the label:and to resume execution from there. In the following example, we have inserted a label called “error_occurred:” at the bottom of the code. Now when the error occurs, code execution skips all lines in between the line where the error occurred and the said label. Code execution then resumes from the line immediately following the label. The output in this case is 0 (unlike 0.2 that we had in the previous case).

Resume Statement in VBA

Once an error has been trapped using the On Error statement, we saw that we can instruct the code execution to resume either from the line immediately following the error causing line (On Error Resume next) or from a specifically given point marked with a label (On Error Resume Label:).

In both the above cases, the exact line or step that caused the error is skipped. However if that step is critical and has an impact on subsequent lines of code, you may want to catch the error, rectify the error and then resume from the same line again. The first time the following code executes, the variable mynum is assigned a value of 0 and therefore causes an error to occur. The code catches the error, assigns a new value to the variable and then re-tries the same step again (by going back and resuming code execution from the line that caused the error).

Debug and alerts in VBA

Knowing where the error occurred and what caused it are an important part of handling the error. In many cases, by simply rectifying the part of the code that generated the error we can eliminate the need for error handling (or atleast reduce the dependence on it to a great extent). In the code given below, we trap and error and then generate an error message which let us know what specifically caused the error. In this case, the error description will read “Division by zero”.

You would have also noticed the use of the Debug print statement. The debug statement tells the application to print the given string (in our case the error message to the “Immediate Window”. You can turn on the Debug (or the Immediate Window) by clicking ‘View’ -> ‘Immediate Window’ from the menu or simply pressing Ctrl+G. The difference between having a message box and a debug is that the message box will appear when an error occurs and until the user specifically clicks on the message, code execution will remain halted. The debug on the other hand will simple keep on showing the error messages in the Debug window which the user can then read through once the code has finished executing.

If a subsequent error has not occurred, how do I prevent resume of code execution after the label :

Assume you have a piece of VBA code where you trap an error, set the code execution to resume from a specific label, rectify the variable causing the error and then resume back from the same line (or the next line) from where the error originally occurred. Here is an example:

What do you think is the final output in this case. 1 or 5 ?.

Answer – none of the above. The code executes indefinitely. Why? Because when we catch the error and reset the mynum variable, we instruct the code to resume again from the line that caused the error. The application faithfully goes back and executes the same code once again and then….well it comes back and hit the “resume” instruction. The resume point in this case is still the one where it went the previous time.

And then it goes back and executes the loop again. However, if we had simply placed one additional line of code which checks if the an error occurred in that specific iteration, that would have prevented the code from looping indefinitely. So in our code if we simply replace the code below…

…with the following…

This ensures that once the we have a fix on the runtime error, the VBA code does not execute the error handling statement (and resume) again.


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. Excel Macro | da TaB is On wrote:

    […] prevents the application from crashing when an unexpected event occurs. Please read more about fixing runtime errors in VBA […]

  2. Prevent Users Ctrl Break (ing) Your VBA Code During Execution | Excel & VBA - da Tab Is On wrote:

    […] The statement instructs Excel to not show the “Code execution has been interrupted” message and provides a way for the developer to tap into the ctrl + break action by the user. Essentially there can be three values for Application.EnableCancelKey : xlDisabled, xlErrorHandler and xlInterrupt. By setting Application.EnableCancelKey = xlDisabled, we are essentially telling the application to stop responding to the ctrl + break command from the user. If the code runs haywire … too bad. The xlInterrupt is the normal course of action where the user can terminate the code and is the value that the application resets to after the code has run its course. The xlErrorHandler is the one that lets the developer instruct the application generate an error (code 18) and then to tap into that error by using error handling. […]

  3. Fix Vba Stop Error Message Errors - Windows XP, Vista & Windows 7, 8 wrote:

    […] Fix Runtime Error in VBA | Excel & VBA – Databison – Prevent Users Ctrl Break (ing) Your VBA Code During Execution; Table Formula in Excel (Something I didn’t Know Till Yesterday) Multithreaded VBA – An Approach To …… […]

  4. Fix Excel Vba Stop Error Messages Errors - Windows XP, Vista & Windows 7, 8 wrote:

    […] Fix Runtime Error in VBA | Excel & VBA – Databison – […] prevents the application from crashing when an unexpected event occurs. Please read more about fixing runtime errors in VBA […]… […]

  5. How To Fix Excel Vba Runtime Error 6 Overflow Errors - Windows Vista, Windows 7 & 8 wrote:

    […] Fix Runtime Error in VBA | Excel & VBA – Databison – […] prevents the application from crashing when an unexpected event occurs. Please read more about fixing runtime errors in VBA […]… […]

  6. How To Fix Excel Runtime Error Errors - Windows Vista, Windows 7 & 8 wrote:

    […] Fix Runtime Error in VBA | Excel & VBA – Databison – Prevent Users Ctrl Break (ing) Your VBA Code During Execution; Table Formula in Excel (Something I didn’t Know Till Yesterday) Multithreaded VBA – An Approach To …… […]

  7. Fix Excel Vba Error 40040 wrote:

    […] Fix Runtime Error in VBA | Excel & VBA – … – Prevent Users Ctrl Break (ing) Your VBA Code During Execution; Table Formula in Excel (Something I didn’t Know Till Yesterday) Multithreaded VBA – An …… […]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES