Public Function seems to be slowing down all my subs

CatyH

Board Regular
Joined
Jun 27, 2017
Messages
64
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.

Code:
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,265
Office Version
365
Platform
Windows
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
Code:
Application.Calculation = xlManual
Code:
Application.Calculation = xlAutomatic
 

CatyH

Board Regular
Joined
Jun 27, 2017
Messages
64
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
Code:
Application.Calculation = xlManual
Code:
Application.Calculation = xlAutomatic
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,265
Office Version
365
Platform
Windows
In the subs that are running slowly
 

CatyH

Board Regular
Joined
Jun 27, 2017
Messages
64
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
 

Forum statistics

Threads
1,089,556
Messages
5,408,927
Members
403,243
Latest member
psabin8951

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top