Filter OLAP pivot table with VBA

Question

I have access to our database through an OLAP pivottable. I often want to filter based on multiple client numbers (which are not continuous). The client list is so long, however, that trying to scroll through and manually select clients is out of the question. I am hoping to solve the issue by adding a slicer and using a macro to select visible slicer items (though I'm open to other ideas). I'm hoping to have a worksheet where I can add a list of client numbers I would like to filter on which the macro applies to the slicer when run.

When I recorded a macro, it produced the following code:

ActiveWorkbook.SlicerCaches("Slicer_Client_ID").VisibleSlicerItemsList = Array _
        ( _
        "[DimMatter].[CurrClientNumber].&[090204]", _
        "[DimMatter].[CurrClientNumber].&[092223]", _
        "[DimMatter].[CurrClientNumber].&[102028]", _
        "[DimMatter].[CurrClientNumber].&[103118]", _
        "[DimMatter].[CurrClientNumber].&[104382]")

So I wrote code to create an array that I hope to feed into the visible slicer items list:

    Set wb = Workbooks("workbook.xlsm")
    Set iws = wb.Sheets("Client List")
    Set tws = wb.Sheets("Table_Sheet")

    
    s_count = iws.Range("A1").End(xlDown).Row
    
    'initialize the array
    ReDim s_array(s_count - 2)
    'populate the array
    For i = 2 To s_count
        arr_itm = iws.Range("A1").Offset(i - 1, 0).Value
        s_array(i - 2) = "[DimMatter].[CurrClientNumber].&[" & arr_itm & "]"
    Next i

    wb.SlicerCaches("Slicer_Client_ID").VisibleSlicerItemsList = Array(s_array)

I'm stuck on how to actually input my array to the slicer items list. When I run the code above I get an error that reads: "Run-time error '1004': The item could not be found in the OLAP Cube".

I'm new to working with both arrays and OLAP cubes, so would appreciate any feedback.

Answer 1

The Array function takes a list of values and turns them into an array. Using Array(s_array) gives you a one-element array where that one element is itself an array that has (s_count - 1) elements - i.e. from zero to (scount - 2). In other words, you have created a two-dimensional array with bounds (0 To 0, 0 To s_count - 2).

To solve this, just use s_array directly and leave out the Array(...) part:

wb.SlicerCaches("Slicer_Client_ID").VisibleSlicerItemsList = s_array

Note: answer assumes that the default Option Base 0 is in effect and that arrays start with element 0