excel - 宏记录在另一张纸上：“运行时错误'-2147024809（80070057）'：指定的值超出范围”

Question

我在 Excel 中录制了一个宏。当我尝试在新工作表上运行它时，我收到错误消息：

“运行时错误'-2147024809（80070057）'：指定的值超出范围”

当我单击“调试”时，它会突出显示：

ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font.Size = 12

我尝试用整个代码替换“图表 1” ActiveChart，但这没有奏效。

Rows("2:2").Select
    Selection.ClearContents
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$B$496"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    Application.CutCopyMode = False
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table1", Version:=7).CreatePivotTable TableDestination:="Sheet1!R3C1", _
        TableName:="PivotTable10", DefaultVersion:=7
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Program Area")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
        "PivotTable10").PivotFields("Payment Amount"), "Sum of Payment Amount", xlSum
    ActiveSheet.PivotTables("PivotTable10").PivotFields("Sum of Payment Amount"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable10").AddDataField ActiveSheet.PivotTables( _
        "PivotTable10").PivotFields("Payment Amount"), "Sum of Payment Amount", xlSum
    Columns("B:B").Select
    Selection.NumberFormat = "$#,##0"
    Range("B7").Select
    ActiveSheet.Shapes.AddChart2(251, xlPie).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$3:$B$8")
    ActiveChart.SetElement (msoElementDataLabelBestFit)
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    Selection.ShowPercentage = True
    Selection.ShowSeriesName = True
    Selection.ShowSeriesName = False
    Selection.ShowCategoryName = True
    Selection.Separator = " "
    Selection.Separator = "" & Chr(13) & ""
    ActiveChart.SetElement (msoElementLegendNone)
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = _
        "Edmond J. Safra Philanthropic Foundation (Vaduz)" & Chr(13) & "Donations: DATE" & Chr(13) & "GRAND TOTAL"
    Selection.Format.TextFrame2.TextRange.Characters.Text = _
        "Edmond J. Safra Philanthropic Foundation (Vaduz)" & Chr(13) & "Donations: DATE" & Chr(13) & "GRAND TOTAL"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 49).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 40).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(41, 9).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(50, 16).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(50, 16).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(66, 11).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(66, 11).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select
    ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font.Size = 12
    With ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font
        .NameComplexScript = "Times New Roman"
        .NameFarEast = "Times New Roman"
        .Name = "Times New Roman"
    End With
    ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font.Bold = msoTrue
    With ActiveSheet.Shapes("Chart 1").TextFrame2.TextRange.Font.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorText1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartArea.Select
    ActiveChart.Location Where:=xlLocationAsNewSheet
    Application.PrintCommunication = False
    With ActiveChart.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .ChartSize = xlScreenSize
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .BlackAndWhite = False
        .Zoom = 100
    End With
    Application.PrintCommunication = True
    ActiveChart.ChartArea.Select
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).LeaderLines.Select
    ActiveChart.ChartArea.Select
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).Points(4).DataLabel.Select
    Selection.Left = 136.5
    Selection.Top = 34
    ActiveChart.PlotArea.Select
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    ActiveChart.FullSeriesCollection(1).Points(4).DataLabel.Select
    Selection.Left = 112.591
    Selection.Top = 54
    ActiveChart.ChartArea.Select
End Sub

标签： excelvba