Running a subroutine every half hour up to 5 times

[cchristner]

Hey guys

Does anyone have any suggestions on how I might run a subroutine every half hour on its own up to 5 times and stops after the fifth time. Any suggestions would be greatly appreciated.


Thank you.

[mancubus]

?



Public i As Long

Sub M_snb_ontime_start()

i = i + 1

'******************************
'paste your macro here
'******************************

If i = 5 Then Exit Sub

Application.OnTime DateAdd("n", 30, Time), "M_snb_ontime_start"

End Sub


notice the declaration of public variable i, at top of the all macros, in the module.

credits: http://www.snb-vba.eu/VBA_Application.OnTime_en.html

[mancubus]

you can test it with



Sub M_snb_ontime_start()

i = i + 1

'******************************
MsgBox i & " - illustration"
'******************************

If i = 5 Then Exit Sub
Application.OnTime DateAdd("s", 2, Time), "M_snb_ontime_start"

End Sub

[cchristner]

That was very helpful. Thank you

[Bob Phillips]

Looks like a case for a static variable to me, not a global one.

[mancubus]

at first i inserted a line for a called sub (which is very common) but in the end came with this. :)


in that case, for the OP:



Sub M_snb_ontime_start_test()

Static i As Long

i = i + 1

'******************************
MsgBox i & " - illustration"
'******************************

If i = 5 Then Exit Sub
Application.OnTime DateAdd("s", 5, Time), "M_snb_ontime_start"

End Sub