Strange VBA behaviour when running a macro

[dmarx88]

Hello all,

I am experiencing a quite interesting problem when I try to run a macro and I'll try to be as clear as possible.

I have few graph in one worksheet all made of clustered bars and a scatter point with error bars (it's a normal bar-graph with a target line).

Now, I have to delete all the curves from the graph to "refresh" them and I wrote this simple function:

Sub Reset()
 For i = 1 To ActiveSheet.ChartObjects.Count
  ActiveSheet.ChartObjects(i).Activate
  For Each s In ActiveChart.SeriesCollection
      s.Delete
  Next s
 Next i
End Sub

This function was working perfectly if run in debugging mode, but excel was crashing if run normally. Moreover this function was working if I don't add the target line to my graphs and leave just the clustered columns.

So, I have googled my problem and found few solutions like inserting a kind of "sleep" in the function (the function below is a bit manipulated to see if something could change)

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Reset2()
Dim i, n As Long
Application.ScreenUpdating = False 'To Turn Off at the start of code.
 For i = 1 To ActiveSheet.ChartObjects.Count
  ActiveSheet.ChartObjects(i).Activate
  With ActiveChart
    For n = .SeriesCollection.Count To 1 Step -1
        .SeriesCollection(n).Delete
       Sleep 10
    Next n
  End With
 Next i
Application.ScreenUpdating = False 'To Turn on at the end of the code.
End Sub

Now it is the really strange stuff:

1 - If i add clustered columns to the graph, and then the target line and then I try to run the reset function, excel crashes.

2 - If I add just the target line to the graphs, call the reset function (that works perfectly with just target line) and then do everything as point 1, all is fine!! It's like excel has to learn first how to delete my target line.....

Seriously it's been a few days I am struggling with this problem.. and I really hope to have some help from you guys..