Premature Exit of week long running code 1

[Twullf]

I have written a code to pull data from an excel spread sheet which is being continuously updated by a program called WinDaq. The data is load cell data and the intent is to let this run for a week and pull data every twenty minutes.

For some reason after 13 hours the code just stopped. The computer did updates last night, but that was at 3 am and the code stopped prematurely at 8 pm. I have a save showing that the WinDaq software continued to pull data at least another 20 minutes.

I'd post my code, but it worked well for 13 hours and I don't think that the problem is there. I suspect there is an error handler or something in 2003 to prevent code from continuing excessively and I am not accounting for that.

Has anyone run into this issue before. Here is a quick run down on the code logic

Privat bStop As Boolean 'determines when stop button has been pressed

while not bStop

Do the data collection

For counter 1 to 120

' Dump the buffer every 10 seconds for 20 minutes to maintain connection to WinDaq and register button cancellation

Next counter

Save the file 'The reason I have data 20 minutes after that last data collection

Loop

Thanks in advance for any help

 

[SkipVought]

Hi,

Plz post your code. The devil is in the details.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IRstuff]

Have you looked at the Event Viewer logs?

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Twullf]

The code is relatively simple as follows:

' Used to stop the program when button is pushed

Private bStop As Boolean

Sub CollectData()
'
' test Macro
' Macro recorded 11/8/2017 by teeyanaw
'
Dim loadA_Colm As String
Dim loadB_Colm As String
Dim load_Row As Integer
Dim loadA_Cell As String
Dim loadB_Cell As String
Dim pasteCell As String
Dim newHour As Integer
Dim newMinute As Integer
Dim newSecond As Integer
Dim counter As Integer
Dim loadValue As Double

loadA_Cell = "K10"
loadB_Cell = "K15"
load_Row = 2
loadA_Colm = "A"
loadB_Colm = "B"

bStop = False

' So long as the stop button has not been pressed continue with loop
Do While Not bStop
'counter = 1

' Open sheet 1 and select the cells containing the average load value set above
Sheets("Sheet1").Select
'Range(loadA_Cell).Select
' Set Cell Value as a long value for loadValue
loadValue = Worksheets("Sheet1").Range(loadA_Cell).Value

' Goto Sheet two
Sheets("Sheet2").Select

' Pastecell contains the cell location to put the data, changes everytime
pasteCell = loadA_Colm + CStr(load_Row)

' Select the pastecell location and set the value equal to the loadValue variable
Range(pasteCell).Select
ActiveCell.Value = loadValue

' Open sheet 1 and select the cells containing the average load value set above
Sheets("Sheet1").Select

' Set Cell Value as a long value for loadValue
loadValue = Worksheets("Sheet1").Range(loadB_Cell).Value

' Goto Sheet two
Sheets("Sheet2").Select

' Pastecell contains the cell location to put the data, changes everytime
pasteCell = loadB_Colm + CStr(load_Row)

' Select the pastecell location and set the value equal to the loadValue variable
Range(pasteCell).Select
ActiveCell.Value = loadValue

'Insert Timestamp
Range("C" + CStr(load_Row)).Select
ActiveCell.Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
' Move row counter down one row
load_Row = load_Row + 1

' Goto Sheet one to wait out
Sheets("Sheet1").Select

' Intended to allow buffer to clear multiple times while waiting for the next entry
' currently set to clear buffer every 10 seconds and pull data every 20 min
For counter = 1 To 120
' Wait ten seconds between buffer clearing
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

'DoEvents will clear the buffer and deposit the DAQ data, maintaining the connection
DoEvents

If bStop = True Then counter = 120

Next counter

' Save File
ActiveWorkbook.Save

' For debug limits how many times program will run
' If load_Row = 6 Then Exit Sub

Loop
End Sub

Sub StopIt()
bStop = True
End Sub

I have found in research since posting that the save function should be clearing the memory removing the possibility that the premature exit is due to an excel memory issue from the windaq add-on.

Also last night it ran all night with no issue and is still running now. Which puts the run time at 26 hours so far.

As for event logs I am unsure how to find one for Excel when no error appears to have been triggered. Advice for where to go on that would be appreciated. It is a windows 7 system currently running Excel 2003.

 

[SkipVought]

1) Your DoEvents belongs inside the 10 second wait. DoEvents enables the user to hit the STOP button among other Excel things.

2) Exits from loops should be accomplished without changing the counter according to best practices.

'
        For counter = 1 To 120
        ' Wait ten seconds between buffer clearing
            newHour = Hour(Now())
            newMinute = Minute(Now())
            newSecond = Second(Now()) + 10
            waitTime = TimeSerial(newHour, newMinute, newSecond)
            Do
                'DoEvents will clear the buffer and deposit the DAQ data, maintaining the connection
                DoEvents
                If waitTime >= Now() Then Exit Do
            Loop
            
            If bStop Then Exit For
            
        Next counter

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Twullf]

Took me a bit to figure out why you coded it that way, but I like what I think that would do. It doesn't solve how everything will run fine for 13 or so hours and stop, but it would definitely make everything run nice and smooth. I could posibbly remove the counter and just go straight to the 20 minutes.

Thank you for the coding advice.

Do you think that 10 second lag could be causing the issue with the program stopping for no reason?

 

[SkipVought]

Just noticed, waitTime starts out 10 seconds greater than Now() so

'
    If waitTime [highlight #FCE94F]<=[/highlight] Now() Then Exit Do

Nothing wrong with a delay, 10 sec or 20 min.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jrs87]

Tight loops sometimes benefit from Sleep(1) after Doevents.

Instead of using Timeserial. Add 10 seconds to Now().

WaitTime = Now() + (10/86400)