Excel VBA切换数据透视表列字段报1004错无法设置Orientation属性

• 基于含多维度变量的大型数据集制作Excel内置仪表盘，需在保留其余配置不变的前提下，逐个切换数据透视表的Column字段（例如将列维度的Q1替换为Q2）
• 仪表盘使用人员无透视表操作基础，需通过宏实现一键切换功能，无需手动调整透视表字段：数据源为200题李克特量表调研数据，透视表列维度对应单道题的统计结果，点击按钮即可切换当前展示的调研题目
• 套用公开找到的VBA代码运行时，执行到pf.Orientation = xlHidden行抛出如下错误：

Run-time error '1004': Unable to set the Orientation property of the PivotField class.

Sub Add_Column_Field()
'Remove all Column fields and add the Column field to the pivot table.
'The field is determined by the button text that calls the macro.

Dim pt As PivotTable
Dim pf As PivotField
Dim sField As String

'Set variables
Set pt = ActiveSheet.PivotTables(1)
sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
  
'Remove existing fields
For Each pf In pt.ColumnFields
    pf.Orientation = xlHidden
Next pf
  
'Add field that button was clicked for
pt.PivotFields(sField).Orientation = xlColumnField

End Sub

• 遍历pt.ColumnFields集合的过程中直接修改字段Orientation属性，会导致集合本身在遍历周期内发生动态变更，触发集合访问冲突
• Excel透视表默认开启“值字段显示在列区域”配置时，值区域的字段也会被识别为ColumnFields集合的成员，这类字段本身不支持直接设置为xlHidden状态，强制操作就会抛出1004错误

修正逻辑避开上述两个触发点即可：

1. 操作透视表前先开启手动更新模式，避免操作过程中反复重算触发异常
2. 不直接遍历ColumnFields集合执行删除，先将所有现有列字段的名称存入独立数组，遍历数组完成字段隐藏操作，规避集合动态变化问题
3. 隐藏字段前先判断字段类型，跳过属于值区域的字段，避免对不支持隐藏的字段执行操作
4. 完成新字段挂载后恢复自动更新，刷新透视表生效

修正后可直接使用的代码如下：

Sub Add_Column_Field()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim sField As String
    Dim colFieldArr() As String
    Dim i As Integer
    
    '绑定当前工作表第一个透视表，读取触发宏的按钮文本作为目标字段名
    Set pt = ActiveSheet.PivotTables(1)
    sField = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
    
    '开启手动更新，减少操作过程中的冗余重算
    pt.ManualUpdate = True
    
    '将现有列字段名称存入独立数组，避免遍历原集合时动态变更报错
    ReDim colFieldArr(1 To pt.ColumnFields.Count)
    For i = 1 To pt.ColumnFields.Count
        colFieldArr(i) = pt.ColumnFields(i).Name
    Next i
    
    '遍历数组移除原有列字段，跳过值区域字段避免报错
    For i = LBound(colFieldArr) To UBound(colFieldArr)
        Set pf = pt.PivotFields(colFieldArr(i))
        If pf.Orientation <> xlDataField Then
            pf.Orientation = xlHidden
        End If
    Next i
    
    '将目标字段挂载到列区域
    pt.PivotFields(sField).Orientation = xlColumnField
    
    '关闭手动更新，刷新透视表生效
    pt.ManualUpdate = False
    pt.RefreshTable
End Sub

使用注意事项 #

• 按钮上的显示文本必须和数据源中对应题目的字段名完全一致（例如要切换到Q2题，按钮文字就必须是Q2，不能带多余空格或特殊字符），否则会触发“找不到对应透视字段”的错误
• 如果工作簿内有多个关联透视表需要同步切换，可在代码内补充其他透视表的绑定逻辑，复用同一套字段切换规则即可

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