Accessing the Slicer through VBA


There may be times when we want to programmatically control slicers through VBA. For example, we may want to default a date to the current date or set a cost centre depending on who has opened the book. This post looks how a slicer can be controlled through VBA.

In this example, we have added a slicer to a worksheet that uses a date hierarchy as its source. Because, we have included all levels of the hierarchy when the slicer was setup, we get three individual slicers for each level of the hierarchy.

If we look at the settings for the slicer (right click on the slicer and select slicer settings), we can see that the slicer has a name and each level of the slicer hierarchy maintains the hierarchy level name. For example, the Slicer_Dates_Hie below has a level Year, Month and Day. Although we can change the name (for example the name Year in the picture below), the slicer retains the mdx level that the slicer belongs to.

Accessing the Slicer

We can access the slicer through the SlicerCaches object. This is as simple as declaring the slicer cache and referencing it to the name of the slicer we want to use. For example;

Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)

Navigating the Structure of the Slicer

Once we have a reference to the slicer we can navigate its structure using SlicerCacheLevels. For example we can determine the number of levels of the slicer and iterate over them with the following code.

Dim sC As SlicerCache
Dim sL As SlicerCacheLevel
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)

For Each sL In sC.SlicerCacheLevels
Debug.Print “Level ” + CStr(sL.Ordinal) + ” –> ” + sL.Name
Next sL

Naturally, the level can be accessed through the cache level ordinal to produce the same result. The highest level (year) takes the value 1 which increments for each level from the first level. There is always a level (ie level 1) even if the slicer is based on a single attribute.

Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)
For i = 1 To sC.SlicerCacheLevels.Count
Debug.Print “Level ” + CStr(i) + ” –> ” + sC.SlicerCacheLevels(i).Name
Next i

Slicer Data Members

We can gain access to the data items through slicer items, as mdx attributes, they have a caption, value and a key (member unique name). For example the year 2011 in this slicer has a value of 2011 and a name (MDX unique name) of [Dates].[Dates Hie].[Year].&[2011]

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

Setting the Slicer Value

Slicer item selection must be set through the visible slicer items list and is specified using an array. For example, we could set the SlicerCache (selected items) to 2011 and 2012 with the following code;

sC.VisibleSlicerItemsList = Array(“[Dates].[Dates Hie].[Year].&[2011]“, “[Dates].[Dates Hie].[Year].&[2012]“)

The name selected must be a data member of the level. If not a runtime error will occur (as below)

Once the values are set, connected pivots are updated immediately

Member Iteration

Members can be easily iterated using the following code;

Dim sC As SlicerCache 
Dim SL As SlicerCacheLevel 
Dim sI As SlicerItem
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Dates_Hie") 
Set SL = sC.SlicerCacheLevels(2)
For Each sI In SL.SlicerItems    
 sC.VisibleSlicerItemsList = Array(sI.Name) 
Next

Conclusion

The control of slicers through VBA could be used to provide some very nice personalisation to work books.

NB:  If you liked this post you might be interested in this one.  In it, I discuss setting slicers through cell association to pivot rows.

About these ads

34 thoughts on “Accessing the Slicer through VBA

  1. Hi! Please
    can I get different format of a measure in a PowerPivot table based on Slicer Item value
    something like this. I did try but no luck. I can send a file if it needed

    thank you!!!

    If SlicerItem = “abs” Then

    With ActiveSheet.PivotTables(“PP002″).PivotFields( _
    “[Measures].[sum]“)
    .Calculation = xlNormal
    .NumberFormat = “# ### ##0_.”
    End With
    End If

    If SlicerItem = “ratio” Then

    With ActiveSheet.PivotTables(“PP002″).PivotFields( _
    “[Measures].[sum]“)
    .Calculation = xlPercentOfColumn
    .NumberFormat = “0.0%”
    End With
    End If

    • Hi 100sky,

      You can specify the format string for the pivot columns with code like this;

      Dim x As PivotTable
      Set x = Sheet2.PivotTables(1)
      x.DataBodyRange.Cells.NumberFormat = “#,##0″
      x.PivotFields(“[Measures].[End of Day Rate]“).NumberFormat = “#,##0.000″

      Note i can set the entire data area or only a particular field.

      Is this what you want?

  2. thank you!
    let’s we’ve got 2 store (facts)

    A 100
    B 200

    we can do a measure Sum:=sum(FT) and we can make 2 PP tables like
    A 100
    B 200
    or
    A 33.3%
    B 66.7%

    What I want, if it possible:
    To create dimension and not link it to fact table, like

    absolute_value
    ratio

    and in One PP table after clicking by slicer item “ABS” getting format “100” but after clicking by slicer item “RATIO” getting format “33.3%” all in the same column

    I can get result what I need only by using Buttons&VBA and want to know if it’s possible by SlicerItems&VBA

    Thank you! Very much!

  3. I think what you want to do is change the way that the pivot table operates so that you can set the ‘value’ to be a percent of the column or the native value.

    So, I have two subs that specify raw values or percent values. You can then fire these how you like (not the only difference is in the calculation and number format)

    Sub RawValues()

    Dim x As PivotTable
    Set x = Sheet3.PivotTables(“PivotTable1″)

    With x.PivotFields(“[Measures].[Sum of Value]“)
    .Calculation = xlNormal
    .NumberFormat = “#,##0″
    End With

    End Sub

    Sub PercentValues()

    Dim x As PivotTable
    Set x = Sheet3.PivotTables(“PivotTable1″)

    With x.PivotFields(“[Measures].[Sum of Value]“)
    .Calculation = xlPercentOfColumn
    .NumberFormat = “#,##0%”
    End With

    End Sub

  4. Paul, thank you!

    Your macro is what I need.

    If it’s possible what when I have Slicer consist of 2 Slicer Items (“abs” and “ratio”) and clicking “abs” I’ve got macro “RawValues” execution and clicking “ratio” I’ve got macro “PercentValues” execution

    Thank you from Russia

    • I am not sure if you can attach the marco(s) to slicer items so that the events are fired with a change of a slicer value. What you may have to do is create a pivot table with the filter showing the ‘slicer selection’ and attach the slicer to it. Then, you can attach your macro to the on_change event on the worksheet so that you can check what the selected value of the slicer is and apply a check against the pivot values.

  5. Yes, I can return name(value) any of slicer items due to your article but can’t attach the macro to slicer item clicking directly. Now I know it definitely

    Your blog is a great help
    Thank you

    • Hi Vanessa, I am a little unsure of exactly what you are after. But doesn’t the following help?

      Sub MemberInfo()

      Dim sC As SlicerCache
      Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Month”)
      Debug.Print “There are ” + CStr(sC.SlicerCacheLevels(1).Count) + ” elements in your slicer”

      Dim sCL As SlicerCacheLevel
      Set sCL = sC.SlicerCacheLevels(1)

      Dim i As Integer
      For i = 1 To sCL.SlicerItems.Count

      Debug.Print “Slicer Caption -> ” + sCL.SlicerItems(i).Caption
      Debug.Print “Slicer Value -> ” + sCL.SlicerItems(i).Value
      Debug.Print “Unique Name –> ” + sCL.SlicerItems(i).Name
      Debug.Print “—-”

      Next i

      End Sub

  6. Hello!

    I was trying to set the Slicer Value as you said before:

    “sC.VisibleSlicerItemsList = Array(“[Dates].[Dates Hie].[Year].&[2011]“, “[Dates].[Dates Hie].[Year].&[2012]“)”

    but i found VisibleSlicerItemsList is read-only.

    http://msdn.microsoft.com/en-us/library/ff822312.aspx

    My data source is OLAP and when you are working with OLAP, I think there are some properties that are read-only. Do u know another way to set the slicer value?

    Thanks in advance and regards!
    Emilio

    • Hi Emilio,

      I can set the slicer (against a cube) values with these snippets;

      ‘ finally set it to 2005
      Sheet1.Cells(2, 2) = “[Date].[Calendar Year].&[2005]”
      sC.VisibleSlicerItemsList = Array(“[Date].[Calendar Year].&[2005]“)

      ‘ multiple set
      Sheet1.Cells(2, 2) = “Multiple”
      sC.VisibleSlicerItemsList = Array(“[Date].[Calendar Year].&[2005]“, “[Date].[Calendar Year].&[2006]“)

      I’ll also flick you through the workbook.

      • Hello Paul, I got it!

        I tried your solution and it works fine. The problem was I was setting de VisibleSlicerItemList of the SlicerCacheLevel instead of the SlicerCache.

        Thank you very much and kind regards,
        Emilio

  7. Does your site have a contact page? I’m having trouble locating it but, I’d like to shoot
    you an e-mail. I’ve got some suggestions for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it expand over time.

  8. Pingback: Pivot Filtering with Cell Selection and VBA « Paul te Braak

  9. Pingback: Dynamic DAX Query Tables in Excel 2013 « Chris Webb's BI Blog

  10. Pingback: Dynamic DAX Query Tables in Excel 2013 - SQL Server Blog - SQL Server - Telligent

  11. Pingback: Dynamic DAX Query Tables in Excel 2013 - Atlas Analytics Inc.

  12. Pingback: Dynamic DAX Query Tables in Excel 2013 | Best Analytics

  13. I was very happy to find this site. I need to to thank you for ones time just for this fantastic read!

    ! I definitely really liked every bit of it and I have you
    book-marked to look at new information in your web site.

  14. Hi,
    I do have a VBA giving me a list of slicers left, according any selection done in related slicers:
    Sub GetSlicerValues01()
    Dim lCt As Long
    Dim oSi As SlicerItem
    Worksheets.Add
    With ActiveSheet.Range(“A1″)
    .Value = “Slicer value”
    .Offset(, 1).Value = “Selected”
    .Offset(, 2).Value = “Available”
    For Each oSi In ActiveWorkbook.SlicerCaches(“Slicer_Belt_series”).SlicerItems
    lCt = lCt + 1
    .Offset(lCt).Value = oSi.Value
    .Offset(lCt, 1).Value = oSi.Selected
    .Offset(lCt, 2).Value = oSi.HasData
    Next
    End With
    End Sub
    But I actually just want a number back, telling me the amount of slicer left. It should be easy just to count where Selected = true AND HasData = true.

    Any suggestions how such code could look like?

    Thanks in advance JHN

    • Hi JHN,

      You could create a function to return the count of selected items. This should be ok.

      Function SelectedCount() As Integer

      Dim sc As SlicerCache
      Dim sl As SlicerCacheLevel
      Set sc = ActiveWorkbook.SlicerCaches(“Slicer_Country”)
      Set sl = sc.SlicerCacheLevels(1)

      For i = 1 To sl.SlicerItems.Count
      If sl.SlicerItems(i).Selected = True Then SelectedCount = SelectedCount + 1
      Next

      End Function

      Regards,

      Paul

  15. Hi, I would like to pose another question on the same subkect of Data Slicers. I have searched around and have not been able to find out if any Slicer Items have been clicked or changed.

    I basically have a complex pivot and correspoding chart, but the chart series keep chaging to default. I have worked out the VBA code t set the series type and colour but this only works when I click a button.

    I need to run this reset macro code when the user click any of the items in the two or 3 slicers I have.

    Could any one help. Thanks Kuldip

    • Hi Kuldip,

      I think the easiest way is to have your slicers put their data to different worksheet (almost like a control sheet you might say). On that worksheet, you can then listen for the worksheet_Change event to update the charts, names etc. There won’t be any need for a different button.

      HTH, Paul

  16. Hi Paul,
    Many Thanks for the suggestion. The issue is that the contents of the data slicer and pivot keep changing so not to static. Much appreciated, however, I managed to find a good solution :

    I used Worksheet_PivotTableUpdate
    So when ever the pivot changed I used this as a trigger to update the series type and colour and works great.

    The following is what I used to set the series attributes.
    I = 1
    Set seriesCol = ActiveSheet.ChartObjects(1).Chart.SeriesCollection
    For Each mySeries In seriesCol
    Set mySeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(I)
    mySeries.Select
    With mySeries
    ‘MsgBox “Series Name : ” & .Name
    Select Case UCase(.Name)
    Case Is = “ACTUAL HEAD BOOKING”
    .ChartType = xlColumnStacked
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(102, 153, 255) ‘ACTUAL HEAD BOOKING (Blue)
    .Transparency = 0
    .Solid
    End With
    Case Is = “EQUIVALENCE”
    ‘Etc.. Etc… Etc…
    End Select
    End With
    I = I + 1

    Next

    Thanks again. Regards Kuldip.

  17. Hi Paul, I have another question that I am struggling with.

    Hi I having difficulty trying to cycle through the slicers to see if they are filtered or not.

    My goal is to get all the selected Slicer into a worksheet so that I can apply a (High to Low) filter to the underlying pivot data so that I can pick the “Top 5 over budget” based upon the selections made in the Data Slicers.

    I have the following code but get see error:
    ‘Run Time Error 438’ Object doesn’t support this method’
    Can someone advise how I can achieve this.

    Public Sub top_over_under_booked()
    Dim oSi As SlicerItem
    Dim oSlicercache As SlicerCache
    Dim oSl As SlicerCacheLevel
    Dim oPt As PivotTable
    Dim oSh As Worksheet
    Set target_ws = ThisWorkbook.Worksheets(“Get Slicer Selections”)
    For Each oSlicercache In ThisWorkbook.SlicerCaches

    For Each oPt In oSlicercache.PivotTables

    oPt.Parent.Activate ‘Slice Name
    worksheet_name = UCase(oPt.Parent.Name)
    If worksheet_name = UCase(“Chart Analysis 5 Years”) Then
    column_no = 0
    slicer_name = UCase(oSlicercache.Name)
    Select Case UCase(oSlicercache.Name)
    Case Is = “SLICER_FY1″
    column_no = 1
    Case Is = “SLicer_REPORT_PT_DEPT1″
    column_no = 2
    End Select
    If column_no 0 Then
    For Each oSl In ActiveWorkbook.SlicerCaches(oSlicercache.Name) ‘ <—– Error
    For Each oSi In oSl.SlicerItems
    'oSi.Selected = True
    check_slicer_string = oSi.Value
    'target_ws.Cells(ource_ws.Cells(65000, column_no).End(xlUp).Row + 1, column_no) = oSlicercache.Value
    Next
    Next
    End If
    oPT.Parent.Name
    End If
    Next
    Next
    End Sub

    • Hi Kuldip – I’ve attached a sample of how this could be done. We just create a function to iterate over the level, then if any items are found to be not selected we know that all are not.

      HTH,
      Paul

      Sub MyControl()

      Debug.Print AllSelected(“Slicer_Customer_Geography”, 1)

      End Sub

      Private Function AllSelected(ByVal SlicerCacheName, ByVal SlicerCacheLevel) as Boolean

      Dim sC As SlicerCache
      Dim sL As SlicerCacheLevel
      Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Customer_Geography”)
      Set sL = sC.SlicerCacheLevels(SlicerCacheLevel)

      ‘ Lets assume that all are selected
      For i = 1 To sL.SlicerItems.Count
      If sL.SlicerItems(i).Selected = False Then
      AllSelected = False
      Exit Function
      End If
      Next

      AllSelected = True
      End Function

  18. Hi Paul,

    I have so nailed a great solution and works 95% of the time. As mentioned in my original question, I created the Pivot chart and through VBA reset the series colours, type etc. I subsequently used :

    Worksheet_PivotTableUpdate

    to trigger the reset_colours_series_type etcBUT the problem is that these are instances when the associated PIVIT Data changes but the inbuilt function Worksheet_PivotTableUpdate DOES NOT trigger the code to be run and the chart remain in bad configuration. Wierd indeed.

    Does anyone have any ideas why this triiger doesn’t trigger (so to speak).

    Thanks in advance Kuldip Mond.

  19. Hi Paul,

    Thanks so much for the great post. I was wondering if there is a way to write a macro that can iterate through a list of items in a slicer, selecting two items at a time. My end goal is to select every possible combination of two items within that slicer. I’ve looked everywhere for the answer to this, but can’t seem to find it. My data is linked to PowerPivot. Thanks!

  20. Hi Linda,

    Yes you can do it. The code below will iterate over every combination. Also note that since the order of selection is not important (ie (2005,2006) is the same as (2006,2005)) you only have to iterate over a reducing number of elements as you move down the list. this is why the second loop only moves from the current index position to the end of the list. I hope that makes sense?

    Sub PlayMe()

    Dim sc As SlicerCache
    Dim sl As SlicerCacheLevel
    Set sc = ActiveWorkbook.SlicerCaches(“Slicer_Date.Calendar_Year”)
    Set sl = sc.SlicerCacheLevels(1)

    For i = 1 To sl.Count

    For j = i + 1 To sl.Count

    Debug.Print sl.SlicerItems.Item(i).Name & “||” & sl.SlicerItems.Item(j).Name
    sc.VisibleSlicerItemsList = Array(sl.SlicerItems.Item(i).Name, sl.SlicerItems.Item(j).Name)

    Next j

    Next i

    End Sub

    • Yes, that’s exactly what I was looking for — thank you!! I do have one quick question though. I understand what you mean when you say that the second loop only moves from the current index position to the end of the list in order to avoid selecting duplicate combinations. How can I extend this idea to capture every possible combination of 3 items in a slicer? I thought that the For..Next statements would look like:

      For i = 1 To sl.Count

      For j = i + 1 To sl.Count

      For k = j + 1 To sl.Count

      sc.VisibleSlicerItemsList = Array(sl.SlicerItems.Item(i).Name, sl.SlicerItems.Item(j).Name, sl.SlicerItems.Item(k).Name)

      Next k

      Next k

      Next i

      However, this code generates duplicate combinations. What do I need to change?

      Thanks again for your help!

  21. No, I just realized I am not getting duplicates. I wrote a macro that records each combination of the selections your code makes on a worksheet, and I was originally seeing duplicates because of a mistake in the way I wrote that macro, not in the above code. I’ve fixed it now and it’s working exactly how I need it to. Thank you so much for taking the time to help me!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s