Excel VBA工作表删除子程序故障：可用与失效版本求助

Let's break down why the version using GlobalSheetName isn't working, and fix it step by step:

1. Critical Logic Error in Deletion Loop #

The biggest issue is how you're checking which sheets to delete. Your current nested loops do this:

• For every worksheet, loop through each allowed sheet name
• If the worksheet doesn't match the current allowed name, delete it

This means even allowed sheets will get deleted! For example, when checking the "Sammanställning" sheet against "Pris_Schrack", the names don't match, so your code tries to delete the allowed sheet.

Instead, you need to:

• For each worksheet, check if it exists in the allowed list
• Only delete it if it's NOT in the allowed list

2. Undeclared Variables #

Variables like char, strCancel aren't declared. This can lead to unexpected behavior (since VBA treats them as variants). Always add Option Explicit at the top of your module to catch this.

3. Looping Through Worksheets While Deleting #

Using For Each ws In Worksheets while deleting sheets can cause the loop to skip items or throw errors, because the worksheet collection changes as you delete items. It's safer to loop backwards from the last sheet to the first.

4. MsgBox Result Comparison #

You're comparing the MsgBox result to the string "1", but MsgBox returns an integer (e.g., vbOK = 1, vbCancel = 2). Use the built-in constants for clarity and reliability.

Corrected Code #

Here's the fixed version incorporating all these changes:

Option Explicit ' Enforces variable declaration (critical for avoiding bugs)

Public Const GlobalSheetName As String = "Sammanställning,Pris_Schrack,Pris_Pelco,Pris_Swansson,Pris_Övrig"

Sub Tabortblad()
    Dim ws As Worksheet
    Dim allowedSheets As Variant
    Dim isAllowed As Boolean
    Dim SnNumm As Long
    Dim strCancel As Integer
    Dim i As Long
    
    allowedSheets = Split(GlobalSheetName, ",")
    SnNumm = UBound(allowedSheets) - LBound(allowedSheets) + 1
    
    If Worksheets.Count = SnNumm Then
        MsgBox "Där finns bara " & SnNumm & " blad"
    Else
        strCancel = MsgBox("Är du säker på att du vill radera bladen", vbOKCancel, "Radera Blad")
        If strCancel = vbOK Then
            Application.ScreenUpdating = False
            Application.DisplayAlerts = False
            
            ' Loop backwards to avoid issues when modifying the worksheet collection
            For i = ActiveWorkbook.Worksheets.Count To 1 Step -1
                Set ws = ActiveWorkbook.Worksheets(i)
                isAllowed = False
                
                ' Check if the current sheet is in the allowed list
                For Each char In allowedSheets
                    If ws.Name = char Then
                        isAllowed = True
                        Exit For ' Stop checking once we find a match
                    End If
                Next char
                
                ' Delete the sheet only if it's not allowed
                If Not isAllowed Then
                    ws.Delete
                End If
            Next i
            
            ' Clean up ranges on the main sheet (using With for efficiency)
            With Sheets("Sammanställning")
                .Range("B:B").Value = ""
                .Range("P68:R" & .Cells(.Rows.Count, "R").End(xlUp).Row + 1).Value = ""
            End With
            
            Application.DisplayAlerts = True
            Application.ScreenUpdating = True
            
            ' Reset to the main sheet
            Sheets("Sammanställning").Activate
            Range("A1").Select
        End If
    End If
End Sub

Key Improvements: #

• Added Option Explicit to catch undeclared variables
• Fixed the deletion logic to only remove sheets not in the allowed list
• Looped backwards through worksheets to avoid collection modification issues
• Used vbOK instead of string "1" for MsgBox comparison
• Used With block for cleaner range references on the main sheet

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