Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Animate the drawing of Excel 2013 Scatter Plot Point by Point

    Hello,

    I have a scatter chart with straight lines and markers. This plot is based on formulas whose values change based on user input.

    Rather than having the whole chart appear at once, I would like to animate the drawing of the chart point by point so users can see the chart being drawn starting with the first point. For example, (B4,C4) are plotted then (B5,C5) are plotted then (B6,C6) are plotted etc...

    When the user changes a value that the plot is based on the chart is redrawn again one point at a time point.

    Any thoughts?

    Thank you.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't have XL2013, but believe this XL2010 version should be adaptable...

    There is a worksheet change event which calls the animate/redraw macro.

    Steve
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Nov 2002
    Location
    USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is great, thank you very much. I have added it to my worksheet and it works great.
    Thanks again,
    Steve

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Steve,

    I'm not sure I see how the "old" line graph is being erased in the code.

    More importantly, I noticed that the axes don't change. So, for example if one of the values goes beyond the original bounds, the redrawn chart doesn't show a point for those points - for example a point has an x-value of 200.

    Seems like you'd have to start the chart over again from my point of view. But the animation would still be desired with renumbered axes.

    Fred

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The code does not erase the old XY graph (it is an XY scatter, not a line). It just redefines the range of the current XY graph.

    The macro only redefines the ranges, it does nothing to the other chart parameters. I hard coded the axes in the chart, you can set them to auto if desired or set to other parameters, or have the code calculate and hard code them, depending on what you want...

    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    alioli (2014-09-24)

  7. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Steve.

    I don't know that much about charts, not much about VBA. So when you put the 2 together, my chart VBA knowledge is vanishingly small.

    Anyway, I put in a break at your setting of dWait - just above the loop. I changed one of the x-values. By the time, the macro stopped, the chart had been redrawn with the new value. When I continued, it encountered the loop and it seems like here the new chart is "erased" and redrawn with the animation (you can see this even without the break if you don't blink).

    I also tried something which seemed obvious to me to see if I understood what was going on but it didn't work. Rather than set rX and rY, I deleted those 2 statement. I redefined the upper limit of the loop statement to be
    Range("B2:B15").Rows.Count
    and changed the .XValues and .Values to use
    .XValues = Range("B2:B15").Offset(x - 1) and similarly for .Values.

    This actually did not work. This actually did end up erasing the entire chart except for the last point. So between this observation and the one above about the chart being redrawn before the loop and then being "erased" for the animation, something is "erasing" the chart.

    Just a learning experience.

    Fred

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    When you redefine the X/Y range, only the new range is plotted. If you want you can look it as erasing the old and creating a new if you want, but I don't see it that way since the code is using the original series.

    Essentially at the end the chart the points B2:C15 are displayed as series1. When the macro i created starts, B2:C2 is displayed as series 1 [this displays nothing on the chart since you are only plotting lines, not points, and a single point does not create a line: is this what you mean by "erasing"? If you changed the to point and lines the single point would be displayed]. Then the chart becomes B2:C3, then B2:C4, etc until at the end B2:C15 is displayed.

    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    alioli (2014-09-24)

  10. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Yes - the display of the series before the loop followed by the definition of the series in the loop is what I meant by erasing. I get it - the series is defined before the loop for the whole range, so it appears in its entirety. Then, going thru the loop, the series is re-defined thru each iteration. So the first time thru the loop, the pre-loop series of all points is replaced by a series with 1 point. Next time, the 1-point series is replaced with a 2-point series with a line connecting the 2 points. Etc.

    I also understand why my code didn't work: each time thru the loop, I was re-defining the series but instead of adding a point to the series, as your code did, my code replaced the previous point from the previous iteration with the next point in the range. So my series always had 1 point.

    Thks.

  11. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Yes, that is correct.

    And you are very welcome.

    Steve

  12. The Following User Says Thank You to sdckapr For This Useful Post:

    alioli (2014-09-24)

  13. #10
    New Lounger
    Join Date
    Sep 2014
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Animated Scatterplots (over time) with 5 samples and 2 responses per sample

    Would you be able to help with a question regarding animated scatterplot using excel? I would like to make an animated scatterplot from the data in the file attached to this email. The data has been collected for 2 responses over time (70 seconds), and in 5 different samples (A-E). I tried multiple options but have not been able to come up with the right VBA code. Also, would you know, once it is finished, how to make it a stand-alone click type graph in powerpoint? Do not want lines, just points appearing and leaving, each sample with a different color. Thanks in advance. !
    Attached Files Attached Files

  14. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you elaborate on what you need in excel? I don't understand what you want the chart to look like and what you want animated. [perhaps you could attach a picture of what you need].

    For PowerPoint you can copy the chart and paste it as a picture or even embed the excel chart (and underlying worksheet). I don't work with PowerPoint so am unsure what you mean by "stand-alone click type graph".

    Steve

  15. #12
    New Lounger
    Join Date
    Sep 2014
    Posts
    2
    Thanks
    3
    Thanked 0 Times in 0 Posts

    I have five points that move over time

    Hi Steve

    I have 5 points (each point corresponds to a specific sample) that move over time. X = time (0-70 seconds). I would like to create a simple animated scatterplot with data moving from a start to an end position. An example of this can be seen in the first graph shown @ http://www.animatedgraphs.co.uk/scatter.html (using stata or R). If I could create a movie and I can insert the movie in powerpoint, I will be able to play it during a presentation. If I do not use an animated graph, the graph will be as shown in the file I am attaching. Is this something you can help me with?

    I have tried in excel, but the VBA programming language is quite difficult to understand.

    Thanks
    alioli


    Quote Originally Posted by sdckapr View Post
    Could you elaborate on what you need in excel? I don't understand what you want the chart to look like and what you want animated. [perhaps you could attach a picture of what you need].

    For PowerPoint you can copy the chart and paste it as a picture or even embed the excel chart (and underlying worksheet). I don't work with PowerPoint so am unsure what you mean by "stand-alone click type graph".

    Steve
    Attached Files Attached Files

  16. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this code?

    Code:
    Option Explicit
    Sub AnimateChart()
      Dim rX As Range
      Dim x As Long
      Dim y As Long
      Dim iCols As Integer
      Dim chtObj As ChartObject
      Dim dWait As Double
      
      'change as desired
      Set chtObj = ActiveSheet.ChartObjects("Chart 4") 'chartname
      Set rX = Range("A4:A73") 'X-range
      iCols = 5 ' 5 Cols of Y-data
      dWait = TimeValue("0:00:01") '1 sec wait
      
      With chtObj.Chart
        With .Axes(xlCategory)
          .MinimumScale = 0
          .MaximumScale = Application.WorksheetFunction.Max(rX)
        End With
        With .Axes(xlValue)
          .MinimumScale = 0
          .MaximumScale = WorksheetFunction.Max(rX.Offset(0, 1).Resize(, iCols))
        End With
      End With
      For x = 1 To rX.Rows.Count
        For y = 1 To iCols
          With chtObj.Chart.SeriesCollection(y)
            .XValues = rX.Cells(x)
            .Values = rX.Cells(x, y + 1)
          End With
        Next
        Application.Wait (Now + dWait)
      Next
      MsgBox "Animation complete"
    End Sub
    Steve

  17. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Here are some modifications to sdckapr's sweet code that will provide a smoother and faster animation of the graph being plotted.

    HTH,
    Maud

    Code:
    Option Explicit
    Sub AnimateChart()
    '----------------------------------------
    'DECLARE AND SET VARIABLES
      Dim rX As Range
      Dim x As Long
      Dim chrt As ChartObject
      Set chrt = Worksheets("Sheet1").ChartObjects("Chart 4") 'chartname
      Set rX = Range("A4:A73") 'X-range
      With chrt.Chart
    '----------------------------------------
    'SET HORIZONTAL AXIS TO MAX VALUE (COL A)
        With .Axes(xlCategory)
          .MinimumScale = 0
          .MaximumScale = Application.WorksheetFunction.Max(rX)
        End With
    '----------------------------------------
    'SET VERTICLE AXIS TO MAX VALUE (COLS B THRU F)
        With .Axes(xlValue)
          .MinimumScale = 0
          .MaximumScale = WorksheetFunction.Max(rX.Offset(0, 1).Resize(, 5))
        End With
      End With
    '----------------------------------------
    'PLOT DATA
      For x = 1 To rX.Rows.Count
        chrt.Select
            ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$A$4:$A$" & x + 3
            ActiveChart.SeriesCollection(1).Values = "=Sheet1!$B$4:$B$" & x + 3
            ActiveChart.SeriesCollection(2).XValues = "=Sheet1!$A$4:$A$" & x + 3
            ActiveChart.SeriesCollection(2).Values = "=Sheet1!$C$4:$C$" & x + 3
            ActiveChart.SeriesCollection(3).XValues = "=Sheet1!$A$4:$A$" & x + 3
            ActiveChart.SeriesCollection(3).Values = "=Sheet1!$D$4:$D$" & x + 3
            ActiveChart.SeriesCollection(4).XValues = "=Sheet1!$A$4:$A$" & x + 3
            ActiveChart.SeriesCollection(4).Values = "=Sheet1!$E$4:$E$" & x + 3
            ActiveChart.SeriesCollection(5).XValues = "=Sheet1!$A$4:$A$" & x + 3
            ActiveChart.SeriesCollection(5).Values = "=Sheet1!$F$4:$F$" & x + 3
        Pause
      Next
      MsgBox "Animation complete"
    End Sub
    
    
    Private Sub Pause()
    '----------------------------------------
    'CHANGE PAUSETIME TO ADJUST SPEED
    Dim PauseTime, Start
        PauseTime = 0.1
        Start = Timer
        Do While Timer < Start + PauseTime
            DoEvents
        Loop
    End Sub

  18. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I have provided the file showing the above animation plus a second animation that scrolls the graph from left to right.
    Attached Files Attached Files

  19. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Nicole545 (2014-11-01),X_LD (2015-03-28)

Page 1 of 2 12 LastLast

Posting Permissions

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