Error handling Resume but with Msgbox reflecting error

Question

I have two files (A & B), each containing multiple sheets. I want to delete certain sheets.

The problem occurs when sheets I want to delete are already gone.

I introduced error handling. Is there any method to Resume code when encountering errors but also give a dynamic report that shows which file does not contain which sheets?

I would like to contine executing code when there is an error but report which file and which sheet has the error.

Sub Remove_sheets()
    Dim A As workbook: Set A = Workbooks("A.xlsx")
    Dim B As workbook: Set B = Workbooks("B.xlsx")
    Const NameA As String = "A"
    Const NameB As String = "B"

    On Error GoTo Handling
    Application.DisplayAlerts = False
       
    A.Worksheets("Sheet1").Delete     'can be any sheets
    B.Worksheets("Sheet5").Delete
    B.Worksheets("Sheet5 (2)").Delete

    Exit Sub
    
Handling:
    MsgBox "There is no sheet1 in " & NameA & " to be deleted"
    Application.DisplayAlerts = True
End Sub

A typical method would be to use On Error Resume Next in a separate function to determine if the sheet exists before attempting to delete it. Is there a specific reason for wanting to know the names of sheets that did not exist? — freeflow, Mar 19 '21 at 15:14
Another option is to put a `Resume Next` as the last line in the error handler. — Brian M Stafford, Mar 19 '21 at 15:16
Thank you, yes, I did use Resume Next to run the code, but this method does not report the summary, the reason why I'd like to see which file and which sheets is I always want to have an idea of what is going on behind the code. — Shane, Mar 19 '21 at 15:25

score 1 · Accepted Answer · answered Mar 19 '21 at 17:07

Using error handling like you do in your code won't achieve the desired result. Since an error could occur with any of the delete statements, there's no way to know which delete threw the error so accurate reporting isn't possible.

Here's how I would set this up:

Public Sub Remove_sheets()
   Dim A As Workbook: Set A = Workbooks.Open("A.xlsx")
   Dim B As Workbook: Set B = Workbooks.Open("B.xlsx")

   Application.DisplayAlerts = False
   
   DeleteSheet "Sheet1", A
   DeleteSheet "Sheet5", B
   DeleteSheet "Sheet5 (2)", B
   
   Application.DisplayAlerts = True
End Sub

Private Sub DeleteSheet(shtName As String, wb As Workbook)
   If WorksheetExists(shtName, wb) Then
      wb.Worksheets(shtName).Delete
   Else
      MsgBox "There is no " & shtName & " in " & wb.Name & " to be deleted"
   End If
End Sub

The following helper function is from this answer:

Private Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
   Dim sht As Worksheet

   If wb Is Nothing Then Set wb = ThisWorkbook
   On Error Resume Next
   Set sht = wb.Sheets(shtName)
   On Error GoTo 0
   WorksheetExists = Not sht Is Nothing
End Function

Thank you, Brian, your code is more dynamic than mine. Will learn from this. — Shane, Mar 19 '21 at 18:19
If an answer has solved your question please consider [accepting it](https://meta.stackexchange.com/q/5234/179419) by clicking the check-mark and upvoting by clicking the up arrow. This indicates to the wider community that you've found a solution. There is no obligation to do this. — Brian M Stafford, Mar 19 '21 at 18:21

Error handling Resume but with Msgbox reflecting error

1 Answers1