Is there really no way to count the number of Visible Slicer Items in a Slicer connected to an OLAP data source?

Question

I'm trying to loop through all slicer items from three slicers and then call another sub within the inner most loop. The slicers are connected to an OLAP data source. For each slicer I'd only like it to loop through visible slicer items. I hide the items with no data after selecting an item from the outer slicer. If I use the following code, then it will loop through all slicer items, not just the visible ones:

'Begin Loop through Fund Name Slicer
i = 1

Do Until i = ActiveWorkbook.SlicerCaches(SC1).SlicerCacheLevels.Item.count + 1

    'select ith item in Fund Name Slicer
    ActiveWorkbook.SlicerCaches(SC1).VisibleSlicerItemsList = Array( _
    ActiveWorkbook.SlicerCaches(SC1).SlicerCacheLevels.Item.SlicerItems(i).Name)

    'hide items with no data in Scenario Name Slicer
    With ActiveWorkbook.SlicerCaches(SC2).Slicers(S2)
        .SlicerCacheLevel.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
        .SlicerCacheLevel.SortItems = xlSlicerSortDataSourceOrder
    End With

    'Begin Loop through Scenario Name Slicer
    j = 1

        Do Until j = ActiveWorkbook.SlicerCaches(SC2).SlicerCacheLevels.Item.count + 1

            'select jth item in Scenario Name Slicer
            ActiveWorkbook.SlicerCaches(SC2).VisibleSlicerItemsList = Array( _
            ActiveWorkbook.SlicerCaches(SC2).SlicerCacheLevels.Item.SlicerItems(j).Name)

            'hide items with no data in Override Set Name Slicer
            With ActiveWorkbook.SlicerCaches(SC3).Slicers(S3)
                .SlicerCacheLevel.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData
                .SlicerCacheLevel.SortItems = xlSlicerSortDataSourceOrder
            End With

            'Begin Loop through Override Set Name Slicer
            k = 1

                Do Until k = ActiveWorkbook.SlicerCaches(SC3).SlicerCacheLevels.Item.count + 1

                    'Select kth item in Override Set Name Slicer
                    ActiveWorkbook.SlicerCaches(SC3).VisibleSlicerItemsList = Array( _
                    ActiveWorkbook.SlicerCaches(SC3).SlicerCacheLevels.Item.SlicerItems(k).Name)

                    'Call sub that copies and pastes summary values
                    Call SelectedComboOnly

                    k = k + 1

                Loop

            j = j + 1

        Loop

    i = i + 1

Loop

I tried to replace the Do Until statements with something like:

Do Until  i = ActiveWorkbook.SlicerCaches(SC1).VisibleSlicerItems.count + 1

but this throws a run time error. I found here that "Attempting to access the VisibleSlicerItems property for slicers that are connected to an OLAP data source (SlicerCache.OLAP = True) generates a run-time error." Is there really no way to access the number of visible slicer items here? Or should I try something completely different? My macro does what it's supposed to do, but it takes about 25 minutes. I'm just trying to run through only visible slicer items to optimize the code. This would take the combinations that it has to run through from 1,458 to 84. I already have screen updating off.

Answer 1

Sorry, for the late answer, but I just want to share below solution that worked for me:

UBound(ThisWorkbook.SlicerCaches(SC1).SlicerCacheLevels(1).VisibleSlicerItemsList)

EDIT:

The .VisibleSlicerItemsList property returns an array so You can't use the .count property on It, like You would do on collections. In order to get the length of an array, use the function UBound (see here).