循环内调用ws.delete触发Run Time '424'错误，仅首次执行时出现

Let's dig into that Run Time '424' (Object Required) error you're facing when deleting sheets in a loop—especially since it only pops up on the first pass. I’ve seen similar issues with sheet deletion workflows tied to user login/logout, so here’s how to troubleshoot and fix this:

Why This Happens (Likely Root Causes) #

Run Time 424 almost always boils down to a broken object reference. Given your scenario (sheets created on login, deleted on logout via two subroutines), these are the most probable culprits:

• A target sheet doesn’t actually exist on first delete attempt: Maybe the sheet creation failed for one user (e.g., duplicate name, permission glitch) but your logout code still tries to delete it.
• Loop indexing issues: If you’re deleting sheets by index (Sheets(i)), deleting a sheet shifts the indexes of all subsequent sheets forward. On the first pass, this can lead to referencing a sheet that no longer exists.
• Conflict between the two logout subroutines: One subroutine might delete a sheet before the other gets to it, so the first loop iteration hits a "missing object" error, while later iterations skip already-deleted sheets.

Step-by-Step Fixes & Troubleshooting #

1. Verify the Sheet Exists Before Deleting #

First, add a check to make sure the sheet you’re trying to delete actually exists. This prevents trying to delete a non-existent object:

Sub DeleteUserSheets()
    Dim ws As Worksheet
    Dim targetSheetName As String
    
    For Each user In AuthorizedUsers ' Replace with your user list variable
        ' Adjust this to match your sheet naming logic (e.g., "费用日志_UserName")
        targetSheetName = "费用日志_" & user.Name
        
        ' Try to grab the sheet object
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(targetSheetName)
        On Error GoTo 0
        
        ' Only delete if the sheet exists
        If Not ws Is Nothing Then
            Application.DisplayAlerts = False ' Skip deletion confirmation prompt
            ws.Delete
            Application.DisplayAlerts = True
            Set ws = Nothing ' Clean up the object reference
        End If
    Next user
End Sub

Repeat this logic for your income sheet deletion subroutine, adjusting the sheet name pattern accordingly.

2. Reverse Your Loop If Deleting By Index #

If you’re deleting sheets using their index number, reverse the loop direction (start from the last sheet and work backwards). This avoids index shifting issues that break object references:

Sub DeleteAllUserSheets()
    Dim i As Integer
    
    ' Loop from last sheet to first to avoid index gaps
    For i = ThisWorkbook.Sheets.Count To 1 Step -1
        With ThisWorkbook.Sheets(i)
            ' Check if the sheet is a user-specific log sheet
            If Left(.Name, 4) = "费用日志" Or Left(.Name, 4) = "收入日志" Then
                Application.DisplayAlerts = False
                .Delete
                Application.DisplayAlerts = True
            End If
        End With
    Next i
End Sub

3. Coordinate Your Two Logout Subroutines #

If the two subroutines overlap in which sheets they try to delete, you’ll get duplicate deletion attempts. Fix this by:

• Splitting responsibilities: Have one subroutine delete only expense logs, the other only income logs—no cross-over.
• Tracking deleted sheets: Add a collection to log which sheets have been deleted, then have the second subroutine skip those entries.
• Merging the subroutines: Combine both deletion workflows into one single subroutine to eliminate overlap entirely.

4. Add Error Logging to Pinpoint the Exact Issue #

To figure out exactly which sheet is causing the first-pass error, add error handling that logs the problematic sheet name:

Sub DeleteSheetsWithLogging()
    Dim ws As Worksheet
    Dim targetSheetName As String
    
    On Error GoTo DeleteErrorHandler
    
    For Each user In AuthorizedUsers
        targetSheetName = "费用日志_" & user.Name
        Set ws = ThisWorkbook.Sheets(targetSheetName)
        
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
        Set ws = Nothing
    Next user
    
    Exit Sub

DeleteErrorHandler:
    MsgBox "Failed to delete sheet '" & targetSheetName & "': " & Err.Description & _
           " (Error Code: " & Err.Number & ")"
    Resume Next ' Continue with remaining sheets
End Sub

This will pop up a message telling you exactly which sheet is failing, so you can check if it was ever created correctly.

Quick Additional Tips #

• Make sure no other code (like cell formulas or other macros) is referencing the sheets you’re deleting—this can also trigger object errors.
• Always reset Application.DisplayAlerts = True after deleting sheets, otherwise future alert prompts will stay disabled.

内容的提问来源于stack exchange，提问作者Bill Miller