Attempt at creating a chart that changes data with time too laggy?

Question

I am trying to create a chart that changes the data with time. The Y data are the same but only the X data are changed. I am using Application.Wait to allow for some time between each change in X values (So I can see it with my eye). However when I run the code its very laggy and I am not sure if I am using the Application.Wait function properly but I want between each change a time of 0.5 seconds. Is there a way to make it more smooth so that I can see the graph as it changes with time (as the code loops through the rows)

Here is my code:

Sub UpdateChart()

Dim ChtObj As ChartObject
Dim counter As Integer
Dim timecount As Double


Set ChtObj = ActiveSheet.ChartObjects.Add(200, 50, 500, 500)

'Creating intial graph
With ChtObj.Chart
    'Chart Type
    .ChartType = xlXYScatterSmooth

    'Datainput
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "Bending moment"
    .SeriesCollection(1).Values = Range("D3:H3")
    .SeriesCollection(1).XValues = Application.Union(Cells(5, 4), Cells(5, 5), Cells(5, 6), Cells(5, 7), Cells(5, 8))
    .HasLegend = False

    'Title
    .HasTitle = True
    .ChartTitle.Text = "Bending moment along pile " & ActiveSheet.Name & " at time 0 seconds"

    'X-Axis
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Bending moment (kN.m)"
    'Y-Axis
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Length along pile (m)"

End With

'Loopingthrough data to be done**
counter = 6
timecount = 0
While (Not IsEmpty(Cells(counter, 4)))

    'Pausing for half a second and recording cumulative time
    timecount = timecount + 0.5
    Application.Wait (Now() + TimeValue("0:00:005"))

    'Updating Chart data
    With ChtObj.Chart
        .SeriesCollection(1).XValues = Application.Union(Cells(counter, 4), Cells(counter, 5), Cells(counter, 6), Cells(counter, 7), Cells(counter, 8))
        .ChartTitle.Text = "Bending moment along pile " & ActiveSheet.Name & " at time " & timecount & " s"
    End With

    'Next row
    counter = counter + 1
Wend
End Sub

I think [this SO post](http://stackoverflow.com/questions/18602979/how-to-give-a-time-delay-of-less-than-one-second-in-excel-vba) answers your question. Try `Applicaton.Wait(Now() + 1/(24*60*60.0*2))` — OldUgly, May 04 '16 at 03:19

score 0 · Answer 1 · answered May 04 '16 at 15:24

0

This seemed to solve my problem:

Public Function PauseEvent(ByVal Delay As Double)
  Dim dblEndTime As Double
  dblEndTime = Timer + Delay
  Do While Timer < dblEndTime
    DoEvents
  Loop
End Function

answered May 04 '16 at 15:24

Mofasa E

49
10

Attempt at creating a chart that changes data with time too laggy?

1 Answers1