如果出现错误1004，如何显示MsgBox

Question

我创建此代码是为了更改4个工作簿中的数据透视字段。

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

如果在所有4个工作簿中都找到根帐户，则该代码有效。

如果根帐户在任何工作簿中都不可用，则会给出

运行时错误1004

在.CurrentPage = rootAccount上。

是否可以从根帐户中清除筛选器并显示此根帐户不可用的消息框？

Answer 1

On Error Resume NextWhen Single Statement

' Declare a Long variable to hold the error number.
Dim cpErrNum As Long
With .PivotFields("Root Account")
.ClearAllFilters

' Attempt to...
On Error Resume Next ' defer error trapping (ignore errors)
.CurrentPage = rootAccount ' ... do this.
cpErrNum = Err.Number ' store the error number in the variable
On Error GoTo 0 ' disable error handling
If cpErrNum <> 0 Then ' an error occurred
MsgBox "Root Account is not available.", vbExclamation
'Else ' no error occurred i.e. 'cpErrNum = 0'; do nothing
End If
End With

• 请注意，对象的处理方式不同，即不需要Long变量，并检查对象是否未被引用，例如If obj Is Nothing Then