Public Function seems to be slowing down all my subs

[CatyH]

Thought it was my imagination... but... I have two public functions that I've defined which appear to be running as part of EVERY sub, regardless of if they are needed or not. [The public functions are called from my pivot table pages and are supposed to report back on which slicer items were selected (or NOT selected).]

Other subs seemed slower... and sure enough, when I tried to F8-step my way through a sub I got caught up in something like an infinite loop through the Public Slicer function (code below).

My question - can I just make this Public function Private since I only really need it on three certain pages? Or is there something else I don't understand about public/private and user-defined-functions?

thanks in advance!


What it does:
This function is referenced in about 12 places on 3 tabs calling for a list of selected slicer options.

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 selected"
            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

 

[Yongle]

Application.Volatile makes the function run whenever your sub is writing to a cell
I cannot test this (away from PC for several days) but you could try setting calculations to manual at the beginning of your sub and reset to automatic at the end of your sub

Application.Calculation = xlManual

Application.Calculation = xlAutomatic

 

[CatyH]

Thank you for your reply! Not to be completely daft, but... do you mean i should adjust calculation mode (manual/auto) in the "GetSlicerItems" sub? OR adjust the manual/auto in the OTHER subs that are slower?

Maybe you mean in the GetSlicerItems... and then it wouldn't run all the time?

I was thinking maybe I could limit the UDF to just the pages that actually need it, somehow. Maybe that's with "private" versus "public"? would that work, do you think?

Application.Volatile makes the function run whenever your sub is writing to a cell
I cannot test this (away from PC for several days) but you could try setting calculations to manual at the beginning of your sub and reset to automatic at the end of your sub

Application.Calculation = xlManual

Application.Calculation = xlAutomatic

 

[Yongle]

In the subs that are running slowly

 

[CatyH]

Thanks - it seems to have knocked processing time down from 7:45 to 7:00 for a 14% time-saving on the big-sub-run... I'll test it out on all the other 50-ish subs, too... Thank you!

In the subs that are running slowly