Here is some VBA code that can help you automate working with a slicer in Excel. Slicers, as you would know, are the latest introduction to Excel. As I mentioned in my previous post, they can also be programattically controlled using simple VBA code. Let’s see how.
Slicer Creation using VBA – Basic Steps
Let’s look at the slicer hierarchy – a workbook has slicer caches (accessed the SlicerCaches object), each slicer cache in turn having multiple slicers (one for each pivot table field) and finally each slicer having one slicer object.
1 2 3 | Set My_Slicer_Caches = ActiveWorkbook.SlicerCaches Set My_Slicers = Add(Source, SourceField, [Name]) Set My_Slicer = Add(SlicerDestination, [Level],[Name],[Caption],[Top],[Left],[Width],[Height]) |
where
My_Slicer_Caches is an object of type SlicerCaches
My_Slicers is an object of type Slicers
My_Slicer is an object of type Slicer
Source is usually the name of the pivot table from which the slicer is being created.
SourceField is the name of the column header of the field for which the slicer is being created.
Name, Caption, Top, Left, Width and Height are used to specify the other parts of the hierarchy.
Imagine a pivot table that shows the sales of a number of product categories in various regions. We will first construct a slicer based on the “Region” field and then gradually move into carrying out other operations in it using VBA. You can extend the same code to any other field of the pivot table – visible or otherwise.
A word of caution though – If you already have already created a slicer of a particular field for one specific pivot table, you may find it difficult to run another code snippet which creates another slicer for the same field without deleting the first one. In such cases you may frequently find the application throwing the dreaded Run-time error ‘1004’: Application-defined or object-defined error. (More about this at the end of the post).
So every time you have to run the another code snippet, you may want to go back to the excel spreadsheet and manually delete the slicer. If the error still persists, goto the slicer button, explicitly create the slicer for that field and then go back, right click the slicer and remove it. The easier option is the delete the slicer at the end of every VBA code snippet by inserting something magical like slicer.delete
So let’s look at how we can create a simple slicer using VBA. In this case, we want to create a slicer based on the “Region” field. So we catch hold of the pivot table, tell the application that we want to create a slicer for the “Region” field and then specify the specific coordinates and the shape in which we want it to be presented to us 🙂
How to Create a Slicer Using VBA
1 2 3 4 5 6 7 8 9 10 | Sub create_slicer() Dim i As SlicerCaches Dim j As Slicers Dim k As Slicer Set i = ActiveWorkbook.SlicerCaches Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200) Msgbox "Created Slicer" End Sub |
where
Region is the name field that we want to build the slicer for.
My_Region is the name that has been assigned to the slicers object.
Turn Slicer Fields On and Off Using VBA
Once you have created the slicer, the very next thing you would probably want to do with it is to turn on and off various items within the slicer. In our case, the slicer for the “Region” field has four unique items – East, West, North and South.
Let’s see how we can turn on (and off) the item “West” within that pivot field using VBA.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | Sub turn_off_on_slicer_fiekd() Dim i As SlicerCaches Dim j As Slicers Dim k As Slicer Set i = ActiveWorkbook.SlicerCaches Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200) i("Region").SlicerItems("West").Selected = False 'You can also use k.SlicerCache.SlicerItems("West").Selected = False 'Or k.SlicerCache.SlicerItems(1).Selected = False Msgbox "Turned off WEST" i("Region").SlicerItems("West").Selected = True 'Or k.SlicerCache.SlicerItems("West").Selected = True Msgbox "Turned on WEST" End Sub |
Slicer Name and Caption Change Using VBA
You can also modify the slicer name and caption. It makes great sense to provide descriptive and unique captions to slicers when you have more than one slicer based on the same field for multiple pivot tables.
1 2 3 4 5 6 7 8 9 10 11 12 | Sub change_name_caption_slicer() Dim i As SlicerCaches Dim j As Slicers Dim k As Slicer Set i = ActiveWorkbook.SlicerCaches Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200) k.Name = "Slicer_Name" 'Or use j(1).Name = "Slicer_Name" k.Caption = "My Caption" 'Or use j(1).Caption = "My Caption" Msgbox "Changed slicer name and caption" End Sub |
Delete a Slicer Using VBA
So what to do when the slicer has done its job? Dispose it. How. Simple. Slicer.delete
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | Sub delete_slicer() Dim i As SlicerCaches Dim j As Slicers Dim k As Slicer Set i = ActiveWorkbook.SlicerCaches Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200) Msgbox "Created Slicer" k.Delete 'OR 'j("My_Region").Delete Msgbox "Deleted Slicer" End Sub |
Change Slicer Position, Height, Width and Style Using VBA
Other things like the height, width, top and left coordinates can be changed – either at the time of creation of the slicer or at runtime. It is also possible to adjust the height and width of the slicer items. In the example below, we first create a slicer, then move the slicer around by changing its top and left coordinates and then finally adjust the slicer row height and the column width.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | Sub change_slicer_look_feel() Dim i As SlicerCaches Dim j As Slicers Dim k As Slicer Set i = ActiveWorkbook.SlicerCaches Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region").Slicers Set k = j.Add(ActiveSheet, , "My_Region", "Region", 0, 0, 200, 200) ' Specify the dimensions here MsgBox "Created Slicer" k.Top = 200 k.Left = 200 'OR 'j("My_Region").Top = 200 'j("My_Region").Left = 200 MsgBox "Moved Slicer" k.Shape.ScaleWidth 0.4, msoFalse, msoScaleFromTopLeft k.Shape.ScaleHeight 0.6, msoFalse, msoScaleFromTopLeft MsgBox "Changed Slicers Row Height and Width" k.RowHeight = 8.4 k.ColumnWidth = 358.4 'OR use the 'j("My_Region") syntax as shown above MsgBox "Changed Slicer Field Row Height and Width" k.Style = "SlicerStyleLight3" MsgBox "Changed Slicer Color" End Sub |
A few more important things. You can sort, toogle header display, filter setting and change other settings of the slicer using VBA. Here is a small code snippet.
Create Slicer Settings with VBA
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | Sub change_slicer_settings() Dim i As SlicerCaches Dim j As Slicers Dim k As Slicer Set i = ActiveWorkbook.SlicerCaches Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "Region").Slicers Set k = j.Add(ActiveSheet, , "Region", "Region", 0, 0, 200, 200) k.Caption = "Amazing Slicer" k.DisplayHeader = True k.SlicerCache.CrossFilterType = xlSlicerNoCrossFilter ' OR xlSlicerCrossFilterShowItemsWithNoData / xlSlicerCrossFilterShowItemsWithDataAtTop k.SlicerCache.SortItems = xlSlicerSortDescending 'OR xlSlicerSortAscending k.SlicerCache.SortUsingCustomLists = True k.SlicerCache.ShowAllItems = False End Sub |
Possible Issues with Slicer Implementation – Creating Duplicate Slicers with VBA
As a member of the shape family, it is entirely possible to duplicate the slicer object. However when one tries to create two slicer objects on the same pivot field (from the same pivot table), the application seems to throw up the dreaded VBA “Run-time error ‘1004’: Application-defined or object-defined error”.
For example, the VBA code shown below is perfectly valid and work fine:
1 2 3 4 5 6 | Sub create_duplicate_slicer() Set i = ActiveWorkbook.SlicerCaches Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region1").Slicers Set k = j.Add(ActiveSheet, , "My_Region1", "Region", 0, 0, 200, 200) ActiveSheet.Shapes.Range("My_Region1").Duplicate.Select End Sub |
but if you try to create two slicer objects, that causes an error. We first try creating a slicer on the same slicers object and give each one a separate name (“Region1” and “Region2”). This doesn’t work and the code generates a VBA “Run-time error ‘5’: Invalid procedure call or argument”.
1 2 3 4 5 6 | Sub create_duplicate_slicer() Set i = ActiveWorkbook.SlicerCaches Set j = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region1").Slicers Set k1 = j.Add(ActiveSheet, , "My_Region1", "Region1", 0, 0, 200, 200) Set k2 = j.Add(ActiveSheet, , "My_Region1", "Region2", 0, 0, 200, 200) End Sub |
We then try to create each slicer from a two different slicers object The following code generates a VBA “Run-time error ‘1004’: Application-defined or object-defined error”
1 2 3 4 5 6 7 | Sub create_duplicate_slicer() Set i = ActiveWorkbook.SlicerCaches Set j1 = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region1").Slicers Set j2 = i.Add(ActiveSheet.PivotTables(1), "Region", "My_Region2").Slicers Set k1 = j1.Add(ActiveSheet, , "My_Region1", "Region1", 0, 0, 200, 200) Set k2 = j2.Add(ActiveSheet, , "My_Region2", "Region2", 0, 0, 200, 200) End Sub |
I am not too sure whether that’s a gap in my understanding of the slicer dynamics or a bug – though I think it would likely be the former.
You can download the worksheet with vba code for slicers here or click on the button below:
The information is great, I just would like to manipulate slicers that already exist on the sheet rather than create them on the fly. Also I don’t know how to call VBA code when the sheet opens.
Regards
Andy
March 21st, 2011 at 9:23 pmI downloaded your workbook and stepped through the code. Thanks for that…big help.
However, I have a question unrelated to the slicers.
How did you hide gridlines on the first tab? I see in the View tab that Gridlines are checked and this is a Normal view. I didn’t realize there was another way to do that.
Thanks,
Craig
May 22nd, 2011 at 8:31 amPretty cool stuff.
January 10th, 2013 at 6:22 pmThis rocked. Thank you very much!
July 31st, 2013 at 12:17 pmI have a template with 4 slicers. After updating the source data, I noticed that the slicers aren’t updated. for example: in my slicer market, there should be one more value “jumbo groep”.
it is in the source data, i’ve refresh pivots using vba: thisworkbook.refreshall but it doesn’t seem to refresh the slicers. Which code do I have to use to refresh my slicers?
December 2nd, 2013 at 4:38 amThank you for the code. I really wanted a floating slicer that moved down the worksheet as I scrolled. Alternatively…….. Slicer is called “Years” and have column “A” blank. To move the slicer to the required row, double-click on the row number in column “A”.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range(“A:A”)) Is Nothing Then
Dim R As Range
Set R = Target
With ActiveSheet.Shapes(“Years”)
.Top = R.Top
End With
End If
End Sub
The above code was added to the worksheet that contained the pivot table.
Regards
November 10th, 2014 at 4:38 pmClive
Have you ever considered writing an ebook or guest authoring on other sites?
January 19th, 2021 at 11:08 amI have a blog based upon on the same subjects you discuss and would really like to have you share
some stories/information. I know my subscribers would
enjoy your work. If you’re even remotely interested, feel free to shoot me
an e-mail.