ActiveSheet.ChartObjects("chart") always returns Nothing?

Question

I'm querying for a chart in the worksheet and I can see it's there but everytime I execute the following piece of code it evaluates to 'Nothing'. Why is that?

Sub DeleteGraph()
Dim graph As ChartObject

On Error Resume Next
'graph always evaluates to Nothing
graph = ActiveSheet.ChartObjects("frontier").Activate

    If Not (graph Is Nothing) Then 'graph always evaluates to Nothing
        ActiveSheet.ChartObjects("frontier").Activate
        ActiveChart.Parent.Delete
    End If

End Sub

This macro executes when a button is clicked on the worksheet that runs the following Initialize macro that resets all calculations and erases any graph if it exists:

Sub InitializeSheet()
    'Get number of projects on worksheet and the corresponding 'final' cell number/ref to use in calculations - Private/Module Variables
    projectCount = Application.WorksheetFunction.Count(Range("A:A"))
    lastCell = projectCount + 1
    DeleteGraph 'Above Macro called here
    'Clear previous calculation data
    Range("I:AD").Value = ""


End Sub

I need to delete the graph created by the macro when resetting/initializing the sheet. I check if the graph exists (and the user hasn't deleted it manually) before performing the deletion. For some reason I can't get this to work. What am I overlooking?

Answer 1

Beware using On Error Resume Next in your code, especially if it's code that hasn't been tested thoroughly. In this case, it's masking a syntax error in the line

graph = ActiveSheet.ChartObjects("frontier").Activate

hence, no value is ever assigned to graph. Try this instead:

Sub DeleteGraph()
Dim graph As ChartObject

On Error GoTo ErrHandler
Set graph = ActiveSheet.ChartObjects("frontier")
graph.Delete
Exit Sub

ErrHandler:
msgBox "Chart does not exist on active sheet!"
Exit Sub

End Sub

If you don't like the pop-up warning when the chart is not found, just remove the msgBox line.