3
\$\begingroup\$

I've written out some VBA code that sets various filters onto pivottables. The problem I am experiencing is that it is extremely slow and bogs down my system heavily.

I've tried implementing various things to speed up the code, but nothing really has that much of an effect.

The reason I am doing it this way is the filters need to be dynamic and I am using multiple data sources, so just using a single slicer doesn't work in my case.

Here is my code:

 Private Sub Worksheet_Calculate()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    ActiveSheet.DisplayPageBreaks = False


    Dim DivRef, RegRef, DistRef, ZoneRef As String
    DivRef = Sheet5.Range("AH6").Value
    RegRef = Sheet5.Range("AH7").Value
    DistRef = Sheet5.Range("AH8").Value
    ZoneRef = Sheet5.Range("AN4").Value

With Sheet5.PivotTables("PivotTable21")
    .ManualUpdate = True
    .PivotFields("Division2").CurrentPage = DivRef
    .PivotFields("Region2").CurrentPage = RegRef
    .PivotFields("District2").CurrentPage = DistRef
    .ManualUpdate = False
End With


With Sheet5.PivotTables("PivotTable9")
    .ManualUpdate = True
    .PivotFields("Division2").CurrentPage = DivRef
    .PivotFields("Region2").CurrentPage = RegRef
    .PivotFields("District2").CurrentPage = DistRef
    .ManualUpdate = False
  End With

     Application.EnableEvents = True
     Application.ScreenUpdating = True
     Application.Calculation = xlCalculationAutomatic

    End Sub

In reality, there are a number of pivottables that are being filtered, but for the sake of this post, I am only posting PivotTable21 and PivotTable9's filters, though it is all more or less the same code.

I've been told that part of the problem might be that I am using calculated fields in my pivottables, and that may bog down some of the code as well.

Does anyone have any tips for speeding this code up?

\$\endgroup\$
2
  • \$\begingroup\$ You need to dim each variable. Dim DivRef, RegRef, DistRef, ZoneRef As String only ZoneRef is String the rest are Variant`. \$\endgroup\$
    – IvenBach
    Commented May 8, 2018 at 20:51
  • \$\begingroup\$ I may have done this wrong, so let me know... DivRef, RegRef, etc will refer to a String, like "West" or "East", etc. I'm guessing I can remove the .value at the end of the reference, though I'm not sure if this will improve speed at all. Should I still set them to Variant? \$\endgroup\$
    – ranopano
    Commented May 8, 2018 at 20:54

2 Answers 2

1
\$\begingroup\$

I find that when you set PivotTable.ManualUpdate = True, Excel seems to often set that back to False for many of the changes you can make to PivotTables. It does depend on the change, but I think changing PivotFields is one of those things that triggers this.

So you could try going to every PivotField change and resetting ManualUpdate to True after it. Such as this:

With Sheet5.PivotTables("PivotTable21")
    .ManualUpdate = True
    .PivotFields("Division2").CurrentPage = DivRef
    .ManualUpdate = True
    .PivotFields("Region2").CurrentPage = RegRef
    .ManualUpdate = True
    .PivotFields("District2").CurrentPage = DistRef
    .ManualUpdate = False
End With

I know it seems silly, but I've seen this work.

\$\endgroup\$
2
  • \$\begingroup\$ Hmmm interesting. I wouldn't have thought of this. I will give it a try and see if it speeds things up a bit. Thanks! \$\endgroup\$
    – ranopano
    Commented May 8, 2018 at 21:33
  • \$\begingroup\$ You could test this theory by doing Debug.Print to the Immediate windows and seeing if the ManualUpdate for the PivotTable in question is still set to True. Prior to updating it everywhere. \$\endgroup\$
    – PerryJ
    Commented May 8, 2018 at 22:22
1
\$\begingroup\$

I think a problem you might have is that you have this in Worksheet_Calculate and you set your Application.Calculation to automatic -

Private Sub Worksheet_Calculate()
...
Application.Calculation = xlCalculationAutomatic
End Sub

You are applying all of these filters every time anything calculates on whatever sheet this is on. Do you need that? I doubt it - maybe set your sheet to manual calculation or move the event out of the calculate event and set up a button or something.


When you declare variables, you need to give them all a type -

Dim DivRef, RegRef, DistRef, ZoneRef As String

This declares DivRef, RegRef and DistRef as Variant and only ZoneRef as string. Variants can slow down your code, but I doubt that's really the problem here. It's good practice, however, to always type them. You need to type each one e.g.

Dim divRef as String, regRef as String, distRef as String, zoneRef as String

Or more appropriately-

Dim division As String
Dim region As String
Dim district As String

I don't see you using zone so I didn't include it.

I also changed the naming, you want your names to be clear and concise.

\$\endgroup\$
1
  • \$\begingroup\$ Really appreciate the comment! To answer your question, yes, I am applying these filters every time something calculates on the sheet. I'm using excel 2010 without PowerPivot. The issue is that I have 5-6 different data sources and I need all the data sources to be controlled by 1 slicer. Anytime a slicer item is clicked, RegRef, DistRef, etc, will change based on the slicer selection and the VBA filters will filter the pivot table, even if it's not connected to the original slicer. \$\endgroup\$
    – ranopano
    Commented May 9, 2018 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.