VBA terminating with extreme prejudice in a userform

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Nov 7, 2006
Messages
1,003

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,834
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,487
Messages
5,529,162
Members
409,851
Latest member
Ingar
Top