I'm trying to write some VBA code to count all available items in a slicer that is connected to an ODBC connection. Below is what I currently have but the result is always 1 when I'm expecting 8 as there are 8 available items to choose from in the slicer. Any ideas?

Public Function GetSlicerItemsCount(SlicerName As String) As Integer

    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Integer
    On Error Resume Next
    Application.Volatile
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            lCt = lCt + 1
        Next
        GetSlicerItemsCount = lCt
    Else
        GetSlicerItemsCount = 0
    End If

End Function

Hi,

This is the forum to discuss questions and feedback for Microsoft Excel, your issue is related to macro, I recommend you post the question to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

George Zhao
TechNet Community Support