Timer Question

[Bill_Biggs]

I have created a macro that continually updates a pivot chart. The macro runs constanly, not unlike a permanent loop.

I need the macro stop approximately every minute and update. Here is the command for that:
<font face=Courier New>B = Now</FONT>

Established earlier in the macro

<font face=Courier New><SPAN style="color:#00007F">If</SPAN> B <= B + TimeValue("00:01:03") And B >= B - TimeValue("00:00:57") <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Alpha<br></FONT>

The problem is that when the command loops the the line above, if the time is not precisely either of the times listed, it ignores the command and keeps going. Is there a way I can have the macro goto Alpha if the time is BETWEEN the two times listed?

 

[Andrew Fergus]

Hi

If you simply want to pause the macro then use the Sleep API. This puts the code to sleep for as many milliseconds as you specify, and is not time dependant.

Make the following declaration at the top of your code:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

and to make the code go to sleep for 60 seconds, then use the following line:

Sleep 60000

HTH, Andrew

 

[Bill_Biggs]

I have not used anything like this before. When the code comes to the time I have indicated, it is refreshing its data from another drive and then refreshes the graph in question. So if it is "sleeping" will it carry out the other fiunctions I need it to carry out during these times?

 

[Andrew Fergus]

This API causes the processing to pause such that nothing else happens. If you are doing other things during the minute in question then you don't want to use the sleep API - I assumed you had a permanent loop that pretty much did nothing and once every minute jumped out and updated the pivot table. Is this not the case?

 

[Bill_Biggs]

I did, but I like the idea you gave me so much, I am thinking about changing the whole macro around for it. I learn something new and useful every time I come to this website. Thank you.

 

[VoG]

Hello Bill

Take a look at OnTime in the Help. You won't be able to use it with your GoTo spaghetti code but for the future...

 

[Bill_Biggs]

Yes, but this will eliminate my need for the goto's. I am genuinely excited here.

 

[VoG]

What you need is something like this

Dim dNext As Date

Sub StartDoingIt()
 DoItAgain
End Sub

Sub DoItAgain()
 dNext = Now + TimeValue("01:00:00")
 Application.OnTime dNext, "DoItAgain"
 DoIt
End Sub

Sub DoIt()
 MsgBox "Hello Bill Biggs!!"
End Sub

But you need to turn this off once the WB is closed or it will keep opening. In the ThisWorklbook code enter

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dNext, "DoItAgain", schedule:=False
End Sub

 

[Bill_Biggs]

I appreciate all the advice. Here is where I am at:

I just need this to run all day long, refreshing once an hour (I have it set for 1 minute for testing purposes), but I need it to loop constanly in the primary macro so that I can stop it by clicking command buitton 2 if I wish to. Finally, while the macro is running I want the D8 cell to be red. Otherwise gray. But it is not updating on the minute. It just keeps running. Can you tell me what I hvae done wrong?

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> mySw1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN></FONT>
__________________________________________________________________________________________
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> SetUpPage()<br>RefreshData<br><SPAN style="color:#00007F">Dim</SPAN> n&<br>B = Now<br><br><SPAN style="color:#00007F">If</SPAN> mySw1 = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    mySw1 = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Range("D8").Select<br>        ActiveCell.Interior.Color<SPAN style="color:#00007F">In</SPAN>dex = 3<br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c In Cells<br>DoEvents<br>    <br>    <SPAN style="color:#00007F">If</SPAN> mySw1 = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> MyEnd<br>    <SPAN style="color:#00007F">If</SPAN> B = B + TimeValue("00:01:00") <SPAN style="color:#00007F">Then</SPAN><br>    RefreshData<br>    B = Now<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> c.Value = "" <SPAN style="color:#00007F">Then</SPAN> n = n + 1<br><SPAN style="color:#00007F">Next</SPAN> c<br><br><br>MyEnd:<br>Range("D8").Select<br>    ActiveCell.Interior.ColorIndex = 15<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
______________________________________________________________________________________
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()<br>mySw1 = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
________________________________________________________________________________________
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> RefreshData()<br><br>Sheets("Receive Raw Data").Select<br>    Selection.QueryTable.Refresh BackgroundQuery:=<SPAN style="color:#00007F">False</SPAN><br>    Sheets("Receive Pivot").Select<br>    Sheets("Receive Pivot").ScrollArea = "a1:g15"<br>    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh<br>    ActiveWorkbook.Save<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Also, how ddo you guys put your code in the window that roll up and down on these pages?

 

[Bill_Biggs]

Nevermind. I got it massaged to the point it works! And no Goto Peter!