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

userMFe2 0
Viewed 29

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.

olap-cube pivot-table vba excel
1 Answers

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)
BigBen0
Related