Update Excel Chart at Run VBA Runtime

Question

this question was already discussed in different topics but the solutions are not helping me. I am trying to calculate and display a series of data for a chart. Something like "Calculate y" --> "Update Series" --> wait 100 ms --> "Recalculate". The program is running but the chart is only updated when the Main-Sub is completely finished (and not after each 100 ms).

The series is based entirely on arrays within VBA and do not refer to Cells.

When I'm running the program with break points directly after the "sleep" function, the chart is updated.

For me the DoEvents and chart.Refresh method are not giving me the results I want. Does somebody has an idea? Thanks for your help guys.

Sub Main()
Dim ws As Worksheet
Dim mychart As Chart
Dim ser As Series
Dim x(1 To 10) As Double
Dim y(1 To 10) As Double
Dim i As Integer

Set ws = ThisWorkbook.Sheets("Sheet1")
Set mychart = ws.ChartObjects("Chart 1").Chart

Call DeletePlot(mychart)

For i = 1 To 10
    x(i) = i
    y(i) = i
Next i


' Plot first data
Set ser = mychart.SeriesCollection.NewSeries
With ser
        .Values = y
        .XValues = x
End With


Dim j As Integer
For j = 2 To 4
    For i = 1 To 10
        y(i) = i ^ j    ' update data
    Next i
    With ser
        .Values = y     ' update series
    End With
    mychart.Refresh     ' Does not work
    DoEvents            ' Does not work
    Sleep (100)
Next j
End Sub

The sleep function:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

The PlotDelete Function:

  Sub DeletePlot(mychart As Chart)
    Dim ser As Series
    For Each ser In mychart.SeriesCollection
      ser.Delete
    Next ser
  End Sub

How about making a function of the first part of your code (delete plot + adding data) and call that after you recalculated your X and Y. — Luuklag
it has the same result. the chart is updated only at the end of the Main. — M_Tornack
Do you use application.screenupdating = false somewhere in your code? — Luuklag
Did you try adding the x data as well in your second with ser block? — Luuklag

M_Tornack M_Tornack · Accepted Answer · 2017-09-04T07:36:22

A timer such as Application.OnTime or a windows API timer see link gave me the desired behaviour.

Update Excel Chart at Run VBA Runtime

1 Answers