Slicers in Excel VBA (Visual Basic for Applications)

Slicers in Excel VBA (Visual Basic for Applications)

Slicers

To provide advanced level of filters and good visual over the data. Slicers can be built over Table or Pivot, hence you need to convert your data into a data source. In this article we will be referring slicers with List as data source.

Converting your existing data into table

  1. Select your data including headers and navigate to Insert Ribbon Tab
  2. Select Table command
  3. A popup will com to get user confirmation
  4. Say OK

Observation

If you notice, all the headers at row 1 got auto filter and formatting of data gets changed, which reflects that you are moving into right direction. Next we need to form slicers over given table. Select your table and on Insert Tab Click on Slicer command as shown below:

A popup comes with all the fields listed based on the data source, here user to make choices on all the fields on which you want to put filter. In this example I would be selecting all four fields to show the filter reactions as shown below:

On Clicking OK button each slicer turns into a separate deck which can be dragged or arranged as per your wish within sheet. If you notice Contact Number Deck it has only one Number it is because, the field Contact Number has repeated values and slicer deck removed the repeated values and gives single entry. Let do some code around to understand how VBA can put scripting power to manipulate slicers:

SlicerCaches

This is similar to PivotCache which represents a source. Each SlicerCaches can be accessed with the help of an integer “index” and it provide access to receptive slicers collection. Each slicer of Slicers collection has various properties and methods to manipulate slicer:

Code Example

Sub ManipulateSlicers()
    ThisWorkbook.SlicerCaches(1).Slicers(1).Caption = "This is my Slicer"
End Sub

Properties

  • ActiveItem
  • Application
  • Caption
  • ColumnWidth
  • Creator
  • DisableMoveResizeUI
  • DisplayHeader
  • Height
  • Left
  • Locked
  • Name
  • NumberOfColumns
  • Parent
  • RowHeight
  • Shape
  • SlicerCache
  • SlicerCacheLevel
  • Style
  • TimelineViewState
  • Top
  • Width

Methods

  • Add
  • Copy
  • Cut
  • Delete

Copy/Paste

Public Sub SlicersCopyPasteExample()
    ThisWorkbook.SlicerCaches(1).Slicers(1).Copy Sheet2.Paste
End Sub

Delete

Sub DeleteSlicers()
    ThisWorkbook.SlicerCaches(1).Slicers(1).Delete
End Sub

Add

ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.ListObjects("Table1"), "Address"). _
        Slicers.Add ActiveSheet, , "Address", "Address", 156.75, 394.5, 144, 198.75

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.