Excel宏图创建只在执行调试器时执行

So I'm having a stress inducing issue with my Excel Macro. When I run the code to create a chart off some table data it breaks saying there is an error creating the sixth series but when I step through the code in the debugger it runs perfectly! Is there something with my approach below or is it another issue I don't know about.

我的Excel宏有一个压力诱发问题。当我运行代码从一些表数据创建一个图表时，它会中断，说创建第6个系列有错误，但是当我在调试器中遍历代码时，它会完美地运行!下面是我的方法，还是我不知道的另一个问题。

Here is the chart code:

这是图表代码:

Sub staffing_Chart()
    Dim staffChart
    Set staffChart = ActiveWorkbook.Worksheets("2015   Chart").ChartObjects.Add(Left:=175, Width:=500, Top:=350, Height:=325)
    With staffChart.Chart
        .ChartType = xlAreaStacked
        .HasTitle = True
        .ChartTitle.Text = "All Geo Int Staffing"
        .SetSourceData Source:=Range("B5:G30")
        .FullSeriesCollection(1).Name = "='2015 Chart'!$B$4"
        .FullSeriesCollection(1).XValues = "='2015 Chart'!$A$5:$A$30"
        .FullSeriesCollection(2).Name = "='2015 Chart'!$C$4"
        .FullSeriesCollection(3).Name = "='2015 Chart'!$D$4"
        .FullSeriesCollection(4).Name = "='2015 Chart'!$E$4"
        .FullSeriesCollection(5).Name = "='2015 Chart'!$F$4"
        .FullSeriesCollection(6).Name = "='2015 Chart'!$G$4"
        .Axes(xlCategory).CategoryType = xlCategoryScale
        .Axes(xlCategory).CrossesAt = 1
        .Axes(xlCategory).Crosses = xlAutomatic
        .Axes(xlCategory).TickMarkSpacing = 5
        .Axes(xlCategory).TickLabelSpacing = 5
        .Axes(xlCategory).TickLabels.NumberFormat = "m/d/yyyy"
        .Axes(xlCategory).TickLabels.NumberFormat = "[$-409]mmm-yy;@"
        .Axes(xlCategory).TickLabels.NumberFormat = "[$-409]mmm;@"
    End With
End Sub

And here is the table:

这是一张桌子:

EDIT

编辑

In answer to your suggestion Scott using your code I get this chart when I run in normally,

根据你的建议，斯科特使用你的代码，当我正常运行时，我得到了这个图表，

But if I step through your code in the debugger it does work correctly so that is a good suggestion from a optimization front.

但是如果我在调试器中遍历您的代码，它确实可以正确地工作，这是来自优化方面的一个很好的建议。

Nonetheless my problem persists.

不过我的问题依然存在。

1 个解决方案

#1

Why not just rely on Excel's built-in intelligence when building this chart and set the Source Range to include the Column Labels and x-Axis Category Labels.

在构建此图表并设置源范围以包含列标签和x轴类标签时，为什么不依赖Excel的内置智能呢?

The below code worked flawlessly for me, either in full execution or stepping through:

下面的代码对我来说完美无缺，无论是完全执行还是逐步完成:

Sub staffing_Chart()

    Dim staffChart
    Set staffChart = ActiveWorkbook.Worksheets("2015 Chart").ChartObjects.Add(Left:=175, Width:=500, Top:=350, Height:=325)

    With staffChart.Chart

        .ChartType = xlAreaStacked
        .HasTitle = True
        .ChartTitle.Text = "All Geo Int Staffing"
        .SetSourceData Source:=Range("A4:G30")

        With .Axes(xlCategory)
            .CategoryType = xlCategoryScale
            .CrossesAt = 1
            .Crosses = xlAutomatic
            .TickMarkSpacing = 5
            .TickLabelSpacing = 5
            .TickLabels.NumberFormat = "[$-409]mmm;@"
        End With

    End With

End Sub

#1