My VBA code streaming data should generate one record each minute but generates two, why?

Question

The following VBA code activate CopyData 2 times each minute, It should activate CopyData only once each minute. (Yeasterday it activated CopyData only once per minute and generated one record each minute, no changes have been made to the code)

This is part of the records generated:

                 Time     Value

16:29:07	431,22
16:30:00	431,00
16:30:19	431,00
16:31:00	430,97
16:31:18	430,97
16:32:00	431,53
16:32:18	431,53
16:33:00	431,57
16:33:18	431,57
16:34:00	431,52
16:34:12	431,52
16:35:00	431,30
16:35:18	431,30
18:20:40	429,69
18:20:59	429,69

The code activating the CopyData where the record of time and valueis is streamed is this:

Sub StartProcess()

   If Time >= Range("StartTime") And Time < Range("EndTime") Then

     BeginStream

   Else

     Range("RunningOrNot") = "Waiting"

     dtime = Range("StartTime") - TimeValue("0:0:20") + Date + IIf(Time >= Range("EndTime"), 1, 0)

     Range("dtime") = dtime

     Application.OnTime dtime, "BeginStream"

   End If

 End Sub

Sub BeginStream()

  'Check the time and for every "hole" minute go to CopyData

  If Range("DCVAR") = "DAILY" And Time > Range("EndTime") Then

    Exit Sub

  End If

  'Start time next whole minute

  dtime = TimeSerial(Hour(Now), Minute(Now) + 1, 0)

  'Start streaming and copying data

  Range("RunningOrNot") = "Running"

  Range("dtime") = dtime

  Application.OnTime dtime, "CopyData"

End Sub

Thanks in advance

Per Morten

Answer 1

The program write either STOPPED, Running or Waiting to the range "RunningOrStopped".

 

So you say that if the program always have seen STOPPED before I start over again and I see that the range RunningOrStopped changes from STOPPED to Running then I can be sure that only one version of the program is running.

 

Per Morrten

Yes...if I understand you correctly.

If you have a range that contains Stopped/Running/Waiting, then the code in StartProcess should ensure that the range contains Stopped before it schedules another process.

Of course, whatever code you use to stop the process should update the same range to be 'stopped'.

Answer 2

The program write either STOPPED, Running or Waiting to the range "RunningOrStopped".

So you say that if the program always have seen STOPPED before I start over again and I see that the range RunningOrStopped changes from STOPPED to Running then I can be sure that only one version of the program is running.

Per Morrten

Answer 3

How is it possible to start the process twice and what do you mean be a left over.

How can I avoid both; to start twoce and a left over?

 

Per Morten.

To start the process multiple times just run the StartProcess subroutine multiple times.

A left over from the previous day is just that.  If you started the process yesterday and left Excel running, that process will restart at the start time today.  So, if you ran it again today, you'd have one process from yesterday and one that you started today.

I guess you could introduce a global variable that is used to indicate the process is active.  So, if someone runs the StartProcess subroutine, that routine would check the state of the global variable before deciding whether to start the timed execution of the code.

Answer 4

How is it possible to start the process twice and what do you mean be a left over.

How can I avoid both; to start twoce and a left over?

Per Morten.

Answer 5

My guess is that you have started the process twice -- or have one left over from the previous day.