vba - 使用 VBA 循环遍历工作簿切片器名称

Question

我尝试过谷歌搜索并搜索这个，但无法完全理解。我想做的就是循环遍历事件工作表上的切片器并删除切片器(如果存在)。

目前我那里有 6 个切片机。以前我有

    ActiveSheet.Shapes.Range(Array("Market Segment Name 2", "Line of Business 2" _
    , "Customer Name", "Product Group Name", "Product Type Name", "Product Code") _
    ).Select
    Selection.Delete

但如果我已经删除了切片器，那就不好了。

现在我正在尝试(注意 wb 在名为“Public”的模块中设置为全局变量)

Option Explicit
Dim sl As Slicer
Dim slName As String

Set wb = ActiveWorkbook

For Each sl In wb.SlicerCaches
    If sl.Name = "Market Segment Name 2" Or _
        sl.Name = "Line of Business 2" Or _
        sl.Name = "Customer Name" Or _
        sl.Name = "Product Group Name" Or _
        sl.Name = "Product Type Name" Or _
        sl.Name = "Product Name" Then
        slName = sl.Name
        ActiveSheet.Shapes.Range(slName).Delete
    End If
Next sl

对我来说，它似乎应该有效。如果我进入 SlicerItem 级别，我就可以使用它，但我只是不知道如何在 Slicer 级别访问它......

任何想法将不胜感激。谢谢。

如果失败，我将尝试构建数组并以这种方式删除，但我仍然需要一种方法来测试切片器当前是否存在。

Answer 1

我能想到的有两种方法。

其中一个是Force方法。这里我们不检查切片器是否存在。如果它存在，我们只需将其删除即可。例如

On Error Resume Next
ActiveSheet.Shapes.Range("Market Segment Name 2").Delete
ActiveSheet.Shapes.Range("Line of Business 2").Delete
'
'~~> And so on
'
On Error GoTo 0

另一种方法是实际检查切片器是否存在，然后将其删除。例如

Dim sl As SlicerCache

On Error Resume Next
Set sl = ActiveWorkbook.SlicerCaches("Market Segment Name 2")
On Error GoTo 0

If Not sl Is Nothing Then sl.Delete

'For Each sl In ActiveWorkbook.SlicerCaches
    'Debug.Print sl.Name
'Next

编辑:

评论跟进。

将第一个代码转换为循环。

Sub Sample()
    Dim sSlicers As String
    Dim Myar
    Dim i As Long

    '~~> Slicers you want to delete
    sSlicers = "Market Segment Name 2,Line of Business 2"
    sSlicers = sSlicers & "," & "Customer Name,Product Group Name"
    sSlicers = sSlicers & "," & "Product Type Name,Product Code"

    '~~> Split the names using "," as a delimiter
    '~~> If your slicer names have "," then use a different delimiter
    Myar = Split(sSlicers, ",")

    For i = LBound(Myar) To UBound(Myar)
        On Error Resume Next
        ActiveSheet.Shapes.Range(Myar(i)).Delete
        On Error GoTo 0
    Next i
End Sub