VBA:运行时错误-第二个数据透视图的SetSourceData [英] VBA: Runtime Error - SetSourceData for Second Pivot Chart

问题描述

我得到了:

运行时错误'-2147467259(80004005)':对象"_Chart"的方法"SetSourceData"失败

Run-time error '-2147467259 (80004005)': Method 'SetSourceData' of object '_Chart' failed

我尝试运行以下脚本时间歇性地

intermittently when I try to run the following script:

Sub CreatePiePivot()
'Define worksheets
Set PSheet = Sheets("Tools")
Set DSheet = Sheets("Aggregate")

'Define last data points
LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Selects first to last filled row, and first to last filled column for data
Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

'Create pivot cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:=PRange)

'Moves user to Dashboard
Sheets("Tools").Activate

'Create blank pivot table
Set PTable = PCache.CreatePivotTable _
    (TableDestination:=PSheet.Range("F1"), _
        TableName:="ExcPT1")

'Create blank pivot chart
PSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie
    ActiveChart.SetSourceData _
        Source:=Range("$F$2:$H$19"), _
        PlotBy:=xlRows

ActiveWorkbook.ShowPivotTableFieldList = False

With ActiveChart.PivotLayout.PivotTable.PivotFields("Exception")
    .Orientation = xlRowField
    .Position = 1
End With

'Insert Data
With PSheet.PivotTables("ExcPT1").PivotFields("Exception")
    .Orientation = xlDataField
    .Position = 1
    .Caption = "Exception Status Count"
    .Function = xlCount
End With

'Hide Not Due
With ActiveChart.PivotLayout.PivotTable.PivotFields("Exception Status")
    .PivotItems("Not due").Visible = False
End With

'Move bar chart to Dashboard; resize
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsObject, Name:="Dashboard"

Set ChartWidth = Sheets("Dashboard").Range("B26:L49")
With ActiveChart.Parent
    .Height = ChartWidth.Height
    .Width = ChartWidth.Width
    .Top = ChartWidth.Top
    .Left = ChartWidth.Left
End With

With ActiveChart
    .HasTitle = False
End With

End Sub

此子项在另一个非常类似的子项之后运行，该子项使用连接到按钮的子项创建枢轴条形图:

This sub is running after another very similar sub that creates a pivot bar chart using a sub connected to a button:

Sub CreatePivots()

Call CreateBarPivot
Call CreatePiePivot

End Sub

BarPivot每次都能完美运行，没有问题，并且看起来与上面显示的PiePivot几乎相同，但进行了适当的更改以显示适当的数据.如果我分别运行这些潜艇，那么通常可以正常运行.在出现错误之前，我运行并删除了子项和结果三遍.调试指向此行:

The BarPivot runs perfectly, no problem, every time, and looks almost identical to the PiePivot shown above with the applicable changes to display the appropriate data. If I run those subs separately, it usually runs without a problem. I ran-and-deleted the sub and results three times before I got the error. The debug is pointing at this line:

    ActiveChart.SetSourceData _
        Source:=Range("$F$2:$H$19"), _
        PlotBy:=xlRows

...但是它看起来与BarPivot所使用的完全一样.由于并非每次都发生，而是在大多数情况下，所以我不确定从哪里开始进行故障排除.我查看的文档(顺便说一下，这是稀疏的，或者我在错误的位置查找)表明我做对了.

...but it looks exactly like the one used by the BarPivot. Since it's not happening exactly every time, but most of the time, I'm not sure where to start on troubleshooting. The documentation I've looked up (which is sparse by the way, or I'm looking in the wrong places) indicates I'm doing this right.

是什么原因导致该错误，以及如何解决该错误?

What's causing the error, and how do I fix it?

推荐答案

这是说明如何避免在代码中使用 Select 和 Activate 的示例，其他一些(希望是有用的)评论.

This is an example to illustrate how to avoid using Select and Activate in your code, plus a few other (hopefully useful) comments.

总是使用Option Explicit-强调得不够

Always Use Option Explicit - this can't be emphasized enough

这会将 Sub 的前几行变成:

Option Explicit

Sub CreatePiePivot()
    Dim thisWB As Workbook
    Set thisWB = ThisWorkbook

    Dim PSheet As Worksheet
    Dim DSheet As Worksheet
    Set PSheet = thisWB.Sheets("Tools")
    Set DSheet = thisWB.Sheets("Aggregate")

请注意，通过定义 thisWB ，您将在一行中设置一个工作簿引用，如果您需要更改工作簿，则只需更改那一行就可以了.另外，此参考可以解释为什么您应该使用通过 ActiveWorkbook 上的ThisWorkbook .

Notice that by defining thisWB, you're setting up a workbook reference in a single line that, if you ever needed to change workbooks, change that one line and you're done. Also, this reference can explain why you should use ThisWorkbook over ActiveWorkbook.

由于您的某些问题涉及到 假定的 参考(工作簿，工作表或图表)，因此以下几行可以帮助显示一些容易遗漏的必要参考:

Because some of your issues involve assumed references (either workbooks or worksheets or charts), the next lines can help show some necessary references that are easy to miss:

    'Define last data points
    Dim lastRow As Long
    Dim lastCol As Long
    lastRow = DSheet.Cells(DSheet.Rows.Count, 1).End(xlUp).Row
    lastCol = DSheet.Cells(1, DSheet.Columns.Count).End(xlToLeft).Column

请注意，您必须参考 Cells 参考的 DSheet inside ，以确保您指的是同一个工作表.否则，VBA可能会假定您所指的是 ActiveSheet ，该代码可能完全不同，并为您提供了不正确的值.

Notice here that you have to reference DSheet inside of the Cells reference to guarantee that you're referring to the same worksheet. Otherwise VBA could assume that you're referring to the ActiveSheet, which may be completely different and give you an incorrect value.

接下来的几行变成:

    'Selects first to last filled row, and first to last filled column for data
    Dim PRange As Range
    Set PRange = DSheet.Cells(1, 1).Resize(lastRow, lastCol)

    'Create pivot cache
    Dim PCache As PivotCache
    Set PCache = ActiveWorkbook.PivotCaches.Create _
                 (SourceType:=xlDatabase, SourceData:=PRange)

您代码中的下一条语句是 Sheets("Tools").Activate .除非有特殊原因要在此时向用户显示该工作表，否则完全不需要.如果您希望用户在所有处理完成后查看此工作表，请将此语句移到 Sub 的末尾.

Your next statement in your code is Sheets("Tools").Activate which, unless there is a specific reason you want to display that worksheet to the user at this time, is completely unnecessary. If you want the user to view this worksheet when all processing is completed, move this statement to the end of the Sub.

下一步:

    'Create blank pivot table
    Dim PTable As PivotTable
    Set PTable = PCache.CreatePivotTable _
                 (TableDestination:=PSheet.Range("F1"), _
                  TableName:="ExcPT1")

下一个真正的错误修复方法:

The real fix for your error comes next:

    'Create blank pivot chart
    Dim PChart As Chart
    Set PChart = PSheet.Shapes.AddChart
    PChart.ChartType = xlPie
    PChart.SetSourceData Source:=PSheet.Range("$F$2:$H$19"), PlotBy:=xlRows

因为要为新添加的图表创建对象，所以不必使用 ActiveChart .它遵循指定完整引用的相同思想.

Because you're creating an object for the newly added chart, you never have to use ActiveChart. It follows the same idea of specifying complete references.

以此类推...您可以希望使用这些示例来完成其余的代码，并解决您遇到的一些问题.试试看.

And so on... you can hopefully use these examples to finish out the rest of your code and work through some of the issues you're having. Give it a try.

这篇关于VBA:运行时错误-第二个数据透视图的SetSourceData的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！