PDA

View Full Version : Code goes into a infinite loop



Shazam
11-02-2007, 08:53 AM
Hi everyone


I have this code below that will postion the vaules on the chart and it works grest. So I apply this code to a pivot chart and does not work properly. Everytime I use the drop down boxes it goes into a infinite loop.

Is there anyway to solve this?

Here is a link where I got the code from.
http://vbaexpress.com/forum/showthread.php?t=7497



Private Sub Chart_Calculate()
Dim intSeries As Integer
Dim intPoint As Integer

Dim pi As Long
Dim modifier As Integer
Dim OldNumberFormat As String

If Not ActiveChart Is Nothing Then
Application.ScreenUpdating = False
For Each srs In ActiveChart.SeriesCollection
'make sure we have datalabels and they hold values
With srs
.HasDataLabels = True
.DataLabels.Type = xlValue
OldNumberFormat = .DataLabels.NumberFormat
.DataLabels.NumberFormat = "General"
End With

For pi = 1 To srs.Points.Count
'set up an exception for the first point
If pi = 1 Then
modifier = -1
Else
modifier = 1
End If
'decide where the label goes
If CSng(srs.Points(pi).DataLabel.Text) _
> CSng(srs.Points(pi - modifier).DataLabel.Text) Then
srs.Points(pi).DataLabel.Position = xlLabelPositionAbove
Else
srs.Points(pi).DataLabel.Position = xlLabelPositionBelow
End If
Next pi
srs.DataLabels.NumberFormat = OldNumberFormat
Next srs
Application.ScreenUpdating = True

End If

End Sub

Bob Phillips
11-02-2007, 09:31 AM
Is it because the changes to the chart cause a re-entry of the calculate routine?

Try disabling application events at the start and turn them on at the end.

Shazam
11-02-2007, 09:51 AM
Thank You xld I did what you told me to and it works great.



Private Sub Chart_Calculate()

Dim pi As Long
Dim modifier As Integer
Dim OldNumberFormat As String

Application.EnableEvents = False

If Not ActiveChart Is Nothing Then
Application.ScreenUpdating = False
For Each srs In ActiveChart.SeriesCollection
'make sure we have datalabels and they hold values
With srs
.HasDataLabels = True
.DataLabels.Type = xlValue
OldNumberFormat = .DataLabels.NumberFormat
.DataLabels.NumberFormat = "General"
End With

For pi = 1 To srs.Points.Count
'set up an exception for the first point
If pi = 1 Then
modifier = -1
Else
modifier = 1
End If
'decide where the label goes
If CSng(srs.Points(pi).DataLabel.Text) _
> CSng(srs.Points(pi - modifier).DataLabel.Text) Then
srs.Points(pi).DataLabel.Position = xlLabelPositionAbove
Else
srs.Points(pi).DataLabel.Position = xlLabelPositionBelow
End If
Next pi
srs.DataLabels.NumberFormat = OldNumberFormat
Next srs
Application.ScreenUpdating = True

End If
Application.EnableEvents = True

End Sub



Can you tell me how these lines work?


For pi = 1 To srs.Points.Count
'set up an exception for the first point
If pi = 1 Then
modifier = -1
Else
modifier = 1
End If
'decide where the label goes
If CSng(srs.Points(pi).DataLabel.Text) _
> CSng(srs.Points(pi - modifier).DataLabel.Text)

Bob Phillips
11-02-2007, 10:15 AM
It's a cute way to alternate the labels above and below the data point. Useful if there are many squeezed closely together.

Shazam
11-02-2007, 10:43 AM
It's a cute way to alternate the labels above and below the data point. Useful if there are many squeezed closely together.

Thanks!