首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >如何用切片器加速这个VBA代码?

如何用切片器加速这个VBA代码?
EN

Stack Overflow用户
提问于 2017-02-16 20:57:41
回答 1查看 1.6K关注 0票数 0

我有一个电子表格,它有七个表(tbl_1,tbl_2 ...tbl_7),每个表都由自己的切片机控制。每个切割机有六个按钮(10,20,30,40,50,60)。我使用下面的代码在每个切片机上选择一个团队,然后为每个团队/切片器设置创建一个PDF。到目前为止,代码运行所需的时间从5-7分钟不等。任何帮助都是非常感谢的。

代码语言:javascript
复制
Sub SlicerTeam()
Dim wb As Workbook
Dim sc As SlicerCache
Dim si As SlicerItem

On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ThisWorkbook

For x = 1 To 6
    For i = 1 To 7
    Set sc = wb.SlicerCaches("tbl_" & i)
        sc.ClearAllFilters
        For Each si In sc.VisibleSlicerItems
            Set si = sc.SlicerItems(si.Name)
                If Not si Is Nothing Then
                    If si.Name = x * 10 Then
                        si.Selected = True
                    Else
                        si.Selected = False
                    End If
                Else
                    si.Selected = False
                End If
        Next si

    Next i
Call PDFCreate
Next x

exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler

End Sub
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-02-17 00:13:40

假设这些切片器正在切割枢轴表,请尝试下面的代码。它可能有助于加快速度,这取决于您的PivotTables有多大。

代码语言:javascript
复制
Sub SlicerTeam()
Dim wb As Workbook
Dim sc As SlicerCache
Dim si As SlicerItem

dim pt as PivotTable

On Error GoTo errHandler
Application.ScreenUpdating = False
Application.EnableEvents = False

Set wb = ThisWorkbook

For Each pt in wb.PivotTables
    pt.ManualUpdate = True
Next

For x = 1 To 6
    For i = 1 To 7
    Set sc = wb.SlicerCaches("tbl_" & i)
        sc.ClearAllFilters
        For Each si In sc.VisibleSlicerItems
            Set si = sc.SlicerItems(si.Name)
                If Not si Is Nothing Then
                    If si.Name = x * 10 Then
                        si.Selected = True
                    Else
                        si.Selected = False
                    End If
                Else
                    si.Selected = False
                End If
        Next si

    Next i

    For Each pt in wb.PivotTables
        pt.ManualUpdate = True
    Next


    Call PDFCreate
Next x

exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox ("Error in updating slicer filters.")
Resume exitHandler

End Sub
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/42284468

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档