Simple Variable to someone

Mike · Oct 29, 2013

Hi

Been Trying to figure out how to declare a Variable for 2 Application On Time events in excel VBa

These events (Macro's) get called by another Macro then they have to wait for 10 - 20 minutes, when the time to run arrives they don't run.

Have read that "(EarliestTime argument) should be assigned to a variable to store it".

"(EarliestTime argument)" is in Cell $X$9 and $W$11 16:40:15 time format

Any help appreciated, Thanks

Sub settimers()

Application.OnTime TimeValue(Range("$X$9").Text), "StartBlink"
Application.OnTime TimeValue(Range("$W$11").Text), "StopBlink"

End Sub

Walter Briscoe · Oct 29, 2013

In message <[email protected]> of
Tue, 29 Oct 2013 04:09:09 in microsoft.public.excel.programming, Mike

Hi

Been Trying to figure out how to declare a Variable for 2 Application
On Time events in excel VBa

These events (Macro's) get called by another Macro then they have to
wait for 10 - 20 minutes, when the time to run arrives they don't run.

Have read that "(EarliestTime argument) should be assigned to a
variable to store it".

"(EarliestTime argument)" is in Cell $X$9 and $W$11 16:40:15 time format

Any help appreciated, Thanks

Sub settimers()

Application.OnTime TimeValue(Range("$X$9").Text), "StartBlink"
Application.OnTime TimeValue(Range("$W$11").Text), "StopBlink"

End Sub

I have the following code, which works with Excel 2003.

Sub somename()
....
Dim IEalarm As Date ' Date at which MakeIEVisible will run
....
IEalarm = Now + TimeValue("00:00:15")
Application.OnTime EarliestTime:=IEalarm, Procedure:="MakeIEVisible"
....
end sub

You would need 2 variables for your 2 timers.

Where did you read "(EarliestTime argument) should be assigned to a
variable to store it"?
The 2003 help for ontime has several examples which suggest otherwise,
including

Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedure:="my_Procedure", Schedule:=False

Have you tried running your code? I see no reason why it should not
work.

Mike · Oct 29, 2013

Hi

Been Trying to figure out how to declare a Variable for 2 Application On Time events in excel VBa

These events (Macro's) get called by another Macro then they have to waitfor 10 - 20 minutes, when the time to run arrives they don't run.

Have read that "(EarliestTime argument) should be assigned to a variable to store it".

"(EarliestTime argument)" is in Cell $X$9 and $W$11 16:40:15 time format

Any help appreciated, Thanks

Sub settimers()

Application.OnTime TimeValue(Range("$X$9").Text), "StartBlink"

Application.OnTime TimeValue(Range("$W$11").Text), "StopBlink"

End Sub

Hi Walter

Thanks for assistance here is the paragraph that i interpreted as "Need to have Time to start, stored in a Variable"
As they run when called close to run time like a minute or so but any longer and they don't, i'm a stitch code together from the net type guy at the moment deffinately not schooled : )

These are the sentences i focused on, figured must be why they don't run, if variable needed for Canceling makes sense to me to have them for calling ?

Not trying to take it out of context just reducing read time for other.

--------------------------------------------

"if meanwhile another procedure is being executed and Excel is not in readymode within 20 seconds, this procedure will not run."

"This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable"

--------------------------------------------

Site where i got "need to send Earliest Time to a Variable"

http://www.globaliconnect.com/excel...rvals-or-a-specified-time&catid=79&Itemid=475

It says

--------------------------------------------------------

Stop or Cancel a Running Procedure (using the OnTime method)

To cancel a running procedure (using the OnTime method), the precise time of its scheduled run is required. Note that if you don't pass the time to a variable, Excel will not know which OnTime method to cancel, as Now + TimeValue("00:00:03") is not static, but becomes static when passed to a variable. This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable (use a Public variable to make the variable available to all Procedures in all modules) and then use it to cancel the OnTime.

--------------------------------------------------------------------------

But still don't know what to do with what you have put i have 7 macro's to call actually in the same procedure but again reduced it to try and keep things simple for others who may help.

Any further clarification or actual running code appreciated

Thanks for response

Cheers Mike

Walter Briscoe · Oct 30, 2013

In message <[email protected]> of
Tue, 29 Oct 2013 14:28:23 in microsoft.public.excel.programming, Mike

Hi Walter

Thanks for assistance here is the paragraph that i interpreted as "Need
to have Time to start, stored in a Variable"
As they run when called close to run time like a minute or so but any
longer and they don't, i'm a stitch code together from the net type guy
at the moment deffinately not schooled : )

These are the sentences i focused on, figured must be why they don't
run, if variable needed for Canceling makes sense to me to have them
for calling ?

Not trying to take it out of context just reducing read time for other.

--------------------------------------------

"if meanwhile another procedure is being executed and Excel is not in
ready mode within 20 seconds, this procedure will not run."

"This means that the time when the procedure is to run (EarliestTime
argument) should be assigned to a variable"

--------------------------------------------

Site where i got "need to send Earliest Time to a Variable"

http://www.globaliconnect.com/excel/index.php?option=com_content&view=a
rticle&id=103:applicationontime-vba-schedule-excel-to-run-macros-at-
periodic-intervals-or-a-specified-time&catid=79&Itemid=475

It says

--------------------------------------------------------

Stop or Cancel a Running Procedure (using the OnTime method)

To cancel a running procedure (using the OnTime method), the precise
time of its scheduled run is required. Note that if you don't pass the
time to a variable, Excel will not know which OnTime method to cancel,
as Now + TimeValue("00:00:03") is not static, but becomes static when
passed to a variable. This means that the time when the procedure is to
run (EarliestTime argument) should be assigned to a variable (use a
Public variable to make the variable available to all Procedures in all
modules) and then use it to cancel the OnTime.

--------------------------------------------------------------------------

But still don't know what to do with what you have put i have 7 macro's
to call actually in the same procedure but again reduced it to try and
keep things simple for others who may help.

Any further clarification or actual running code appreciated

Thanks for response

Cheers Mike

I am pleased with the answer to my question. <http://www.globaliconnect.
com> looks useful. I agree that you need a variable to cancel a timer,
whose value is not static. From what I see, your timers ARE static.
Assuming the story is more complicated than you have said, I suggest you
extend the examples on that page with something like these untested
fragments.
Const TimerCount as Long = 7
Dim Timers(1:TimerCount) as Date

Timers(1) = ...
....
Timers(TimerCount) = ...
Application.Ontime Timers(1), "Sub1"
....
Application.Ontime Timers(TimerCount), "SubTimerCount"

You will probably need some logic to note if each timer is active.
e.g.
sub sub1()
Timers(1) = 0
...
end sub

Then you need code to conditionally cancel timers.
if Timers(1) <> 0 Application.Ontime Timers(1), "Sub1", , False
....
if Timers(TimerCount) <> 0 Application.Ontime Timers(TimerCount), _
"SubTimerCount", , False

VBA does not seem to support arrays of functions. ;(

Mike · Oct 30, 2013

Hi

Been Trying to figure out how to declare a Variable for 2 Application On Time events in excel VBa

These events (Macro's) get called by another Macro then they have to waitfor 10 - 20 minutes, when the time to run arrives they don't run.

Have read that "(EarliestTime argument) should be assigned to a variable to store it".

"(EarliestTime argument)" is in Cell $X$9 and $W$11 16:40:15 time format

Any help appreciated, Thanks

Sub settimers()

Application.OnTime TimeValue(Range("$X$9").Text), "StartBlink"

Application.OnTime TimeValue(Range("$W$11").Text), "StopBlink"

End Sub

Hi Walter

Thanks for assistance here is the paragraph that i interpreted as "Need to have Time to start, stored in a Variable"
As they run when called close to run time like a minute or so but any longer and they don't, i'm a stitch code together from the net type guy at the moment deffinately not schooled : )

These are the sentences i focused on, figured must be why they don't run, if variable needed for Canceling makes sense to me to have them for calling ?

Not trying to take it out of context just reducing read time for other.

--------------------------------------------

"if meanwhile another procedure is being executed and Excel is not in readymode within 20 seconds, this procedure will not run."

"This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable"

--------------------------------------------

Site where i got "need to send Earliest Time to a Variable"

http://www.globaliconnect.com/excel...rvals-or-a-specified-time&catid=79&Itemid=475

It says

--------------------------------------------------------

Stop or Cancel a Running Procedure (using the OnTime method)

To cancel a running procedure (using the OnTime method), the precise time of its scheduled run is required. Note that if you don't pass the time to a variable, Excel will not know which OnTime method to cancel, as Now + TimeValue("00:00:03") is not static, but becomes static when passed to a variable. This means that the time when the procedure is to run (EarliestTime argument) should be assigned to a variable (use a Public variable to make the variable available to all Procedures in all modules) and then use it to cancel the OnTime.

--------------------------------------------------------------------------

But still don't know what to do with what you have put i have 7 macro's to call actually in the same procedure but again reduced it to try and keep things simple for others who may help.

Any further clarification or actual running code appreciated

Thanks for response

Cheers

Mike · Oct 30, 2013

Hi Walter

I actually think EarliestTime macro's run intermittently, (unpredictable times) could be not due to a lack of a Variable to store time format in Linked Cell but because i have the time ticking over in 3 second intervals, to help Excel calculate as there is so much being calculated, not the most uptodate computer, so have set it back to 1 second, i thought everything ran off system time, test again tomorrow just incase someone reading this is having issues to, beginners ay : ) Cheers

Simple Variable to someone

Mike

Walter Briscoe

Mike

Walter Briscoe

Mike

Mike