Consulting

Results 1 to 5 of 5

Thread: Code goes into a infinite loop

  1. #1
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location

    Code goes into a infinite loop

    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
    SHAZAM!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    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.
    Last edited by xld; 11-02-2007 at 10:45 AM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location
    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)
    SHAZAM!

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,149
    Location
    It's a cute way to alternate the labels above and below the data point. Useful if there are many squeezed closely together.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Expert Shazam's Avatar
    Joined
    Sep 2005
    Posts
    530
    Location
    Quote Originally Posted by xld
    It's a cute way to alternate the labels above and below the data point. Useful if there are many squeezed closely together.
    Thanks!
    SHAZAM!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •