Here is the Hello, world! of VBA error handling:
Sub MyRoutine() On Error GoTo Err_MyRoutine '...some code... Exit_MyRoutine: Exit Sub Err_MyRoutine: 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:
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 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:
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
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.