VBA terminating with extreme prejudice in a userform

Johnny C

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,820
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,040

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
38,820
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
76,344
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.:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,443
Members
430,548
Latest member
hh_dh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top