Timing loop help please

[Marles McDonald]

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

 

[DaveO]

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]

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]

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

 

[Marles McDonald]

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

Marles

 

[DaveO]

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.