PDA

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