将代码通过VBA添加到工作簿时出错

我试图在Excel中使用VBA将条件格式添加到数据透视表的列 . 问题是每当刷新数据透视表或更改过滤器等时，条件格式都会丢失 . 我的解决方案是在工作簿中的数据透视表更新事件中添加一个宏，它有效...有点 . 似乎当我运行创建数据透视表的代码并添加处理条件格式的代码时，会发生错误，但仅在VBA窗口未打开时 . 如果VBA窗口打开，代码将正常执行 - 尽管代码没有更改或引用更改 .

Private Sub setupConditionalFormattingForStatusColumn()
    Dim thisSheetModule As vbcomponent
    Dim formattingCodeString As String

    On Error GoTo conditionalFormattingError

    formattingCodeString = _
    "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" & vbNewLine & _
    "    With Target.parent.Columns(" & harReportColumn("Status") & ")" & vbNewLine & _
    "         .FormatConditions.AddIconSetCondition" & vbNewLine & _
    "         .FormatConditions(.FormatConditions.Count).SetFirstPriority" & vbNewLine & _
    vbNewLine & _
    "         With .FormatConditions(1)" & vbNewLine & _
    "              .IconSet = ActiveWorkbook.IconSets(xl4TrafficLights)" & vbNewLine & _
    "              .IconCriteria(1).Icon = xlIconYellowExclamation" & vbNewLine & _
    vbNewLine & _
    "              With .IconCriteria(2) " & vbNewLine & _
    "                   .Type = xlConditionValueNumber" & vbNewLine & _
    "                   .value = -1" & vbNewLine & _
    "                   .Operator = 5" & vbNewLine & _
    "                   .Icon = xlIconGreenCircle" & vbNewLine & _
    "              End With" & vbNewLine & _
    vbNewLine & _
    "              With .IconCriteria(3)" & vbNewLine & _
    "                   .Type = xlConditionValueNumber" & vbNewLine & _
    "                   .value = 1.05" & vbNewLine & _
    "                   .Operator = 7" & vbNewLine & _
    "                   .Icon = xlIconYellowCircle" & vbNewLine & _
    "              End With" & vbNewLine
    formattingCodeString = formattingCodeString & vbNewLine & _
    "              With .IconCriteria(4)" & vbNewLine & _
    "                   .Type = xlConditionValueNumber" & vbNewLine & _
    "                   .value = 1.15" & vbNewLine & _
    "                   .Operator = 7" & vbNewLine & _
    "                   .Icon = xlIconRedCircleWithBorder" & vbNewLine & _
    "              End With" & vbNewLine & _
    vbNewLine & _
    "             .ShowIconOnly = True" & vbNewLine & _
    "         End With" & vbNewLine & _
    vbNewLine & _
    "         .HorizontalAlignment = xlCenter" & vbNewLine & _
    "         .VerticalAlignment = xlCenter" & vbNewLine & _
    "     End With" & vbNewLine & _
    "End Sub"

    Set thisSheetModule = ThisWorkbook.VBProject.VBComponents(harReportSheet.CodeName)
    thisSheetModule.CodeModule.AddFromString formattingCodeString

    Exit Sub

conditionalFormattingError:
    errorLog.logError "WARNING: An error occured while applying the conditional formatting code for the ""Status"" column."
    Err.Clear
    Resume Next
End Sub

生成错误的行是： thisSheetModule.CodeModule.AddFromString formattingCodeString 但只有在VBA窗口关闭时才会生成错误 .

有任何想法吗？