VBA Excel工作表方法在Office更新后不起作用

Question

我有一个脚本，它从CSV文件中获取数据，然后生成图形。经过多次Office更新后，该脚本已经完成工作。不可能带Office版本回来。

这是代码:

Dim sheet
    For Each sheet In ActiveWorkbook.Sheets
        Select Case sheet.Name
            Case "Archive": Sheets(sheet.Name).Delete
            Case "Trend":  Sheets(sheet.Name).Delete
            Case "PivotTable": Sheets(sheet.Name).Delete
        End Select
Next

我收到以下错误:"应用程序定义或对象定义错误"。这个错误出现在选择情况下，因为我的对象"sheet"没有方法"Name"。

我尝试将"sheet"声明为工作表

Dim sheet as Worksheet
    For Each sheet In ActiveWorkbook.Sheets
        Select Case sheet.Name
            Case "Archive": Sheets(sheet.Name).Delete
            Case "Trend":  Sheets(sheet.Name).Delete
            Case "PivotTable": Sheets(sheet.Name).Delete
        End Select
    Next

在脚本的第一圈我没有任何问题，从第二圈我有错误:"类型不匹配"

我怎么解决它?

谢谢

Answer 1

注意:这不一定是答案，但它太长了，不能作为评论写下来。

你可以保持你的代码更干净，更简单:我不喜欢使用sheet，因为它非常类似于Sheet。另外，最好避免使用ActiveWorkbook。

Dim Sht             As Worksheet
For Each Sht In ActiveWorkbook.Sheets
    Select Case Sht.Name
        Case "Archive", "Trend", "PivotTable"
            Sht.Delete
    End Select
Next Sht

Answer 2

工作表可以包含图表或工作表对象

然后

如果只删除Worksheets，则:

Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets '<-- loop through Worksheets collection
    Select Case sheet.Name
        Case "Archive": Sheets(sheet.Name).Delete
        Case "Trend":  Sheets(sheet.Name).Delete
        Case "PivotTable": Sheets(sheet.Name).Delete
    End Select
Next

如果你想同时删除Worksheets和Charts，那么:

Dim sheet As variant '<-- a Variant variable can be assigned both a Worksheet and a Chart object
For Each sheet In ActiveWorkbook.Sheets '<-- loop through Worksheets and Charts collections
    Select Case sheet.Name
        Case "Archive": Sheets(sheet.Name).Delete
        Case "Trend":  Sheets(sheet.Name).Delete
        Case "PivotTable": Sheets(sheet.Name).Delete
    End Select
Next