Excel VBA: check if a chart series exists, if not continue

Question

Each time a pivot chart is refreshed, it loses it's formatting. So I have a piece of VBA code to re-format the graph each time the pages refreshes.

Private Sub Worksheet_Calculate()
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnStacked
    ActiveChart.FullSeriesCollection("Limit").ChartType = xlLine
    ActiveChart.Deselect
End Sub

The graph type is combo, and all times series are stacked, except the time series called "limit". The above code works as long as the series "Limit" is in the data set. If the user selects a filter or slicer that removes the Limit time series from the data set excel throws an error.

I need to make the format optional.

I have tried to test to see if the series exists. But it isnt quite working.

Private Sub Worksheet_Calculate()
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnStacked

    If ActiveChart.FullSeriesCollection("Limit") Is Nothing Then "do nothing here"
    Else ActiveChart.FullSeriesCollection("Limit").ChartType = xlLine
    End If    

    ActiveChart.Deselect
End Sub

How can I get the VBA to skip the "limit" series if it doesn't exist?

Answer 1

One way is to just ignore errors. You can do that with the On Error statement:

Private Sub Worksheet_Calculate()
    On Error GoTo Finish
    Dim Nope As Integer
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnStacked
    ActiveChart.FullSeriesCollection("Limit").ChartType = xlLine
    ActiveChart.Deselect
Finish:
    On Error GoTo 0
End Sub

The On Error GoTo 0 tells it to cancel all error handling and manage it normally.

You could also just use On Error Resume Next at the top instead of GoTo Finish:

Private Sub Worksheet_Calculate()
    On Error Resume Next
    Dim Nope As Integer
    ActiveSheet.ChartObjects("Chart 3").Activate
    ActiveChart.FullSeriesCollection(1).ChartType = xlColumnStacked
    ActiveChart.FullSeriesCollection("Limit").ChartType = xlLine
    ActiveChart.Deselect
End Sub