使用 VBA 遍历切片器项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21884843/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Loop through slicer items using VBA
提问by Andrei Vasilev
I want to loop select all slicer items within a certain range . Let's say , all items from 3to 6. My slicer contains the following items 1 , 2, 3, 5, 6, 8
我想循环选择一定范围内的所有切片器项目。比方说，从3到 的所有项目6。我的切片器包含以下项目1 , 2, 3, 5, 6, 8
Here is what I tried
这是我尝试过的
Sub SlicerTest()
With ActiveWorkbook.SlicerCaches("Slicer_rtytr")
Dim maxNumberOfDays As Long
maxNumberOfDays = 9 'I want to be able to identify the number of items programmatically but do not know how to do this
Dim fromDay As Long
fromDay = 3
Dim toDay As Long
toDay = 6
For i = 1 To maxNumberOfDays
If (i > fromDay And i < toDay) Then
.SlicerItems(CStr(i)).Selected = True
Else
.SlicerItems(CStr(i)).Selected = False
End If
Next i
End With
End Sub
The result should be that the slicer picks only 5but it throws an error . I tried a regular 1,2,3,4,5,6,7,8array and it works fine . I guess this one does not work because of missed values ?
结果应该是切片器只选择5但抛出错误。我尝试了一个常规1,2,3,4,5,6,7,8数组，它工作正常。我猜这个因为遗漏值而不起作用？
回答by Tomas Finn?y
This article explains what you need, and some.
这篇文章解释了你需要什么，以及一些。
https://paultebraak.wordpress.com/2012/02/24/accessing-the-slicer-through-vba/
https://paultebraak.wordpress.com/2012/02/24/accessing-the-slicer-through-vba/
The relevant bit:
相关位：
Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)
Set SL = sC.SlicerCacheLevels(1)
Debug.Print “——————————————————————————“
For Each sI In SL.SlicerItems
Debug.Print “Caption –> ” & sI.Caption
Debug.Print “Value –> ” + CStr(sI.Value)
Debug.Print “Unique Name –> ” + sI.Name
Debug.Print “——————————————————————————“
Next
回答by Max
The reason that a normal array works (normal being quantified as containing 9 elements in an array in this context) is because you specify that you want to loop from 1 to 9 in this line here For i = 1 To maxNumberOfDays. In your example you only have 6 items so you'll hit an error by trying to access more items than are currently within the array.
正常数组起作用的原因（在此上下文中正常被量化为数组中包含 9 个元素）是因为您指定要在此行中从 1 循环到 9 For i = 1 To maxNumberOfDays。在您的示例中，您只有 6 个项目，因此您会在尝试访问比数组中当前数量更多的项目时遇到错误。
I'm not too familiar with slicer objects unfortunately, but, to fix this issue you'll want to know exactly how many items are within your slicer. After a quick look at the documentation on silcersthere doesn't appear to be a count property, however, there is a numberOfColumns property. If the column number correlates with how many objects you have (again not entirely sure that it does) then just assign:
不幸的是，我对切片器对象不太熟悉，但是，要解决此问题，您需要确切知道切片器中有多少个项目。快速查看有关silcers的文档后，似乎没有 count 属性，但是，有一个 numberOfColumns 属性。如果列号与您拥有的对象数量相关（再次不完全确定），则只需分配：
maxNumberOfDays = ActiveWorkbook.SlicerCaches("Slicer_rtytr").numberOfColumns
maxNumberOfDays = ActiveWorkbook.SlicerCaches("Slicer_rtytr").numberOfColumns
If this does not work I would explore other options for figuring out the number of elements that will be within your array.
如果这不起作用，我会探索其他选项来计算数组中元素的数量。
