Slicer Data Extraction, Public Function

[umplebyuk]

Hi Guys,

Re Slicer Data Extraction I use the below Public Function, followed by the formula "=GetSelectedSlicerItems([@Description])".

This works exactly as required, however every time I run other Macros, it loops through the Public Function constantly, slowing the Macro down.

A; Is there an alternative to the below Public Function that will produce the same outcome?

B; Is there a way to keep the same Public Function in place but only run when required, rather than constantly?

Thank you in advance,

James

Public Function GetSelectedSlicerItems(SlicerName As String) As String
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Application.Volatile
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "All_Items"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
        Else
            GetSelectedSlicerItems = "No items selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

 

[Worf]

Hi James

If this is an option for you, consider changing calculation to manual at the formula tab.
If calculations are to remain automatic, the slicer code can be attached to a button and would run only when clicked.