Phantom Breakpoints Driving me insane!

[fred3]

This makes no sense to me:
I am running a vba-based program to update a number of files.
The code seems pretty simple, if not done with best practices .. as I got code suggestions from a number of places.

Anyway, it was all working fine!!!!!
So, I assume that the code was pretty much OK, eh?
Then it would stop working.
So then I would make changes to try to fix it.
So, I can't say that I've made NO changes but no changes that I'd expect to have such drastic outcomes.
And then only to fix the disruptions.

As an outline:
There is a summary file that has a list of all the data files.
The objective is for the summary file to call the data files in sequence, have them get their respective updates and close, returning to the control of the summary file vba.

Right now the outcome is that the summary file stops (with "Code execution has been interruped: Continue/End/Debug/Help")
at an End If statement but will continue with either F8 or Continue.

Then it stops with the same interruption again at the beginning of the next called workbook Private Sub Workbook_Open() statement.

Selecting continue bounces between these two interrupts.
Using CTRL-SHIFT-F9 seems to do nothing.

This is very bad because I can't get code to just execute normally - except perhaps by accident. Then, again by apparent accident it stops working again with non-predicatble results.

I have no idea how to proceed at this point.

[Trebor76]

Hi fred3,

Put this line of code...

Please Login or Register  to view this content.

...at the very start of your procedure.

See here for more info.

HTH

Robert

[Andy Pope]

Try cleaning your vba project.
http://www.appspro.com/Utilities/CodeCleaner.htm

[TMS]

If you are opening macro enabled workbooks with Workbook Open events, the events will fire when you open the workbook ... and, quite possibly, cause some confusion depending on what they are designed to do.

You should use:

Please Login or Register  to view this content.

Regards, TMS

[fred3]

All sound like good advice. Thanks! I'd like to understand them better...

Trebor76: The link also mentioned using CTRL-BREAK when there was nothing running. That appears to not help. I've not added that code yet .. something about disabling debugging....

Andy Pope: I've used the Cleaner but it has never had an effect on the phantom breakpoints.

TMS: As, I believe you can see, I *am* using workbook open events if I understand the terminology. That is, there is a Private Sub Workbook Open() in each of the workbook files that are opened by the Summary workbook. I'm not clear on what "might be confusing". Well, *I'm* not confused about the objectives, just about what the code is doing when it fails. I've written enough code to know that erratic behavior is unusual - just not vba code. I do want to get rid of the erratic behavior obviously but the architecture depends on the Private Sub Workbook Open() in the data workbooks. I guess I could change that but...
The idea is:
- when a data workbook is opened, it will automatically update the data (with a web download). Otherwise, this has to be done manually which is to be avoided.
- depending on control data linked in from the Summary workbook, the file will close thereafter or stay open. In the case of updating an entire list, it's important that this be set to "close thereafter" which is what's done. (The process of open, update, close seems to be working fine and DOES work fine when it works. The problem, and only problem with this right now - which may become different later today or this week depending on the phase of the moon - is that the process stops at the Private Sub Workbook Open() line. The other problem is another phantom breakpoint in the Summary workbook at an End If statement.
The sense I get from your suggestion is that the Private Sub Workbook Open() routine would not run if I add the suggested code. Is that right?
What does that suggested code do? Where would one look it up?

Well, I looked it up in Excel Power Programming with Excel 2013 but I wasn't able to comprehend it all.
So, I just tried it and it appears to work so this is indeed progress. Now if I just knew what I had just done....

[TMS]

The sense I get from your suggestion is that the Private Sub Workbook Open() routine would not run if I add the suggested code. Is that right?

Yes.

Where would you look it up?

In the VBA Help Files. Type Application. and select EnableEvents from the drop down list. Then press F1 for Help on the property.

Excel Developer Reference

Application.EnableEvents Property

True if events are enabled for the specified object. Read/write Boolean.

Syntax

expression.EnableEvents

expression A variable that represents an Application object.

Example

This example disables events before a file is saved so that the BeforeSave event doesn’t occur.

Visual Basic for Applications

Please Login or Register  to view this content.

Application.EnableEvents = False prevents ANY events being processed until it is reactivated with Application.EnableEvents = True.

So, for example, if you have a Worksheet Change event and you need to make changes to the worksheet within the Change event handler, you would switch it off before making the changes. This would avoid the event handler looping.

In this case, it would prevent the Workbook Open event from firing when you open each workbook. That, however, may mean that the data is not as up to date as you would like it to be. It will NOT update the data with a web download, etc.

The implication is that the issue is timing related. Some workbooks may not have completed the download and update process before you move onto the next workbook ... is my guess.

Just because code works in stand alone mode in a safe environment and/or when you step through it, it does not necessarily imply it will work "under pressure". Downloads can sometimes be very quick and sometimes be very slow, depending on a number of factors: time of day, volume of data, integrity of the network connection, and so on.

Well, I looked it up in Excel Power Programming with Excel 2013 but I wasn't able to comprehend it all.

As snb says, you shouldn't use (or depend on) code you don't understand.

So, I just tried it and it appears to work so this is indeed progress.

Good

Now if I just knew what I had just done....

I have explained what you have just done and the implications of doing it.


Regards, TMS

[fred3]

OK. Thanks.
Well, I thought it was working in that the data workbooks were opening and closing OK.
But as you have very nicely described, the update process won't run this way.
(I was initially concerned about them overlapping in opening but this seems to not be the case as the loop in the Summary workbook ideally wouldn't open one until the previous one / task has completed .. I think. These things take long enough to update (seconds) that this seems confirmed - if they were going to overlap it seems that I'd seen that happen by now; unless it's invisible somehow. A "good run" will run through the workbooks getting new data at an interval of 2-3 seconds each - which from a loop execution point of view is a very long time. Still, a very good point.)

What does it mean to "understand code" really? Be able to write assembler from it? There are limits and those are in the eyes of the beholder. Who is "snb". My approach has always been to read as much is possible (perforce limited) and seek advice and dig deeper when necessary. Like now.. That said, I do *like* to understand it all but if it works then "better is the enemy of good enough".

Anyway, the data updates didn't happen and that's the whole purpose. So this may be a step backward or at least sideways. The phantom breakpoints at the End If in the Summary workbook no longer happens but the needed Open sub doesn't run either.

Thanks!

[TMS]

What does it mean to "understand code" really?

I guess the thing is, I didn't know what was a necessary requirement of your process: that is, open, download, update, close. Now I do, and consequently, I now understand that the advice I gave you is not appropriate. You, on the other hand, didn't know what the code did and implemented it, not knowing the impact it would have on your process. So, now you understand both what the code does and also the effect it has. Good news and bad news. Bad news being that though the code runs through end to end, it doesn't do all the things it needs to do.

Two options spring to mind. The first is to put some sort of delay loop into the Summary workbook code to allow the individual workbooks time to finish, even in the worst case. The other is to create some inter-workbook communication. The way I have done this in the past is to create a "flag" passed between the two workbooks. This, typically, takes the form of a text file (content not important). If the flag exists, you do one thing, if it doesn't, you do something else.

In this case, the Summary workbook could test for the presence of the flag (text file) and, if it exists, delete it (or, knowing that you want to delete it anyway, just try to delete it and ignore the error if it doesn't). Then open the workbook which, when it has completed its download and update, would create the flag (text file) and close down as normal. The Summary workbook would wait (loop) until the flag was detected, delete it and carry on with the next file. The inter-workbook communication should allow the two processes to complete without error ... in theory.

Regards, TMS

[fred3]

TMS: Thank you!
That seems clearly best to avoid any question of what's going on or could happen.
In theory, I believe all of the data files could be opened at the same time or not closed at all but the computer can't handle that. So a little overlap seems like it might be OK. Maybe that's what was "working" before.
But then, when I run Solver in the data workbooks it seems pretty clear that they don't overlap as the Solver iterations can take 2-10 minutes each. Maybe that works because of some effect that Solver has - as in it will only run one instance (?).
Using the flags will assure how things line up in time.

Would you recommend the flags be set in cell contents or as vba variables? Either way, I'm going to have to figure out exactly what to do but that's fine. I've set up such controls in other language/machine contexts.