We help IT Professionals succeed at work.
Get Started

Anyway to Speed Up this Code?

751 Views
Last Modified: 2015-07-24
Hi Experts,
I'm doing the following in my workbook.
1. Using OLAP Slicers to Filter data that resides in PowerPivot. Less than 500 records worth of data.
2. Passing the Selections in these OLAP-Slicers back to some Non-OLAP-Slicers (via the code below) which enables me to "ShowDetails" if using multiple filtering criteria. The Non-OLAP-Slicer are connected to a regular Pivot Table that summarizes a Regular Excel Table. FYI Having the Non-OLAP Slicers connect to a Pivoted Summary of the Excel Table ran faster than having the OLAP slicer selections passed directly to slicers connected to the Excel Table.
3. It takes about 3-5 seconds to pass any selection made in the OLAP Slicers to the Non-OLAP slicers (I am "okay" with this)
4. I'm using the 2nd bit of code below to reset all of the OLAP SlicerCaches on the their respective worksheet; this obviously triggers the Pivot table change event and as a consequence takes around 21-25seconds to run.

I'd like to improve the performance of 4, and if possible 3. Thus far, I've tried to create Calculated Fields in PowerPivot and put those into the PivotTable(s) that are being refreshed when the PivotChange event occurs (as opposed to, having the PivotTable itself calculate Sums, Averages, etc...) This has helped some. Is there anything else I can do?

OLAP to Non-OLAP Slicer Sync Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
Dim scOLAP As SlicerCache
Dim scList As SlicerCache
Dim sO As Slicer
Dim sL As Slicer
Dim si As SlicerItem
Dim i As Integer
Dim svalue As String
Dim ar() As String

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_RegionCode")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_RegionCode2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_OpLocationCode")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_OpLocationCode2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_AvailableAsset")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_AvailableAsset2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_StratMissionSet")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_StratMissionSet2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_TOMISTypeClean")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_TOMISTypeClean2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_AssetSuitability")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_AssetSuitability2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_PastPerformance")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_PastPerformance2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_Authorization")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_Authorization2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

Set scOLAP = ActiveWorkbook.SlicerCaches("Slicer_Priority")
Set scList = ActiveWorkbook.SlicerCaches("Slicer_Priority2")

scList.ClearManualFilter

Set sO = scOLAP.Slicers(1)
Set sL = scList.Slicers(1)
ReDim ar(UBound(scOLAP.VisibleSlicerItemsList))
For i = 1 To UBound(scOLAP.VisibleSlicerItemsList)
svalue = Replace(Replace(scOLAP.VisibleSlicerItemsList(i), "[TablePPCleanUORC].[RegionCode].&[", ""), "]", "")
ar(i) = svalue
Next
For Each si In scList.SlicerItems
 If UBound(Filter(ar, si.SourceName)) < 0 Then
  si.Selected = False
 End If
Next

  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  
End Sub

Open in new window

Reset Slicers on Active Worksheet Code:
Public Sub ResetSlicers(ws As Worksheet)
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
    Dim sc As SlicerCache
    Dim scs As SlicerCaches
    Dim slice As Slicer

    Set scs = ws.Parent.SlicerCaches

    If Not scs Is Nothing Then
        For Each sc In scs
            For Each slice In sc.Slicers
                If slice.Shape.Parent Is ws Then
                    sc.ClearManualFilter
                    Exit For 'unnecessary to check the other slicers of the slicer cache
                End If
            Next slice
        Next sc
    End If
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Open in new window

Comment
Watch Question
Partner
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 20 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE