[SOLVED] Change Slicer Items Using Values in Array

Change Slicer Items Using Values in Array - for an OLAP cube pivot table

Building on this question, I have a workbook with a pivot table from an OLAP cube. I need to be able to change a slicer for this pivot using the values in a selected range of cells. I can make the solution provided in the linked question work for one value, but I get a type mismatch error when trying to use an array.

This is the code I'm using. The [Branch Name] is the field I'm trying to set.

Dim sel_Array As Variant
Dim rng As Range

'Set rng = Selection

sel_Array = Selection.Value

ActiveWorkbook.SlicerCaches("Slicer_Branch_Name").VisibleSlicerItemsList = Array("[Dim Location].[Branch Name].&[" & sel_Array & "]")

I also tried using the rng variable to put "rng.Value" into the VisibleSlicerItemsList, but that didn't work either.

Solution

You need to create an array like the following, which will handle both single-cell and multi-cell inputs:

Private Function VisibleItemsList(ByVal rng As Range) As Variant
   Const mdx As String = "[Dim Location].[Branch Name].&["
   
   If rng.CountLarge = 1 Then
       VisibleItemsList = Array(mdx & rng.Value & "]")
       Exit Function
   End If
    
   Dim arr() As Variant
   ReDim arr(0 to rng.Cells.CountLarge - 1)

   Dim data() As Variant
   data = rng.Value

   Dim i As Long, j As Long
   For i = LBound(data, 1) to UBound(data, 1)
       For j = LBound(data, 2) to Ubound(data, 2)
           Dim counter As Long
           arr(counter) = mdx & data(i, j) & "]"
           counter = counter + 1
       Next
   Next
   VisibleItemsList = arr
End Function

Call it like this:

ActiveWorkbook.SlicerCaches("Slicer_Branch_Name") _ 
   .VisibleSlicerItemsList = VisibleItemsList(Selection)