arrays - Excel VBA : Detect what slicers were selected and store into an Array

Question

是否有更有效的方法来检测/收听用户在切片器中选择的内容？我想将他们的选择存储到一个数组中，最好是通过他们给定的 sliceritem 名称而不是由我确定。在此示例中，切片器仅限于 3 个，但在我的实际工作表中，每个切片器将有 10-20 个。

Function slicerReader()
Dim slicerArray(2) As Variant ' 0 for Age, 1 for State
Worksheets(1).Select
 If ThisWorkbook.SlicerCaches("Slicer_Age").SlicerItems("20").Selected = True And ThisWorkbook.SlicerCaches("Slicer_Age").SlicerItems("20").Selected = True And ThisWorkbook.SlicerCaches("Slicer_Age").SlicerItems("30").Selected = True Then
slicerArray(0) = "All"
ElseIf ThisWorkbook.SlicerCaches("Slicer_Age").SlicerItems("20").Selected = True Then
slicerArray(0) = 20
ElseIf ThisWorkbook.SlicerCaches("Slicer_Age").SlicerItems("25").Selected = True Then
slicerArray(0) = 25
Else
slicerArray(0) = 30
End If

Debug.Print slicerArray(0)


If ThisWorkbook.SlicerCaches("Slicer_State").SlicerItems("California").Selected = True And ThisWorkbook.SlicerCaches("Slicer_State").SlicerItems("Florida").Selected = True And ThisWorkbook.SlicerCaches("Slicer_State").SlicerItems("Texas").Selected = True Then
slicerArray(1) = "All"
ElseIf ThisWorkbook.SlicerCaches("Slicer_State").SlicerItems("California").Selected = True Then
slicerArray(1) = "California"
ElseIf ThisWorkbook.SlicerCaches("Slicer_State").SlicerItems("Florida").Selected = True Then
slicerArray(1) = "Florida"
Else
slicerArray(1) = "Texas"
End If
Debug.Print slicerArray(1)

slicerReader = slicerArray

End Function

此代码显然不起作用，因为用户仍有可能选择切片器 (1,3) 或 (2,3) 等的组合

是否有任何可能的方法来遍历每个 sliceritem 并检查它们是否为真，然后将其存储到数组中？对于每个 slicercache，它可能必须是一个多维数组。想法？指针？ :D

Answer 1

尝试这样的事情。它将遍历状态切片器中的选定项目。

For Each x In ActiveWorkbook.SlicerCaches("Slicer_State").SlicerItems
If x.Selected = True Then
    a = x.Value
End If

下一个 x