CODEWITHSUNDEEP
vbaexcel
SpippyTheInsane
SpippyTheInsane

Reputation: 25

Avoiding the use of Activate in Excel VBA for Chart

Very new to Excel VBA here... Very similar to this post: Avoiding the use of Activate and Select when working with charts (Excel), I am attempting to avoid using Activate in order to make a small change to a number of Charts in a large workbook. I am performing this task for a good number of charts, so I would like to minimize the run-time as much as possible and have heard that the select/activate functions tend to slow down macros.

My example code looks like this:

ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.FullSeriesCollection(1).XValues = "=Leb!$C$2:$G$2"

I have attempted to replace these two lines with the following since this makes logical sense to me:

ActiveSheet.ChartObjects("Chart 3").FullSeriesCollection(1).XValues = "=Leb!$C$2:$G$2"

This code causes an error when run, so I then attempted to use the WITH format shown by the post mentioned above:

With Worksheets("Chart").ChartObjects("Chart 3").Chart.XValues = "=Leb!$C$2:$G$2"
End With

This also throws an error.

I understand that I am probably making a small, syntax error, but would anyone be able to provide any insight into what I am doing wrong? (I have never used the WITH function before, so some general insight on it would be very helpful as well.)

Thanks in advance.

Upvotes: 0

Views: 6740

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Almost there...

With Worksheets("Chart").ChartObjects("Chart 3").Chart
    .SeriesCollection(1).XValues = "=Leb!$C$2:$G$2"
End With

XValues belongs to a Series object, not the Chart (referring to your last code sample).

EDIT:

Dim arrCharts, cht
arrCharts = Array("Chart 3", "Chart 4", "Chart 5", "Chart 7")

For Each cht In arrCharts
    With Worksheets("Chart").ChartObjects(cht).Chart
        .SeriesCollection(1).XValues = "=Leb!$C$2:$G$2"
    End With
Next cht

Upvotes: 3

Related Questions