Excel Crashes when Sheet is Deleted

[JungleJme]

I posted about this a while ago and i thought i'd worked around the issue but i haven't.

Wasn't sure whether i was meant to revive the old thread or start a new one, sorry if i've done the wrong thing!

This time i'm attaching a workbook - i've sanitised and simplified it.

There is a control button on the "Instructions" tab that calls the macro delete_sheets. It should delete each sheet after the user defined administrative sheets.

I have a couple of UDFs which calculate on the "Instructions" tab but the code is in their own module. The UDFs use the sheets.count calculation.

When i click the "Delete All Scripts" button (in the attached i would expect it to delete two sheets BP 2 and BP 3). Excel Crashes completely.

I'm sure the conflict is between having a UDF that counts sheets and command that deletes them but i'm stumped as to what a work around is. Any takers?

Thanks,

J

[AB33]

You have a very complicated code for simply deleting sheet.
This line causes excel to crash.
Sheets(Strtsht + 1).Delete
I am not a big fun of do/while loop and there is not need for this task.
Just loop through the sheets and delete them.

[JungleJme]

I did that so the sheet after the admin sheet (strhsht) gets deleted each time -

I'm trying to say delete sheets 4 thru x

By deleting sheet 4 until the loop reaches x i'm always deleting an existing sheet. If i looped up through index numbers eventually it would try to delete a sheet that didn't exist....

[AB33]

No,
You can either exclude some sheets from the loop (For-Next) or use an array to store the names of sheet you want to delete.

[romperstomper]

You need error handling in your functions:

Please Login or Register  to view this content.

for example. You can also simplify the deletion code:

Please Login or Register  to view this content.

[AB33]

Rory,
Good man, you have not used do/while crap.

[JungleJme]

Thank you both!
AB33 - i went away and changed the loop to a "For Next" with some exclusion logic and it didn't crash on the Delete command!
RomperStomper - Thanks, i'll definitely add the error handling to the functions

Thank you both for your help!

[JungleJme]

As a side note, why is Do while or Do Until so bad?

[AB33]

Because they can lead you to infinite loop. This can cause your beloved PC to crash.

[JungleJme]

OK, makes sense. Noted for future reference!

Thanks!

[romperstomper]

There's nothing wrong with Do loops per se (aside from the risk of endless loops) and they do have their uses, but when you know a start and end point it makes sense to use a For... Next structure instead.