如何在出现Runtime Error 1004时弹出MsgBox并清除透视表筛选

问题描述 #

编写的VBA代码用于批量修改4个工作簿中透视表的"Root Account"字段筛选，当所有工作簿的透视表都存在目标Root Account值时代码正常运行，但只要任意工作簿的透视表中不存在该值，就会在.CurrentPage = rootAccount处抛出Runtime Error 1004。需求是：遇到这种情况时，先清除该字段的筛选，再弹出提示框告知该Root Account不存在。

原代码如下：

Option Explicit

Sub Account_Name()
    
    Workbooks.Open "C:\Book1.xlsm"
    Workbooks.Open "C:\Book2.xlsx"
    Workbooks.Open "C:\Book3.xlsx"
    Workbooks.Open "C:\Book4.xlsx"

    Dim workbookNames As Variant
    workbookNames = Array("Book1.xlsm", "Book2.xlsx", "Book3.xlsx", "Book4.xlsx")
    
    Dim i As Long
    For i = LBound(workbookNames) To UBound(workbookNames)
        
        Dim wb As Workbook
        Set wb = Workbooks(workbookNames(i))
        
        Dim ws As Worksheet
        Set ws = wb.Worksheets("Analysis")
        
        Dim rootAccount As String
        rootAccount = ws.Cells(1, 6).Value

        Dim pt As PivotTable
        For Each pt In ws.PivotTables
            With pt
                With .PivotFields("Root Account")
                    .ClearAllFilters
                    .CurrentPage = rootAccount
                End With
            End With
        Next pt
    Next i

End Sub

解决方案 #

可以通过提前检查目标值是否存在于透视表字段选项中，或者加入错误捕获机制来处理该问题。以下是两种可行的修改方案：

方案1：提前检查值是否存在（推荐） #

先遍历透视表字段的所有选项，确认目标值存在后再设置筛选，避免报错：

Option Explicit

Sub Account_Name()
    
    Workbooks.Open "C:\Book1.xlsm"
    Workbooks.Open "C:\Book2.xlsx"
    Workbooks.Open "C:\Book3.xlsx"
    Workbooks.Open "C:\Book4.xlsx"

    Dim workbookNames As Variant
    workbookNames = Array("Book1.xlsm", "Book2.xlsx", "Book3.xlsx", "Book4.xlsx")
    
    Dim i As Long
    For i = LBound(workbookNames) To UBound(workbookNames)
        
        Dim wb As Workbook
        Set wb = Workbooks(workbookNames(i))
        
        Dim ws As Worksheet
        Set ws = wb.Worksheets("Analysis")
        
        Dim rootAccount As String
        rootAccount = ws.Cells(1, 6).Value

        Dim pt As PivotTable
        Dim pf As PivotField
        Dim pi As PivotItem
        Dim accountExists As Boolean
        
        For Each pt In ws.PivotTables
            Set pf = pt.PivotFields("Root Account")
            ' 先清除所有筛选，满足需求
            pf.ClearAllFilters
            accountExists = False
            
            ' 遍历字段选项，检查目标值是否存在
            For Each pi In pf.PivotItems
                If pi.Value = rootAccount Then
                    accountExists = True
                    Exit For
                End If
            Next pi
            
            ' 根据检查结果处理
            If accountExists Then
                pf.CurrentPage = rootAccount
            Else
                MsgBox "工作簿 " & wb.Name & " 的透视表中不存在Root Account：" & rootAccount, vbExclamation, "提示"
            End If
        Next pt
    Next i

End Sub

方案2：错误捕获机制 #

通过临时禁用错误提示，捕获赋值时的报错，再进行处理：

Option Explicit

Sub Account_Name()
    
    Workbooks.Open "C:\Book1.xlsm"
    Workbooks.Open "C:\Book2.xlsx"
    Workbooks.Open "C:\Book3.xlsx"
    Workbooks.Open "C:\Book4.xlsx"

    Dim workbookNames As Variant
    workbookNames = Array("Book1.xlsm", "Book2.xlsx", "Book3.xlsx", "Book4.xlsx")
    
    Dim i As Long
    For i = LBound(workbookNames) To UBound(workbookNames)
        
        Dim wb As Workbook
        Set wb = Workbooks(workbookNames(i))
        
        Dim ws As Worksheet
        Set ws = wb.Worksheets("Analysis")
        
        Dim rootAccount As String
        rootAccount = ws.Cells(1, 6).Value

        Dim pt As PivotTable
        For Each pt In ws.PivotTables
            With pt.PivotFields("Root Account")
                .ClearAllFilters
                ' 临时禁用错误处理
                On Error Resume Next
                .CurrentPage = rootAccount
                ' 捕获1004错误并弹窗提示
                If Err.Number = 1004 Then
                    MsgBox "工作簿 " & wb.Name & " 的透视表中不存在Root Account：" & rootAccount, vbExclamation, "提示"
                End If
                ' 恢复默认错误处理
                On Error GoTo 0
            End With
        Next pt
    Next i

End Sub

关键说明 #

• 两种方案都会先执行ClearAllFilters，确保无论目标值是否存在，都先清除该字段的筛选。
• 方案1通过提前检查避免报错，逻辑更清晰；方案2通过错误捕获处理异常，代码更简洁。

内容的提问来源于stack exchange，提问作者Lal Sahab