VBA Function to read values from slicer

[Guinaba]

Hi guys,

The function below work perfectly in slices reading the data from Excel table, however when I started using PowerPivot getting the data from there, the same code doesnt work giving the error:

Run-time error '1004'
Application-defined or object-defined error

Specifically on the row below:

It seems that there is no cache memory available? Any suggestions?

 MyArr = ArrayListOfSelectedAndVisibleSlicerItems("Slicer_Product_Group")
    'now variable MyArr keeps all items in an array
    'Declare the integer to store the number of rows

   Dim iRw As Integer
   'loop through the values in the array
   For iRw = LBound(MyArr) To UBound(MyArr)
   'populate a different range with the data
      Cells(iRw + 1, 45).Value = MyArr(iRw)
      Cells(1, 44) = CountProducers
   Next iRw
End Sub

Public Function ArrayListOfSelectedAndVisibleSlicerItems(Slicer_Product_Group As String) As Variant
'    'This function returns an array of the limited set of items in Slicer A
    'Limitation is due to both:
    '(1) direct selection of items by user in slicer A
    '(2) selection of items in slicer B which in consequence limits the number of items in slicer A

    Dim ShortList() As Variant
    Dim i As Integer: i = 0 'for iterate`

    Dim sC As SlicerCache
    Dim sI As SlicerItem 'for iterate

    Set sC = ThisWorkbook.Application.ActiveWorkbook.SlicerCaches(Slicer_Product_Group)

    For Each sI In sC.SlicerItems
        If sI.Selected = True And sI.HasData = True Then 'Here is the condition!!!
            'Debug.Print sI.Name
            ReDim Preserve ShortList(i)
            ShortList(i) = sI.Name
            Exit For
            i = i + 1
        End If
    Next sI
    ArrayListOfSelectedAndVisibleSlicerItems = ShortList
End Function

 

[ExcelToDAX]

do you still only have Excel 2016? Or do you have Excel 365? If you have 365, you can check out this link that utilizes the CUBERANKEDMEMBER and CUBESET functions. The SME Sergei Baklan created a LAMBDA function that can output multiple values.

 

[Guinaba]

do you still only have Excel 2016? Or do you have Excel 365? If you have 365, you can check out this link that utilizes the CUBERANKEDMEMBER and CUBESET functions. The SME Sergei Baklan created a LAMBDA function that can output multiple values.

View attachment 103607

Thanks! That is great @Sergei