excel - VBA - Excel 2016 中的箱线图

Question

我目前在 Excel 中创建箱线图时遇到问题

temp.Activate

With ActiveSheet
'First diagram
.Shapes.AddChart2(227, xlLine).Select

With ActiveChart
.SetSourceData Source:=ActiveSheet.Range(ActiveSheet.Cells(2, 1), ActiveSheet.Cells(pEnd, 15))
.ChartTitle.Text = "Diagram 1"
.Axes(xlValue).MinimumScale = -30
.Axes(xlValue).MaximumScale = 25
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Axe1"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Axe2"
.Axes(xlValue).Crosses = xlMaximum
.HasLegend = True
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 3, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleHeight 2, msoFalse, msoScaleFromTopLeft
.ClearToMatchStyle
.ChartStyle = 230
End With

.Shapes.AddChart2(408, xlBoxwhisker, 200, 100, 350, 200, True).Select
With ActiveChart
.SetSourceData Source:=ActiveSheet.Range(ActiveSheet.Cells(2, 1), ActiveSheet.Cells(pEnd, 15))
.ChartTitle.Text = "Diagram 2"
.HasLegend = True
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 3, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleHeight 2, msoFalse, msoScaleFromTopLeft
End With 'Chart


End With 'temp

第一个图表的创建没有任何问题。对于第二个图表，Excel 报错:“对象不支持此操作。”

在线上:

.SetSourceData Source:=ActiveSheet.Range(ActiveSheet.Cells(2, 1), 
ActiveSheet.Cells(pEnd, 15))

这是 BoxWhisker 图的问题吗？还是我合并了一个错误

谢谢!

此致，
蒂莫

Answer 1

这可以简化，但对于初学者来说，这是正确的想法吗？

Option Explicit
Sub TEST()

    Dim temp As Worksheet
    Set temp = ThisWorkbook.Worksheets("Sheet1")

    Dim pEnd As Long

    pEnd = 3

    temp.Activate

    With ActiveSheet
        'First diagram
        .Shapes.AddChart2(227, xlLine).Select

        With ActiveChart
            .SetSourceData Source:=ActiveSheet.Range(ActiveSheet.Cells(2, 1), ActiveSheet.Cells(pEnd, 15))
            .ChartTitle.Text = "Diagram 1"
            .Axes(xlValue).MinimumScale = -30
            .Axes(xlValue).MaximumScale = 25
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Axe1"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Axe2"
            .Axes(xlValue).Crosses = xlMaximum
            .HasLegend = True
            ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleWidth 3, msoFalse, msoScaleFromTopLeft
            ActiveSheet.Shapes(ActiveChart.Parent.Name).ScaleHeight 2, msoFalse, msoScaleFromTopLeft
            .ClearToMatchStyle
            .ChartStyle = 230
        End With

        With .Shapes.AddChart2(408, xlBoxwhisker, 200, 100, 350, 200, True)

            With ActiveChart
                .SetSourceData Source:=ActiveSheet.Range(ActiveSheet.Cells(2, 1), ActiveSheet.Cells(pEnd, 15))

                .ChartTitle.Text = "Diagram 2"
                .HasLegend = True
                ActiveSheet.Shapes(.Parent.Name).ScaleWidth 3, msoFalse, msoScaleFromTopLeft
                ActiveSheet.Shapes(.Parent.Name).ScaleHeight 2, msoFalse, msoScaleFromTopLeft
            End With

        End With                                 'Chart


    End With                                     'temp

End Sub

有测试数据