View Full Version : Running a subroutine every half hour up to 5 times
cchristner
10-25-2016, 10:48 PM
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
10-26-2016, 12:50 AM
?
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
10-26-2016, 12:56 AM
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
10-26-2016, 01:03 AM
That was very helpful. Thank you
Bob Phillips
10-26-2016, 04:06 AM
Looks like a case for a static variable to me, not a global one.
mancubus
10-26-2016, 04:46 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.