Timing loop help please
 

I am trying to create a timing loop that updates data periodically until
a condition is met.
The text data is imported onto ashee from a Data Acquistion system every
5 minutes I need to calculate results from a subset of that data and
when certain conditions are met end the macro. Currently I just assign a
zero to each of the conditional results if true, if all are true the
sum_check is zero.

The timing loop needs to :

operate once
check the sum check cell
if the sum check (error_sum) is zero, exit
if the sum check is greater than zero then wait 5 minutes and operate
then loop until error_check is zero
then exit

I have tried the following which works if error_sum is zero
but gets me an endless loop and ends up using all my system resources if
the error_sum is 1

Sub Timed_Loop()


Do
error_sum = Worksheets("MONITOR").Range("B27")
Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Loop Until error_sum = 0

End Sub

I don't do this often and expect that I am missing something dead simple.

Any help would be appreciated.

Marles

Timing loop help please
 

Marles, what does the sub "Get_early_data" do? If it doesn't alter the
Range("B27") calculation, then it will run forever as there's no way to stop
it.

Personally (I don;t know if this matters) I prefer to use a Do While loop
instead of what you have done here.

HTH.

"Marles McDonald" wrote:

I am trying to create a timing loop that updates data periodically until
a condition is met.
The text data is imported onto ashee from a Data Acquistion system every
5 minutes I need to calculate results from a subset of that data and
when certain conditions are met end the macro. Currently I just assign a
zero to each of the conditional results if true, if all are true the
sum_check is zero.

The timing loop needs to :

operate once
check the sum check cell
if the sum check (error_sum) is zero, exit
if the sum check is greater than zero then wait 5 minutes and operate
then loop until error_check is zero
then exit

I have tried the following which works if error_sum is zero
but gets me an endless loop and ends up using all my system resources if
the error_sum is 1

Sub Timed_Loop()


Do
error_sum = Worksheets("MONITOR").Range("B27")
Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Loop Until error_sum = 0

End Sub

I don't do this often and expect that I am missing something dead simple.

Any help would be appreciated.

Marles

Timing loop help please
 

DaveO

The sub "get early data" doesn't affect B27 all it does is select a
subset of data text data I am updating evert 5 minute from an
acquisition system. but the calculations of the data will change the B27
cell value. I do want it to run continually until B27 changes due to
the results of the calculations satisfy the spreadsheet conditions.

I've tried a do while loop. When I run the statement in a macro as

sub

Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

end sub

it works exactly like I would expect, waits 5 minutes then runs the
sub "get early data" then ends. the problem I am having is that
statement when used in a conditional macro as below, it runs both
continuously and the loop seems to call call the statement infinitely so
I get the infamous hourglass and eventually run out of memeory.

Thanks for taking a stab and I hope the added info helps.


DaveO wrote:
Marles, what does the sub "Get_early_data" do? If it doesn't alter the
Range("B27") calculation, then it will run forever as there's no way to stop
it.

Personally (I don;t know if this matters) I prefer to use a Do While loop
instead of what you have done here.

HTH.

"Marles McDonald" wrote:


I am trying to create a timing loop that updates data periodically until
a condition is met.
The text data is imported onto ashee from a Data Acquistion system every
5 minutes I need to calculate results from a subset of that data and
when certain conditions are met end the macro. Currently I just assign a
zero to each of the conditional results if true, if all are true the
sum_check is zero.

The timing loop needs to :

operate once
check the sum check cell
if the sum check (error_sum) is zero, exit
if the sum check is greater than zero then wait 5 minutes and operate
then loop until error_check is zero
then exit

I have tried the following which works if error_sum is zero
but gets me an endless loop and ends up using all my system resources if
the error_sum is 1

Sub Timed_Loop()


Do
error_sum = Worksheets("MONITOR").Range("B27")
Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Loop Until error_sum = 0

End Sub

I don't do this often and expect that I am missing something dead simple.

Any help would be appreciated.

Marles

Timing loop help please
 

OK, I think I udnerstand.

the problem is this...

You're setting it off on a routine that loops. It sets the error_sum
varaible. trhe it schedules a task, then it loops. By the time it loops B27
hasn't altered as you've scheduled the Get_early_data for 5 minutes. It
therefore keeps looping, keeps setting tasks for 5 minutes in the future and
will grind your machine to a halt.

So as an idea....

Why not use the WorkSheet_Change function...

Use an IF statement something like...

-----------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B27").Text < 0 Then

Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Else

MsgBox "Routine Complete"

End If

End Sub

------------------------

Would this work better??

HTH.

"Marles McDonald" wrote:

DaveO

The sub "get early data" doesn't affect B27 all it does is select a
subset of data text data I am updating evert 5 minute from an
acquisition system. but the calculations of the data will change the B27
cell value. I do want it to run continually until B27 changes due to
the results of the calculations satisfy the spreadsheet conditions.

I've tried a do while loop. When I run the statement in a macro as

sub

Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

end sub

it works exactly like I would expect, waits 5 minutes then runs the
sub "get early data" then ends. the problem I am having is that
statement when used in a conditional macro as below, it runs both
continuously and the loop seems to call call the statement infinitely so
I get the infamous hourglass and eventually run out of memeory.

Thanks for taking a stab and I hope the added info helps.


DaveO wrote:
Marles, what does the sub "Get_early_data" do? If it doesn't alter the
Range("B27") calculation, then it will run forever as there's no way to stop
it.

Personally (I don;t know if this matters) I prefer to use a Do While loop
instead of what you have done here.

HTH.

"Marles McDonald" wrote:


I am trying to create a timing loop that updates data periodically until
a condition is met.
The text data is imported onto ashee from a Data Acquistion system every
5 minutes I need to calculate results from a subset of that data and
when certain conditions are met end the macro. Currently I just assign a
zero to each of the conditional results if true, if all are true the
sum_check is zero.

The timing loop needs to :

operate once
check the sum check cell
if the sum check (error_sum) is zero, exit
if the sum check is greater than zero then wait 5 minutes and operate
then loop until error_check is zero
then exit

I have tried the following which works if error_sum is zero
but gets me an endless loop and ends up using all my system resources if
the error_sum is 1

Sub Timed_Loop()


Do
error_sum = Worksheets("MONITOR").Range("B27")
Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Loop Until error_sum = 0

End Sub

I don't do this often and expect that I am missing something dead simple.

Any help would be appreciated.

Marles

Timing loop help please
 

Thanks I'll try that, looks like I have a lot more to learn :)

Marles

DaveO wrote:
OK, I think I udnerstand.

the problem is this...

You're setting it off on a routine that loops. It sets the error_sum
varaible. trhe it schedules a task, then it loops. By the time it loops B27
hasn't altered as you've scheduled the Get_early_data for 5 minutes. It
therefore keeps looping, keeps setting tasks for 5 minutes in the future and
will grind your machine to a halt.

So as an idea....

Why not use the WorkSheet_Change function...

Use an IF statement something like...

-----------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B27").Text < 0 Then

Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Else

MsgBox "Routine Complete"

End If

End Sub

------------------------

Would this work better??

HTH.

"Marles McDonald" wrote:


DaveO

The sub "get early data" doesn't affect B27 all it does is select a
subset of data text data I am updating evert 5 minute from an
acquisition system. but the calculations of the data will change the B27
cell value. I do want it to run continually until B27 changes due to
the results of the calculations satisfy the spreadsheet conditions.

I've tried a do while loop. When I run the statement in a macro as

sub

Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

end sub

it works exactly like I would expect, waits 5 minutes then runs the
sub "get early data" then ends. the problem I am having is that
statement when used in a conditional macro as below, it runs both
continuously and the loop seems to call call the statement infinitely so
I get the infamous hourglass and eventually run out of memeory.

Thanks for taking a stab and I hope the added info helps.


DaveO wrote:

Marles, what does the sub "Get_early_data" do? If it doesn't alter the
Range("B27") calculation, then it will run forever as there's no way to stop
it.

Personally (I don;t know if this matters) I prefer to use a Do While loop
instead of what you have done here.

HTH.

"Marles McDonald" wrote:



I am trying to create a timing loop that updates data periodically until
a condition is met.
The text data is imported onto ashee from a Data Acquistion system every
5 minutes I need to calculate results from a subset of that data and
when certain conditions are met end the macro. Currently I just assign a
zero to each of the conditional results if true, if all are true the
sum_check is zero.

The timing loop needs to :

operate once
check the sum check cell
if the sum check (error_sum) is zero, exit
if the sum check is greater than zero then wait 5 minutes and operate
then loop until error_check is zero
then exit

I have tried the following which works if error_sum is zero
but gets me an endless loop and ends up using all my system resources if
the error_sum is 1

Sub Timed_Loop()


Do
error_sum = Worksheets("MONITOR").Range("B27")
Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Loop Until error_sum = 0

End Sub

I don't do this often and expect that I am missing something dead simple.

Any help would be appreciated.

Marles

Timing loop help please
 

Let me know if it works!

I have just thought that if the Get_early_data sub is not in th same form,
then you may need to make it available via another module or something.

Let me know how it goes.

HTH.

"Marles McDonald" wrote:

Thanks I'll try that, looks like I have a lot more to learn :)

Marles

DaveO wrote:
OK, I think I udnerstand.

the problem is this...

You're setting it off on a routine that loops. It sets the error_sum
varaible. trhe it schedules a task, then it loops. By the time it loops B27
hasn't altered as you've scheduled the Get_early_data for 5 minutes. It
therefore keeps looping, keeps setting tasks for 5 minutes in the future and
will grind your machine to a halt.

So as an idea....

Why not use the WorkSheet_Change function...

Use an IF statement something like...

-----------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B27").Text < 0 Then

Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Else

MsgBox "Routine Complete"

End If

End Sub

------------------------

Would this work better??

HTH.

"Marles McDonald" wrote:


DaveO

The sub "get early data" doesn't affect B27 all it does is select a
subset of data text data I am updating evert 5 minute from an
acquisition system. but the calculations of the data will change the B27
cell value. I do want it to run continually until B27 changes due to
the results of the calculations satisfy the spreadsheet conditions.

I've tried a do while loop. When I run the statement in a macro as

sub

Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

end sub

it works exactly like I would expect, waits 5 minutes then runs the
sub "get early data" then ends. the problem I am having is that
statement when used in a conditional macro as below, it runs both
continuously and the loop seems to call call the statement infinitely so
I get the infamous hourglass and eventually run out of memeory.

Thanks for taking a stab and I hope the added info helps.


DaveO wrote:

Marles, what does the sub "Get_early_data" do? If it doesn't alter the
Range("B27") calculation, then it will run forever as there's no way to stop
it.

Personally (I don;t know if this matters) I prefer to use a Do While loop
instead of what you have done here.

HTH.

"Marles McDonald" wrote:



I am trying to create a timing loop that updates data periodically until
a condition is met.
The text data is imported onto ashee from a Data Acquistion system every
5 minutes I need to calculate results from a subset of that data and
when certain conditions are met end the macro. Currently I just assign a
zero to each of the conditional results if true, if all are true the
sum_check is zero.

The timing loop needs to :

operate once
check the sum check cell
if the sum check (error_sum) is zero, exit
if the sum check is greater than zero then wait 5 minutes and operate
then loop until error_check is zero
then exit

I have tried the following which works if error_sum is zero
but gets me an endless loop and ends up using all my system resources if
the error_sum is 1

Sub Timed_Loop()


Do
error_sum = Worksheets("MONITOR").Range("B27")
Application.OnTime Now + TimeValue("00:05:00"), " Get_early_data"

Loop Until error_sum = 0

End Sub

I don't do this often and expect that I am missing something dead simple.

Any help would be appreciated.

Marles