VBA生成图表触发Run-time error '-2147417848'报错求助

我是VBA新手，首次求助。现有4个约7000行的工作表，使用仅工作表引用不同的宏生成图表时，其中3个工作表正常，唯独一个工作表在执行Set graphObj = ws.ChartObjects.Add(Left:=200, Width:=375, Top:=75, Height:=225)代码时触发Run-time error '-2147417848 (80010108) - Automation error - 对象已与其客户端断开连接。最初代码可正常生成图表，调整图表尺寸后报错，改回原尺寸仍无法生成，不知如何调试。

附代码如下：

Sub Example_Graph()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim manpowerCell As Range
    Dim companyCell As Range
    Dim graphDataRange As Range
    Dim graphObj As ChartObject
    
    ' Set the worksheet where the data and graph will be created
    Set ws = ThisWorkbook.Sheets("Example")
    
    ' Find the "manpower" cell in column F
    Set manpowerCell = ws.Columns("F:F").Find("manpower", LookIn:=xlValues, LookAt:=xlWhole)
    
    ' Find the "Compay" cell beneath the "manpower" cell in column F
    Set companyCell = ws.Columns("F:F").Find("Company", After:=manpowerCell, LookIn:=xlValues, LookAt:=xlWhole)
    
    ' Find the rows for "MONTH", "Previous P6", "P6", and "REMAINING" next to "Company"
    Dim monthRow As Long
    Dim prevP6Row As Long
    Dim p6Row As Long
    Dim remainingRow As Long
    
    monthRow = companyCell.Offset(1, 1).Row
    prevP6Row = companyCell.Offset(3, 1).Row
    p6Row = companyCell.Offset(4, 1).Row
    remainingRow = companyCell.Offset(6, 1).Row
    
    ' Set the graph data range to include only the required rows
    Dim startCol As Long
    Dim endCol As Long
    
    startCol = ws.Cells(monthRow, "X").Column
    endCol = ws.Cells(remainingRow, "BT").Column
    
    
    Set graphDataRange = ws.Range(ws.Cells(monthRow, startCol), ws.Cells(remainingRow, endCol))
    
    ' Create a new chart on the same worksheet
    Set graphObj = ws.ChartObjects.Add(Left:=200, Width:=375, Top:=75, Height:=225)
    
    ' Set the chart data source and chart type (column clustered graph)
    With graphObj.Chart
        .SetSourceData Source:=graphDataRange
        .ChartType = xlColumnClustered ' Set to column clustered graph
        .HasTitle = True
        .ChartTitle.Text = "6-Month Period Column Clustered Graph for Company"
        ' Customize other chart properties as needed
    End With
End Sub

已尝试操作：

• 最初代码可正常生成图表，调整图表尺寸后报错
• 改回原尺寸仍无法生成图表

排查与解决方向 #

1. 工作表状态检查 #

• 确认报错工作表未被锁定、保护，也未处于只读模式
• 在创建图表前先激活目标工作表，添加代码：ws.Activate

2. 重置Excel环境 #

• 关闭所有Excel窗口，重新打开文件，清除内存中残留的失效对象
• 在代码开头添加Application.ScreenUpdating = False，结尾添加Application.ScreenUpdating = True，减少界面刷新冲突

3. 替换图表创建方式 #

若ChartObjects.Add直接报错，尝试拆分尺寸设置步骤：

' 先创建临时尺寸的图表对象，再调整位置和大小
Set graphObj = ws.ChartObjects.Add(0, 0, 100, 100)
graphObj.Left = 200
graphObj.Top = 75
graphObj.Width = 375
graphObj.Height = 225

或者先创建独立图表再嵌入工作表：

Dim chrt As Chart
Set chrt = Charts.Add
chrt.Location Where:=xlLocationAsObject, Name:=ws.Name
Set graphObj = ws.ChartObjects(chrt.Name)

4. 验证数据范围有效性 #

• 在代码中添加调试语句：MsgBox graphDataRange.Address，确认数据范围是否正确
• 检查目标范围是否存在合并单元格、错误值（#N/A、#VALUE!等），这类内容可能导致图表创建失败

5. 修复Office或升级版本 #

• 打开控制面板→程序和功能→找到Microsoft Office→更改→快速修复，修复可能的文件损坏
• 若使用旧版Excel，尝试升级到最新版本，部分旧版本存在图表对象的已知Bug

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