The Extra Resume

Want an easy way to jump to the line that raised the error AND avoid the infinite loop time bomb? Add an extra Resume.

The Extra Resume

Here is the Hello, world! of VBA error handling:

Sub MyRoutine()
    On Error GoTo Err_MyRoutine

    '...some code...

    Exit Sub
    MsgBox Err.Description
    Resume Exit_MyRoutine
End Sub

In the above routine, if '...some code... raises an error, then program execution will jump to the Err_MyRoutine label.  The program will then display a message box with a description of the error.  At this point, you as the developer will probably press [Ctrl] + [Break] then click [Debug] to jump into the code:

Of course, this doesn't tell you which line actually raised the error.  To return to the erroring line, you can call Resume (with no line label) or Resume 0 to jump back to the line that raised the error.

The "temporary" solution

When I first started developing in VBA, I would simply comment out the line label then press [F8] to return to the erroring line:

I'll just throw this single quote in here while I'm debugging. There's no way I'll forget to remove it when I'm done.

The problem with that approach is that I had just introduced a potential infinite loop into my code.  If I forget to remove the "temporary" single quote then the change will make its way into production.  When an end user runs the code, the erroring line raises an error, the program shows the user a description of the error, then the program jumps back to the erroring line...which raises the same error, etc.

The Infinite Loop Time Bomb

The problem with all temporary code

Milton Friedman famously said, "Nothing is so permanent as a temporary government program."  Since that time, developers the world over have apparently interpreted that quote not as an indictment of public policymaking, but rather as a challenge.  "I see your temporary government program," they say, "and I raise you this temporary bit of code.  We'll see whose temporary concept is truly the most permanent."

Finding the temporary single quote

Like all other "temporary" code, this temporary comment was easy to forget about.

To manage the problem, I would search my code for instances of Resume ' before releasing my code into production (if I remembered). But what if there was an extra space between the keyword "Resume" and the single quote?  That instance would not show up in my search.

To deal with that possibility I made it a point to always use the optional zero for my temporary debugging Resume lines: Resume 0. I did that because the VBA IDE would force exactly one space between the "Resume" keyword and the number zero.  Thus, I could reliably search for Resume 0 and find every one of my infinite loop time bombs.

A Better Way

For years, this was my solution.  And then one day I was watching a video with Armen Stein from J Street Technology.  I don't even remember what the video was about.  All I remember was his offhanded remark about the "extra Resume" they incorporate into their boilerplate error handling.

Here's what it looks like:

Place cursor on the blue highlighted Resume line and press [Ctrl] + [F9] to "Set Next Statement"

The solution is brilliant for a couple of important reasons:

  • There's no code to change when debugging*
  • It completely avoids the possibility of an infinite loop

* (Is this a good thing, though?)

Video Demonstration

After I originally published this article, Armen wrote in to let me know he has a 90-second video that demonstrates this technique.  Thanks, Armen!

UPDATE (3/1/2021): Added link to YouTube video from Armen Stein demonstrating the concept.

UPDATE (3/2/2021): Added link to an article that addresses the question of whether it's a good thing to add the extra Resume before you need it.

All original code samples by Mike Wolfe are licensed under CC BY 4.0