I had spend half day also cannot solve this problem, anyone know what reason and solve?Thanks.
Each press F9 key to refresh formula and make random signal(i.e. "L", "S", "W"), VBA always error '-2147467259(80004005)' on .Points(r).DataLabel.Delete
Each press F9 key to refresh formula and make random signal(i.e. "L", "S", "W"), VBA always error '-2147467259(80004005)' on .Points(r).DataLabel.Delete
Code:
Private Sub Worksheet_Calculate()
Dim sys As Worksheet
Dim dat As Worksheet
Dim cht1 As Chart
Dim srs As Series
Dim r6 As Integer, r7 As Integer, r As Integer, v As Integer
Dim nowPt As Integer, signalPt As Integer
Dim sig As String
'On Error Resume Next
Set sys = Sheets("System")
Set dat = Sheets("Data")
Set cht1 = sys.ChartObjects("Chart_market").Chart
Set srs = cht1.SeriesCollection(4)
With srs
If Range("chart_period").Offset(0, -1) > dat.[Q1].End(xlDown) Then
If Range("chart_period") = Range("chart_sel") - 1 Or Range("chart_period") = Range("chart_sel") Then
v = 0
Else
v = Range("chart_period").Offset(0, -3)
End If
nowPt = Application.Match(Range("Backtest_to"), Range("data_rng"), 0) - v
r6 = Application.Match(dat.[Q1].End(xlDown), Range("data_rng"), 0)
r7 = Range("BE5").End(xlDown).Row
For r = nowPt + 1 To .Points.Count - 1
If .Points(r).HasDataLabel Then
.Points(r).DataLabel.Delete 'clear prior datalabels
End If
Next
For r = 1 To r7 - r6
If Range("BE" & r6 + r) <> Range("BE" & r6) Then
sig = Range("BE" & r6 + r)
signalPt = nowPt + Application.Match(sig, Range("BE" & r6 + 1 & ":" & "BE" & r7), 0) - 3
.Points(signalPt).ApplyDataLabels
With .Points(signalPt).DataLabel 'add signal label
With .Format.TextFrame2.TextRange.Font.Fill.ForeColor
If sig = "S" Then
.RGB = RGB(255, 0, 0)
ElseIf sig = "L" Then
.RGB = RGB(102, 255, 51)
ElseIf sig = "W" Then
.RGB = RGB(255, 0, 255)
End If
End With
End With
Exit For
End If
Next
End If
End With
Set sys = Nothing
Set dat = Nothing
Set cht1 = Nothing
End Sub
Last edited: