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:

Function SHEETNAME(IndexNumber As Integer)
    Application.Volatile
    With ThisWorkbook
        If IndexNumber <= .Sheets.Count Then
            SHEETNAME = .Sheets(IndexNumber).Name
        Else
            SHEETNAME = ""
        End If
    End With
End Function

for example. You can also simplify the deletion code:

Sub Delete_Sheets()

    'Delete All sheets, except for the first number of admin sheets
    'as defined on the instructions page

    If MsgBox("Are you sure you want to delete ALL Test Scripts?", vbYesNo, "Delete All?") = vbNo Then
        MsgBox "Cancelled"
        Exit Sub
    End If

    With Application
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
   End With

    Dim Strtsht                    As Integer
    Dim Lstsht                     As Integer

    Strtsht = Range("admin_sheets").Value
    Lstsht = Sheets.Count

    ' check to see if there any sheets to delete
    If Lstsht <= Strtsht Then
        MsgBox "There are No Additional Sheets to Delete"
    Else

        Application.DisplayAlerts = False
        ' loop through sheets and delete
        For i = Lstsht To Strtsht + 1 Step -1
            Sheets(i).Delete
        Next i
      MsgBox "Deletion Complete."
    End If
    With Application
      .DisplayAlerts = True
      .Calculation = xlCalculationAutomatic
   End With

End Sub

[JungleJme]

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

[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!

[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.