Slicers and VBA

This page has also been published on The Microsoft Office Blog.

Juggling the SlicerCaches collection

For each slicer you add to your workbook, Excel adds a SlicerCache object too, which controls which Pivottable(s) your slicer controls.

So suppose we have three sheets, Sheet1, Sheet2 and Sheet3. Each sheet has a Pivottable, all Pivottables are based on one PivotCache. As soon as you add a slicer to each Pivottable (even if the slicer ties to the same field of the Pivottable) you get three SlicerCache objects:

Hierarchy of the slicer and it's family

The code below enumerates all slicer caches in your workbook:

Sub MultiplePivotSlicerCaches()
    Dim oSlicer As Slicer
    Dim oSlicercache As SlicerCache
    Dim oPT As Pivottable
    Dim oSh As Worksheet
    For Each oSlicercache In ThisWorkbook.SlicerCaches
        For Each oPT In oSlicercache.Pivottables
            oPT.Parent.Activate
            MsgBox oSlicercache.Name & "," & oPT.Parent.Name
        Next
    Next
End Sub

As soon as you check more than one Pivottable on the Pivottable Connections dialog of a Slicer, the slicers in question will share a single SlicerCache object. The other SlicerCache object will be removed from the collection. This explains why you cannot go back by unchecking all but one Pivottable in that dialog: all joined slicers will now be changed by changing the checked Pivottable(s) on any of them. In turn, each checked Pivottable becomes part of the Pivottables collection of the remaining SlicerCache object.

If you decide to select Slicer1 and change it's pivot connections by checking both Pivottable1 and Pivottable2, one slicercache is deleted (the one beloning to the Pivottable you checked to add to the current slicer). So the hierarchy changes to:

Changed hierarchy of slicers

So both Slicer1 and Slicer2 control Pivottables 1 and 2. The slicers 1 and 2 are in sync too because it is in fact the SlicerCache that is changed by the slicer. So the hierarchy in the picture above isn't entirely true.

Changing the buttons using VBA

It is easy enough to change the button appearance using a bit of VBA:

Sub AdjustSlicerButtonDimensions()
    With ActiveWorkbook.SlicerCaches("Slicer_City2").Slicers("City 2")
        .NumberOfColumns = 3
        .RowHeight = 13
        .ColumnWidth = 70
        'Note that changing the ColumnWidth also affects the Width of the slicer itself
        'So the next line will change the ColumnWidth!
        .Width = 300
    End With
End Sub

Note that the numbers do not coincide with what is shown on the ribbon. Apparently the unit of measure differs between VBA and the ribbon.

Changing the slicer itself and some SlicerCache properties

Changing some aspects of your slicer using VBA isn't hard to do. In fact, the macro recorder makes finding out how this works relatively simple. After changing some settings and doing a bit of tidying up I got:

Sub AdjustSlicerSettings()
    With ActiveWorkbook.SlicerCaches("Slicer_City2").Slicers("City 2")
        .Caption = "City"
        .DisplayHeader = True
        .Name = "City 2"
    End With
    With ActiveWorkbook.SlicerCaches("Slicer_City2")
        .CrossFilterType = xlSlicerNoCrossFilter
        'xlSlicerCrossFilterShowItemsWithDataAtTop:
        '   Visually indicate items with no data, items with data are pushed to the top
        'xlSlicerCrossFilterShowItemsWithNoData:
        '   Visually indicate items with no data, items with no data stay put
        'xlSlicerNoCrossFilter:
        '   No indication for items with no data.
        .SortItems = xlSlicerSortAscending
        .SortUsingCustomLists = False
        .ShowAllItems = False
        '   This ensures that data no longer in the pivot cache is not shown on the slicer
    End With
End Sub

Conclusion

Well, I hope I got you started with getting your head around how slicers work and how to address them using VBA. I find Slicers a real gem in Excel 2010. A great addition to the product!

Comments

Showing last 8 comments of 30 in total (Show All Comments):

Comment by: Jan Karel Pieterse (10/31/2011 1:07:57 AM)

Hi RealParadox,

What exactly is the problem?

Comment by: David (2/4/2012 9:08:40 AM)

Thank you so much for this resource! I have been searching everywhere for a way to link my slicers that do not share a cache with no success until now!

I modified the code provided to work with three slicers that have different caches. Unfortunately I have to run the "For Each" section twice, otherwise about 20% of the time it selects all of the slicer fields, or nearly all of them. Any idea why?

Dim mbNoEvents As Boolean
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)
    Dim oSl As SlicerItem
    If mbNoEvents Then Exit Sub
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Sh.Name = "Sheet1" Then
        mbNoEvents = True
        With SlicerCaches("Slicer1")
            For Each oSl In .SlicerItems
                    SlicerCaches("Slicer2").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                    SlicerCaches("Slicer3").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
            Next
            For Each oSl In .SlicerItems
                    SlicerCaches("Slicer2").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
                    SlicerCaches("Slicer3").SlicerItems(oSl.Caption).Selected _
                        = oSl.Selected
            Next
        End With
    End If
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    mbNoEvents = False
End Sub

Comment by: Midimal (2/28/2012 4:02:22 AM)

Hi! Does anyone know how to determine using VBA if slicer filter is on or not? (similar what is possible with autofilter)

thanks

Comment by: Jan Karel Pieterse (3/5/2012 5:12:11 AM)

Hi Midimal,

YOu could use this function to detect whether or not a slicers is in filter mode:

Function IsSlicerFiltered(oSl As Slicer)
    Dim oSi As SlicerItem
    For Each oSi In oSl.SlicerItems
        If oSi.Selected = False Then
            IsSlicerFiltered = True
            Exit Function
        End If
    Next
End Function

Comment by: bill (3/29/2012 3:19:18 PM)

Can someone tell me why I receive an 'application defined or object defined error' when I try to set a slicer selected mode to 'True' or 'False'? (example in below code: oSi.selected = True)

Dim oSi As SlicerItem
Dim oSl As SlicerCacheLevel
Dim selected As Integer
selected = 0
Dim slicerDateVal As Date
Dim slicerDateText As String


Dim i As SlicerCaches
Dim k As Slicer

    Application.EnableEvents = False
    'ActiveWorkbook.SlicerCaches("Slicer_Attendance_Date").ClearManualFilter
     For Each oSl In ActiveWorkbook.SlicerCaches("Slicer_Attendance_Date").SlicerCacheLevels
         For Each oSi In oSl.SlicerItems
             slicerDateVal = oSi.Caption
             slicerDateText = Format(slicerDateVal, "yyyy-mm-dd")
             If slicerDateVal >= startDate And slicerDateVal <= endDate Then
                'oSi(slicerDateVal).selected = True
             If oSi.selected = False Then
                oSi.selected = True
             End If

Comment by: Jan Karel Pieterse (3/30/2012 1:54:14 AM)

Hi Bill,

Not sure why, but perhaps the item in question is greyed out on the slicer because of other filters being in place?

Comment by: Mark F. (4/3/2012 9:26:35 AM)

I cannot find the spot to change the format of numbers in the slicer. Currently I am getting very generic looking numbers all left justified. Any solutions to this?

Comment by: Jan Karel Pieterse (4/3/2012 9:54:29 PM)

Hi Mark,

The number format of a slicer cannot be adjusted I'm afraid.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.