dimanche 22 novembre 2015

Causes for random break points unknown

This has been a long and unresolved problem with Excel VBA code that runs a repeated loop via the Application.OnTime Now + TimeSerial(x,x,x). Users will find that their code is running fine for a few days and may, or may not, mysteriously stop.

I'm facing such a situation and hoping to resolve it. I've read over 30 forum answers, some written by experienced developers, and came to this understanding. My conclusion and question follow.

The conclusion is that Excel randomly goes into a break mode but no one knows the reason why this occurs, more so, why it randomly occurs.

Shall we then conclude that in fact this randomly occurs? And that Excel VBA isn't as robust as other languages.

Some notes:

  1. I know this can be resolved by pressing Ctrl+Break twice. It doesn't explain why we have to do it in the first place.
  2. I realize that this error is code independent. It'll occur with both simple and involved programs.
  3. After trying multiple ways to simulate this error, and I mean a lot - long and multiple ADODB SQL connections and queries, cell editing while macro is running, using multiple and 1 second Application.OnTime recursive calling, I can't replicate the error. It is truly random.
  4. I'm running only one workbook and one Excel instance.
  5. Some say, for each break point we did in the debugging, that break point remains in memory. And then when we run a macro in the future, a write to that part of memory triggers this random break. This is a plausible explanation and does conclude that this error is random. No way can we inspect memory in VBA.

I need something to go by, even if it isn't to solve this problem, to act as proof for my boss.




Aucun commentaire:

Enregistrer un commentaire