×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Premature Exit of week long running code

Premature Exit of week long running code

Premature Exit of week long running code

(OP)
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

RE: Premature Exit of week long running code

Hi,

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Premature Exit of week long running code

(OP)
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.

RE: Premature Exit of week long running code

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.

CODE

'
        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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Premature Exit of week long running code

(OP)
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?

RE: Premature Exit of week long running code

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

CODE

'
    If waitTime <= Now() Then Exit Do 

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Premature Exit of week long running code

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

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

WaitTime = Now() + (10/86400)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close