Excel Macro Chart creation executes corectly only when stepping through debugger

Question

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.

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.

why not just .SetSourceData Source:=Range("A4:G30") and get rid of setting the .FullSeriesCollection individually? If you set the source range as I suggest, it will set up perfectly as it should. — Scott Holtzman
– Scott Holtzman, Commented Dec 7, 2015 at 20:27

Scott Holtzman · Accepted Answer · 2015-12-07 20:40:20Z

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.

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

answered Dec 7, 2015 at 20:40

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

staticFlow Over a year ago

Tried your suggestion but still getting my error, I put the output in an edit to my post.

Scott Holtzman Over a year ago

what happens when you click on the chart and view the data source ... that is strange!?

staticFlow Over a year ago

Ahhh the issue with the code was this line: .SetSourceData Source:=Range("A4:'2015 Chart'!G30") It was using a different sheet because when the code before the chart creation gets ran it is ending on a sheet other than 2015 chart! #facepalm the code now works! Thanks!

staticFlow Over a year ago

fixed it by adding '2015 Chart'! to the range, could have also put a set active sheet call in there.

Scott Holtzman Over a year ago

ah - I should have picked up on that. The best way to write it would this: Worksheets("2015 Chart").Range("A4:G30"). You definitely always want to qualify all specific workbooks / sheets / ranges in VBA. Otherwise, you might get unexpected errors that take forever to discover :) (As you just did!)

Collectives™ on Stack Overflow

Excel Macro Chart creation executes corectly only when stepping through debugger

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related