Make the Procedure of Application.onTime dependent on earliesttime

[schabo]

Hello,
I am using application.onTime to schedule tasks within Excel throughout the day, and the EarliestTime Arguments are coming from defined ranges in Excel such as [Time1] and [Time2]. I wrote the code in this way:

Sub NewTaskScheduler()
Application.onTime earliesttime:=[Time1], procedure:="FirstCode"
Application.onTime earliesttime:=[Time2], procedure:="SecondCode"
End Sub

Sub FirstCode()
'Do Something1
End Sub

Sub SecondCode()
'Do Something2
End Sub

Instead of writing individual Subs for FirstCode, SecondCode etc, which will end up being a long list, I would like to group them all into a Generic Sub like that:

Sub NewTaskScheduler()
Application.onTime earliesttime:=[Time1], procedure:="GenericCode"
Application.onTime earliesttime:=[Time2], procedure:="GenericCode"
End Sub

Sub GenericCode()
If EarliestTime = [Time1] Then DoSomething1
If EarliestTime = [Time2] Then DoSomething2
End Sub

Or Even better, since my DoSomething are quite simple, such as [A1]=1, the best would be:

Sub NewTaskScheduler()
Application.onTime earliesttime:=[Time1], procedure:=[A1]=1
Application.onTime earliesttime:=[Time2], procedure:=[A2]=2
End Sub

Especially on that last option, is there a way to do that?

Thank you for the help.

 

[6StringJazzer]

What is the problem you are trying to solve? The initial code that you showed is the best way to do this.

You cannot query a timer for what time is set. If you set multiple timers at the same time, like you are doing in NewTaskScheduler, you won't be able to tell which one fired in GenericCode.

Also this idea is not a good programming practice. It is known as logical cohesion, a very weak form of cohesion.

 

[schabo]

Thanks for the response. My intent was just to try to simplify the code as I never really used Application.onTime before, but as you suggest I won't pursue that option.

Can you confirm that the Procedure Argument of Application.onTime MUST be a sub though?

For instance, the best way to use Application.onTime seems to require 2 Subs:

Sub NewTaskScheduler()
Application.onTime earliesttime:=[Time1], procedure:="FirstCode"
End Sub

Sub FirstCode()
[A1]=1
End Sub

Simplifying things that way is not possible right?:

Sub NewTaskScheduler()
Application.onTime earliesttime:=[Time1], procedure:=[A1]=1
End Sub

 

[joeu2004]

Can you confirm that the Procedure Argument of Application.onTime MUST be a sub though?

Well, it must be a string. And usually, the first word in the string must be a public Sub name in a normal module (not a worksheet object).

But a google search reveals that we can pass parameters, if we use the following syntax for the "procedure" string:

'subName "arg1" , "arg2" ... '

The key points are:
1. The "procedure" string starts and ends with single-quotes.
2. Each argument is enclosed with double-quotes. Arguments are separated by commas.
3. The type of the argument is determined by the type of the procedure parameter, as demonstrated below.

Although we can type single-quotes and a pair of double-quotes to represent one double-quote in the "procedure" string, I use Chr(39) for single-quotes and Chr(34) for double-quotes to avoid ambiguity and readability issues.

For example, enter the VBA code below, and execute "testit". And example of the results:

2/28/2021 2:34:19 PM
2/28/2021 2:34:19 PM
2/28/2021 2:34:20 PM 1 Long 101 String
2/28/2021 2:34:21 PM 2 Long 202 String
2/28/2021 2:34:22 PM 3 Long 303 String

Option Explicit
Const n As Long = 3
Dim st0 As Date, st1 As Date, cnt As Long
Dim et(1 To n) As Date, arg(1 To n, 1 To 2) As Variant

Sub testit()
Dim i As Long, s As String
MsgBox "warning: this will take " & n & " seconds to complete"
cnt = 0
st0 = Now
For i = 1 To n
    s = Chr(39) & "doit " & Chr(34) & i & Chr(34) & _
        "," & Chr(34) & 100 * i + i & Chr(34) & Chr(39)
    Application.OnTime st0 + TimeSerial(0, 0, i), s
Next
st1 = Now
End Sub

Sub doit(x As Long, y As Variant)
Dim s As String, i As Long
cnt = cnt + 1
et(cnt) = Now
arg(cnt, 1) = x
arg(cnt, 2) = y
If cnt >= n Then
    s = st0 & vbNewLine & st1
    For i = 1 To cnt
        s = s & vbNewLine & et(i) & _
            "    " & arg(i, 1) & "    " & TypeName(arg(i, 1)) & _
            "    " & arg(i, 2) & "    " & TypeName(arg(i, 2))
    Next
    Debug.Print vbNewLine & "----------" & vbNewLine & s
    MsgBox s
End If
End Sub

PS.... In this example, parameter "y" could be type Long as well, since the argument is an arithmetic expression (100*i + i). I use type Variant to emphasize that it is passed as a string.

 

[6StringJazzer]

a google search reveals that we can pass parameters

In that case it would work. I did not know that.

But it still doesn't make sense to do it this way if DoSomething1 and DoSomething2 are totally unrelated.

 

[schabo]

In that case it would work. I did not know that.

But it still doesn't make sense to do it this way if DoSomething1 and DoSomething2 are totally unrelated.

In my case, DoSomething1 and DoSomething2 are not related, so it looks like the proposed solution adds more complexity than it removes. Nonetheless, it is very interesting so let me try to understand the code here.
s = " 'doit "1","101" ' "

So Application onTime will call Sub doit and will assign x= 1 and y = 101 and then do its things and cause the MsgBox to appear.

For me, DoSomething is for the most part turning Cells from "On" to "Off", or from "Off" to "On". So typically, in the morning I trigger my DoSomething1, which simply means [A1] = "On", and then at a certain time later in the day DoSomething2 will simply turn [A1] back to "Off". Do you see an easy way to make that work?

 

[joeu2004]

let me try to understand the code here. s = " 'doit "1","101" ' "

Technically, s = " 'doit ""1"" , ""101"" ' "

That was the point of my using Chr(34) and Chr(39). The juxtaposition of single and double quotes becomes confusing, if not unreadable, at least in the font that my computer uses here.

So Application onTime will call Sub doit and will assign x= 1 and y = 101 and then do its things and cause the MsgBox to appear.

But only after the third time-based call to "doit". My demo was much more complicated than what you require, I suspect. I just wanted to show that the 3 timed events occur when intended.

For me, DoSomething is for the most part turning Cells from "On" to "Off", or from "Off" to "On". So typically, in the morning I trigger my DoSomething1, which simply means [A1] = "On", and then at a certain time later in the day DoSomething2 will simply turn [A1] back to "Off". Do you see an easy way to make that work?

For my previous response, I just wanted to demonstrate a little-known feature: that we can indeed pass parameters to OnTime event macros.

It can be useful, for example, if the number of OnTime events is variable.

But if you know the number of OnTime events that you want to schedule, I agree with 6StringJazzer: I would use unique names for each event macro. If they share common code, perhaps parameterized, each event macro can call a common procedure.

Perhaps something like the following.

' for simplicity, this design is incomplete.
' to stop these OnTime events, exit Excel entirely.
' it is not sufficient to simply close the Excel file.

' time1 and time2 are named cells that contain
' just times of day; for example 6:00 and 18:00

Sub setup()
Application.OnTime Date + [time1], "event1"
Application.OnTime Date + [time2], "event2"
End Sub

Sub event1()
doit "A1"
' reschedule for the same time tomorrow
Application.OnTime Date + 1 + [time1], "event1"
End Sub

Sub event2()
doit "A2"
' reschedule for the same time tomorrow
Application.OnTime Date + 1 + [time2], "event2"
End Sub

' A1 and A2 are cell names.
' toggle their values between "on" and "off"

Private Sub doit(c As String)
Dim x As Variant
x = Range(c)
If x = "on" Then Range(c) = "off" Else Range(c) = "on"
End Sub

 

[joeu2004]

PS....

For me, DoSomething is for the most part turning Cells from "On" to "Off", or from "Off" to "On". So typically, in the morning I trigger my DoSomething1, which simply means [A1] = "On", and then at a certain time later in the day DoSomething2 will simply turn [A1] back to "Off". Do you see an easy way to make that work?

And my previous response might have over-complicated things yet again. For what you describe, perhaps the following is sufficient.

' to get the ball rolling, manually execute
' whichever event macro applies based on the current time of day.
'
' alternatively, execute "startit" to make that decision.
'
' for simplicity, this design is incomplete.
' to stop the timed events, exit (close) Excel.
' it is not sufficient to simply close the Excel file.

Sub event1()
[A1] = "on"
' reschedule for later today
Application.OnTime Date + [time2], "event2"
End Sub

Sub event2()
[A1] = "off"
' rechedule for tomorrow
Application.OnTime Date + 1 + [time1], "event1"
End Sub

Sub startit()
Dim t As Date
t = Time
If t < [time1] Then
    ' mimic "event2", but schedule "event1" for later today, not tomorrow
    [A1] = "off"
    Application.OnTime Date + [time1], "event1"
ElseIf [time1] <= t And t < [time2] Then
    Call event1
Else
    Call event2
End If
End Sub

 

[schabo]

I see the point. This is more than enough material to get me going. Thanks for the help joeu2004 and 6StringJazzer!

 

[joeu2004]

Errata....

Technically, s = " 'doit ""1"" , ""101"" ' "

Actually, in this example, s = " 'doit 1 101' " would work just fine.

There is no requirement that the arguments are passed as strings. That was just the example in the article that I had found.

Of course, if the arguments are indeed strings, they must be surrounded by double-quotes.

So my last example is a perfect candidate for a parameterized invocation of an event macro. It could be simplified as follows.

' to get the ball rolling, manually execute whichever event applies based on current time of day.'
' alternatively, execute "startit" to make that decision.
'
' for this simplicity, this design is incomplete.
' to stop the timed events, exit (close) Excel.
' it is not sufficient to simply close the Excel file.

' time1 and time2 are named cells that contain just times of day; for example 6:00 and 18:00.
' time1 < time2 must be true

Sub event1()
[a1] = "on"
' schedule event2 for later today
Application.OnTime Date + [time2], " 'event2 1' "
End Sub

Sub event2(myoffset As Long)
[a1] = "off"
' schedule event1 for tomorrow (myoffset=1) or later today (myoffset=0)
Application.OnTime Date + myoffset + [time1], "event1"
End Sub

Sub startit()
Dim t As Date
t = Time
If t < [time1] Then
    Call event2(0)    ' schedule event1 for later today
ElseIf [time1] <= t And t < [time2] Then
    Call event1
Else
    Call event2(1)    ' schedule event1 for tomorrow
End If
End Sub