1

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
M_Tornack
  • 114
  • 6
  • 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 Sep 01 '17 at 13:18
  • it has the same result. the chart is updated only at the end of the Main. – M_Tornack Sep 01 '17 at 13:36
  • Do you use application.screenupdating = false somewhere in your code? – Luuklag Sep 01 '17 at 13:40
  • no, the code which I posted, is the complete code – M_Tornack Sep 01 '17 at 13:41
  • Did you try adding the x data as well in your second with ser block? – Luuklag Sep 01 '17 at 13:44
  • yes, I tried it. but as expected it had no effect. however if I put a breakpoint directly after the sleep function, the chart is updated. this tells me that the series is well updated. but the chart updates only at the breakpoint or at the end of the module – M_Tornack Sep 01 '17 at 13:47
  • Possible duplicate of [Excel chart won't update](https://stackoverflow.com/questions/4902703/excel-chart-wont-update) – Luuklag Sep 01 '17 at 14:06
  • thanks for your help. Finally I used the windows API Timer explained in [Windows Timer](http://www.cpearson.com/excel/OnTime.aspx). – M_Tornack Sep 04 '17 at 07:29

1 Answers1

0

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

Community
  • 1
  • 1
M_Tornack
  • 114
  • 6