Cannot reference VisibleSlicerItemsList for regular pivot table in VBA

Question

I am trying to manipulate a slicer for a regular Pivot table in VBA (not PowerPivot), but I cannot reference the VisibleSlicerItemsList. I get an error:

Application-defined or object-defined error

Any idea what I am doing wrong?

Screenshot showing data and pivot table with slicer from test workbook, along with the error message "Application-defined or object-defined error" for the VisibleSlicerItemsList in the Locals window

You can download the test workbook here.

Robin Mackenzie Robin Mackenzie · Accepted Answer · 2016-06-20T07:27:01

Check the MSDN entry on this property - it states that:

The VisibleSlicerItemsList property is only applicable for slicers that are based on OLAP data sources (SlicerCache.OLAP = True).

In your example you are using a pivot table connected to data in the sheet and not to an OLAP cube.

You can work around it with code like this which iterates the SlicerItems collection of the SlicerCache:

Option Explicit

Sub Test()

    Dim objCache As SlicerCache
    Dim objItem As SlicerItem
    Dim varChoices As Variant
    Dim lngCounter1 As Long
    Dim lngCounter2 As Long

    Set objCache = ThisWorkbook.SlicerCaches("Slicer_Company")
    varChoices = Array("1", "3", "5")

    ' iterate slicers
    For lngCounter1 = 1 To objCache.SlicerItems.Count
        Set objItem = objCache.SlicerItems(lngCounter1)
        'assume not for selection
        objItem.Selected = False
        'iterate choices to check for activation
        For lngCounter2 = 0 To UBound(varChoices)
            If objItem.Name = varChoices(lngCounter2) Then
                'activate and exit loop
                objItem.Selected = True
                Exit For
            End If
        Next lngCounter2
    Next lngCounter1

End Sub

Cannot reference VisibleSlicerItemsList for regular pivot table in VBA

1 Answers