VBA terminating with extreme prejudice in a userform

[Johnny C]

I've got some code that runs across a large number of files on a regular basis. As this can take several hours to run, it happens occasionally that some numpty deletes one of the files after the code has prepared the list of files to run on.

Despite having an error trap, when this happens, as soon as the code tries to interrogate the sheet it couldn't open, the code just terminates, the progress userform disappears, the spreadsheet shuts down and in some cases it clears all values/formats from one of the key sheets in the workbook with the code in.

Now that I've figured out it's the missing file I've captured that event, ditto when they delete a folder.

But there are all sorts of other things I haven't thought of no doubt. I can't cater for every potential case of user imbecility.

I'll be gone by the time this workbook gets used as a BAU process. I'm handing it over to a perm member of staff, but it's just shy of 1,000 lines long and if it goes wrong it'll be during a time-critical part of the month, whoever will have to fix the error will be getting flak from all sides to fix it quick. I want to be able to trap enough data so whoever debugs it can do so without having to step through 2,000 files of upto 50,000 lines long.

I've got plenty of debug.print statements to track progress but the immediate window is limited in size, and when the workbook does terminate with extreme prejudice the contents of the immediate window disappear anyway.

So, is there a reason it's terminating with extreme prejudice. If so, can I stop it? I don't know why it's ignoring the error trap, why it's closing the workbook or why it's deleting the contents worksheet of reference data. is it just a bug?

I occasionally get a message on opening that some data is unrecovverable, but this file has no external links and when i recover te file nothing has gone. Could it be some corruption that's causing the termination?

As a quick measure I'm going to recreate the workbook from scratch and copy everything over, but I don't know if this will fix the problem.

 

[Johnny C]

bump

 

[Johnny C]

bump?

 

[RoryA]

Almost impossible to say with that little to go on, but it's not a bug I've heard of. If you have proper error handling in place, it shouldn't happen but there are always quirks (such as if you run your code from a keyboard shortcut involving the Shift key). I would suggest you add a logging routine to your code so that it writes its progress out to text file at each significant step. At least then you may be able to narrow down where it abends.

 

[Johnny C]

Cheers Rory

It runs from a button, so Shift/KB is not an issue

I'll try writing out to a text file, problem is if it does bomb out quickly the file won't be closed so I'll have to open and add and close each time I write.

Thanks for the quick reply!

 

[RoryA]

I'll try writing out to a text file, problem is if it does bomb out quickly the file won't be closed so I'll have to open and add and close each time I write.

Yup - that's the best way. It's quick and easy using File I/O.

 

[Norie]

Johnny C

What does the code actually do?

You mention it involves files, and folders, that could possibly be deleted?

Do you know that these files are and where they are located before the code is run?

Have you considered copying all the files you need to some sort of central location?

Then the code could be run on the copies and not the 'real' files.

No idea if that's viable at all, just an idea.