Running loop in excel vba 2016 for changing series in charts

Question

I have the following macro and I wish to loop the following program for 500 charts starting from 1.

Sub Arrow()
'
' Arrow Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveSheet.ChartObjects("Chart 459").Activate

    ActiveChart.FullSeriesCollection(1).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(192, 0, 0)
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 2.5
    End With
    Selection.Format.Line.EndArrowheadStyle = msoArrowheadTriangle
    With Selection.Format.Line
        .EndArrowheadLength = msoArrowheadLengthMedium
        .EndArrowheadWidth = msoArrowheadWide
    End With
    ActiveChart.FullSeriesCollection(2).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent5
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0
    End With
    With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 2.5
    End With
    Selection.Format.Line.EndArrowheadStyle = msoArrowheadTriangle
    With Selection.Format.Line
        .EndArrowheadLength = msoArrowheadLengthMedium
        .EndArrowheadWidth = msoArrowheadWide
    End With
End Sub

I can guarantee you that "please tell me *urgently*" is going to have quite the opposite effect of what you hope. — Benjamin W., Jan 08 '16 at 07:18
The first thing you will want to do is remove all `.Select`, `.Activate` and `Selection` commands and references in place of indexed based direct references. Once you have that, a `For i = 1 to .Count` will be easy. See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for more methods on getting away from relying on select and activate to accomplish your goals. — , Jan 08 '16 at 07:26

score 1 · Answer 1 · answered Jan 08 '16 at 08:53

I agree with @Jeeped. What you want is not difficult. However, moving from Select etc to Index based takes some learning.

the code below should do what you want. It worked for me in Office 2010, which uses SeriesCollection(1) instead of FullSeriesCollection(1)

Sub Arrow() ' ' Arrow Macro ' ' Keyboard Shortcut: Ctrl+q ' ActiveSheet.ChartObjects("Chart 459").Activate

    Dim i As Long
    Dim cht As Chart

    For i = 1 To ActiveWorkbook.Charts.Count

        Set cht = ActiveWorkbook.Charts(i)

        With cht.FullSeriesCollection(1).Format.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(192, 0, 0)
            .Transparency = 0
            .Weight = 2.5
            .EndArrowheadStyle = msoArrowheadTriangle
            .EndArrowheadLength = msoArrowheadLengthMedium
            .EndArrowheadWidth = msoArrowheadWide
        End With

        With cht.FullSeriesCollection(2).Format.Line
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent5
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = -0.5
            .Transparency = 0
            .Visible = msoTrue
            .Weight = 2.5
            .EndArrowheadStyle = msoArrowheadTriangle
            .EndArrowheadLength = msoArrowheadLengthMedium
            .EndArrowheadWidth = msoArrowheadWide
        End With

    Next i

End Sub

Now you know how to use a For Loop and Index based references.

Running loop in excel vba 2016 for changing series in charts

1 Answers1