How do I DEBUG when I have used error handling method from "Professional Excel Development" book?

Question

I have implemented the error handling methods from "Professional Excel Development" by Rob Bovey and Stephen Bullen in my Excel VBA project.

I would like to know how to run in DEBUG mode so that I can find the exact location of the error since the output error log only shows me the function where the error occurred, not the line.

P.S. I have answered my own question below, since I figured it out during the course of running my code. Please see their excellent book for the full method.

Just curious why a -1? It took me quite a while to figure out the flow of this method and I am trying to save others who use that book time. — Shari W, Sep 15 '14 at 18:33
For anyone visiting this question, Shari posts most of the relevant code in a previous question [here](http://stackoverflow.com/questions/19042604/vba-excel-error-handling-especially-in-functions-professional-excel-developm). — Blackhawk, Sep 15 '14 at 19:58
The reason you might get a downvote for this question is that it doesn't provide nearly enough information for someone to answer it. The question would benefit substantially from being self-contained. I'm hoping the link clears that up for anyone browsing, but you might consider adding the code or at least including the link to it in this question. It would also help anyone searching for this same information. — Blackhawk, Sep 15 '14 at 20:03
Thanks Blackhawk. I was trying to err on the side of not quoting too much of the book, but I got permission from the authors to post what I did in that link above, and they even helped me by email with the answer! Wow. — Shari W, Sep 16 '14 at 14:29

Shari W · Accepted Answer · 2014-09-15T18:06:06.483

-- HOW TO RUN IN DEBUG MODE --

-- This code is based on the method from book "Professional Excel Development" by Rob Bovey and Stephen Bullen, but the book isn't detailed about how to debug.

-- This answer doesn't show all error handling code, only how to use what you have implemented from the book in debug mode.

-- Your error stack will appear in a log file called Error.log in the same directory as your program. Scroll to the bottom to find the name of the function where the error occurred (the first function listed at run time.) This might get you close enough to find the error.

--To find the exact point of the error using this error handling system, you must run in debug mode.

Go to module M_ErrorHandler which contains the error handling functionality from this book. You might have named your module differently.

Change

Public Const gbDEBUG_MODE As Boolean = False

to

Public Const gbDEBUG_MODE As Boolean = True

Run the program.

When an error occurs, the execution will stop in that function at the Stop line under ErrorHandler.

ErrorExit:

On Error Resume Next
-- Include cleanup code here
bBoldLateArrivals = bReturn
Exit Function

ErrorHandler:

bReturn = False
If bCentralErrorHandler(msMODULE, sSOURCE) Then
    Stop                     -- *** STOPS HERE ***
    Resume
Else
    Resume ErrorExit
End If

End Function

In debugger, step through the code using F8. The "Resume" line will execute next. The next line you jump to will be the exact line that caused the error. Note the location of the error and determine how to fix it.
Press F5 to run the bad line and raise the error again, which will take you to STOP again.
Unfortunately, you can't just turn off Debug and continue from this point. You now have two choices: a) Just quit the program in any state (stop execution) or b) Finish carefully to protect your data and worksheets.

To continue with full error cleanup and prevent problems in the worksheets, you must resume execution from the ErrorExit label of each function in the stack. To do this, scroll up a few lines and click on the line "On Error Resume Next" to place the cursor. In your debug menu, choose "Debug...Set Next Statement" or use Ctrl-F9.

  ErrorExit:
      On Error Resume Next    -- *** PUT CURSOR ON THIS LINE **
      -- All the good cleanup lines you have here will run

Press F5 to continue running. You will stop on the Stop line in the next function up the stack. Click again at "On Error Resume Next" in that function and use Ctrl-F9 then F5 again. Repeat until you have reached the top of the program.

Fix the error.
Set DEBUG mode to false and save.

Public Const gbDEBUG_MODE As Boolean = False

How do I DEBUG when I have used error handling method from "Professional Excel Development" book?

1 Answers1