Timer Question

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
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?
 
Last edited:

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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:

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:

Code:
Sleep 60000
HTH, Andrew
 

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
Yes, but this will eliminate my need for the goto's. I am genuinely excited here.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
What you need is something like this

Code:
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

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

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
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?
 
Last edited:

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
Nevermind. I got it massaged to the point it works! And no Goto Peter!
 

Forum statistics

Threads
1,082,557
Messages
5,366,304
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top