Why does my VBA procedure sometimes stop in debug mode?

Question

I have a button click event in an Access form that sometimes opens the VBA editor with the 'On Error...' line highlighted as if it is in debug mode. I can F5 to continue the rest of the procedure and it works fine.

It doesn't happen everytime. It seems random except there seems to be a pattern that it happens on the first click of this button right after the file is opened. Not everytime though.

Any thoughts on this or previous experience with the same thing happening and subsequent solution? What might be causing this? It's a terrible user experience.

Did you have a breakpoint exactly there at one point? Try exporting and re-importing all the modules - sometimes breakpoints in the VBE get "ghosted" and they don't show up but the IDE still breaks at that position, kind of like if something is desynchronized between the editor and whatever data structure stores breakpoint locations. Exporting and re-importing the modules forces the editor to drop whatever half-compiled corrupted cache it had, and then everything should run smoothly again after. — Mathieu Guindon
As to what might be causing this, I'd say it's probably some obscure bug in the edit-and-continue debugger feature. — Mathieu Guindon
@MathieuGuindon - It sounds like that may be what is happening because I did have breakpoints in this module while developing and testing it over the past couple of days. I exported the class module for the form, but I can't seem to remove the module before I re-import. — Todd Brannon
If it doesn't do it, I can confirm that the highly-upvoted accepted answer in the post I linked did fix this annoyance multiple times over the years for me (and visibly, for others too!) — Mathieu Guindon

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2020-09-24T00:55:49

Well, before running any code (hold down shift key during startup to prevent any code from running).

Now, ctrl-g (jump to VBA IDE). Now from tools. Choose debug->Clear all Breakpoints

Like this:

Now, open up any code module - hit enter key to "dirty" the code. Now choose debug->Compile (first menu option). It will say Compile "my app name".

Make sure the code compiles. If it does not, then stray break points can still exist.

Next up, you need to check/change the default behavior for a error.

While STILL in VBA editor/IDE

From menu bar choose tools->options. The default is "Break on Unhandled errors"

If you have break on ALL Errors? Well then code that even assumed to trap or even on-error resume next code it BLOW UP and stop. Often developers will say try for existence in a collection, and we error tap to "mean" the element is not in that list. However, the THIS assumes that the default Error trapping setting was not change.

So, double, and then triple check this setting. You can develop for years, and even have some code ASSUME to error out. But that years of development code assumed the default (break on unhandled Errors. If you have break on all errors, then your are toast, and you find all kinds of breaking of code. (the idea of that option is to LET you debug code with error handling without having to disable errors. And with say on-error resume next, you in effect can't debug parts of code anymore.

Now, if above steps don't fix your issues?

Then the next step is to de-compile your application. This will remove the compiled (binary) part of the application. Once you do this, then you do a full re-compile.

To de-compile, you can't do this from the IDE, and you have to use a FULL qualified path to your existing version of access. Say like this:

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE"
"c:\MyCoolApp\Invoice.accDB" /decompile

Now, when you run above, you REALLY must not let any startup forms or code run. (hold down shift key. Now exit access/application. Now re-launch (and again no code to run on startup).

Now, at this point I high recommend a Compact+ Repair (and AGAIN no startup or code to run). So even on the C+R, you have to hold down shift key.

If you during the decomp, start application, then C+R allow ANY code to run, then you have to start over again at the first decompile step.

Ok, now you done the C+R. Now ctrl-g, and now debug-compile.

Why does my VBA procedure sometimes stop in debug mode?

1 Answers