View Full Version : Solved: VBA timer trouble
arunlgt
03-03-2010, 10:53 AM
Hi Everyone:
I have a spreadsheet(attached to this mail).I want a macro that starts at 9:45 am runs every 15 minutes that captures range (A1:E10) and copies them to a new worksheet.
The timer must end at 4:30 pm every day.
I have written some code,but it does not work,if someone could help me "tweaking" it i would be very grateful
Regards
2879
arunlgt
03-03-2010, 11:18 AM
Oh Sorry here is the code:
Public Sub StartTimer()
' Workbooks(cWorkbook).Sheets("Summary").Range("B2") = DateDiff("s", TimeLastExecution, Now) / 60
Workbooks(cWorkbook).Sheets("Summary").Range("B2") = TimeLastExecution
If TimeLastExecution < Now + TimeSerial(0, 15, 0) Then
Call Update_SQLFundSummary
End If
End Sub
Public Sub EODStartTimer()
Application.OnTime TimeValue("09:45:00"), procedure:="StartTimer" 'EOD timer"
MsgBox "Timer Started"
End Sub
I basically want a loop that starts the timer at 9:45 and ends it at 4:30 everyday
Thanks
Bob Phillips
03-03-2010, 11:31 AM
You have workbook open and close code in a standard module, it should be in ThisWorkbook. And where does StartTime get initiated?
arunlgt
03-03-2010, 11:39 AM
Hi XLD,
actually I already corrected it,i put the code in this workbook.
the start time gets initialized in the EODStartTimer() sub.
arunlgt
03-03-2010, 12:35 PM
ok made some changes :
in This Workbook:
Private Sub Workbook_Open()
Call StartTimer
End Sub
In the module UpdateSummary :
Public Sub StartTimer()
' Workbooks(cWorkbook).Sheets("Summary").Range("B2") = TimeLastExecution
If TimeLastExecution < Now + TimeSerial(0, 1, 0) Then
Call Update_SQLFundSummary
End If
End Sub
Public Sub EODStartTimer()
Application.OnTime TimeValue("09:45:00"), procedure:="StartTimer"
End Sub
now all I need is the timer to start at 9:45 and end at 4:30
Bob Phillips
03-03-2010, 12:44 PM
How does the timer get restarted? You should check the tim in StartTimer and not re-start it if after yhour cutoff time.
arunlgt
03-03-2010, 01:06 PM
Hi XLD:
Thanks for the reply,I think I am taking you every which way,Sorry about that: my manager was not very clear in his statement(!)
here is what I am trying :
I have 5 traders that use this spreadsheet: i want the subroutine StartTimer to fire off every 15 minutes whenever they open the spreadsheet.It should start at 9:45 am and end at 4:30 pm everyday(except weekends and holidays).The subroutine fires off ONLY if the workbook is open or it is stays open.
We can ignore the weekend and holidays part for now,its too complicated.
Any pointers how I can proceed?
Regards
Bob Phillips
03-03-2010, 01:32 PM
No, I don't think so, I think you have been clear (can't say the same for your code, which is why I am trying to tell you how to do it rather than do it :)).
What you need is to fire thye Startimer routine in Workbook_Open, as you are now doing.
StartTimer should check the day and time to see whether it does anything. If within bounds, fire your doing macro. If not, just exit gracefully.
Something like this
Public Sub StartTimer()
If Weekday(Date, 2) < 6 Then
If Time >= TimeSerial(9, 45, 0) And _
Time <= TimeSerial (16, 15, 0) Then
Application.Ontime "StartTimer", Now + TimeSerial(0, 15, 0)
Call Update_SQLFundSummary
End If
End If
End Sub
arunlgt
03-03-2010, 01:50 PM
Thanks XLD:
but i am getting an error on this line :
Application.OnTime "StartTimer", Now + TimeSerial(0, 1, 0)
i am using it like this:
Private Sub Workbook_Open()
Call StartTimer
End Sub
arunlgt
03-03-2010, 01:57 PM
I slightly modified your proc :
Public Sub StartTimer()
If Weekday(Date, 2) < 6 Then
If Time >= TimeSerial(9, 45, 0) And _
Time <= TimeSerial(16, 15, 0) Then
' Application.OnTime "StartTimer", Now + TimeSerial(0, 1, 0)
RunWhen = Now + TimeSerial(0, cRunIntervalMinutes, 0)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
' Call Update_SQLFundSummary
End If
End If
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.