Dynamic Update of Excel Chart with Delays

Question

I read a data line from an instrument using Excel VBA. I would like to plot the data dynamically on an Excel Active Chart, IMMEDIATELY after it has been read. I need to wait and read the data every 5 seconds and, in the mean time, I "sleep", either through the VBA Application.Wait command, or through the Kernel32 Sleep command. In either case the Active Chart does NOT get updated. The complete plot shows up only after the LAST "sleep". Any suggestions will be appreciated.

Here is the simplified code

Sub New_Data(indx)
Dim x As Integer

While True

    x = Read_Instrument(1)
    y = Read_Instrument(2)
    Cells(indx, 1) = x
    Cells(indx, 2) = y

    ActiveSheet.ChartObjects.Item(1).Activate
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$A$1:$A$" & indx
    ActiveChart.FullSeriesCollection(1).Values = "=Sheet1!$B$1:$B$" & indx

    indx = indx + 1
    Sleep 5000  'Use the KERNEL32 Sleep function for 5000 milliseconds
Wend
End Sub

Answer 1

Wait and Sleep will keep VBA running, so the screen will not updated. Try using Application.OnTime, along these lines (in a standard code module, i.e. Module1).

Sub refreshMyChart()
   Dim indx as long: indx = sheet1.Cells(Rows.Count, 1).End(xlUp).offset(1)

   Cells(indx, 1) = Read_Instrument(1)
   Cells(indx, 2) = Read_Instrument(2)

   With Sheet1.ChartObjects(1).FullSeriesCollection(1)
     .XValues = "=Sheet1!$A$1:$A$" & indx
     .Values = "=Sheet1!$B$1:$B$" & indx
  End With

  ''''' Now Schedule the same routine for 5 seconds later''''''''''
  Application.OnTime Now + TimeSerial(0,0,5), "refreshMyChart"
  ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

p.s.: be aware that this data cannot grow indefinitely.. You should define some way to stop or to delete old rows to keep the number of displayed data rows reasonable.