Solved: VBA timer trouble

[arunlgt]

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

Attachment 2879

[arunlgt]

Oh Sorry here is the code:

[VBA]
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


[/VBA]




I basically want a loop that starts the timer at 9:45 and ends it at 4:30 everyday

Thanks

[Bob Phillips]

You have workbook open and close code in a standard module, it should be in ThisWorkbook. And where does StartTime get initiated?

[arunlgt]

Hi XLD,

actually I already corrected it,i put the code in this workbook.

the start time gets initialized in the EODStartTimer() sub.

[arunlgt]

ok made some changes :


in This Workbook:

[vba]Private Sub Workbook_Open()
Call StartTimer
End Sub
[/vba]


In the module UpdateSummary :


[vba]

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

[/vba]


now all I need is the timer to start at 9:45 and end at 4:30

[Bob Phillips]

How does the timer get restarted? You should check the tim in StartTimer and not re-start it if after yhour cutoff time.

[arunlgt]

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]

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

[vba]

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
[/vba]

[arunlgt]

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:

[VBA]

Private Sub Workbook_Open()
Call StartTimer
End Sub

[/VBA]

[arunlgt]

I slightly modified your proc :

[VBA]
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

[/VBA]