OnTime event

[daveatthewell]

Here's what I'd like to achieve:

I have subroutine which takes one or more parameters - one being the same time that an ontime event triggers eg
sub mysub(active as date, lRow as long)

code

end sub
I need the ontime event to run the subroutine say 10 times a day at hourly intervals, each time checking the time until a certain time has been reached, then not call it again until the same start time every day. because I want to run effectively, the same routine every hour, it would be good if I could call one routine and pass parameters to it including the time it runs and and integer which would be used in the routine eg a row number.

OnTime requires that the subroutine to be run is given as a string. I have tried every which way to construct a string that contains all the parameters without success. If "A1" contains '09:00 and "B2" contains 1 on sheet xyz
then Application.OnTime EarliestTime:=TimeValue("17:00:00"), Procedure:="my_Procedure "& cstr(sheets("xyz").cells(1,1)) &" "& cstr(sheets("xyz").cells(1,2)) should be running the equivalent of my_procedure 09:00 1. But I can't get it to work. So is it at all possible to pass parameters in such a case?

 

[Richard Schollar]

Hi

It would have helped had you provided the code for the routine that you are attempting to call with the OnTime method. Anyway, you can pass paramters to the procedure using the following format (I've provided test code for you to see it working):

Sub Test()
Dim msg As String, msg2 As String
msg = "Hello!": msg2 = "World!"
Application.OnTime Now + TimeValue("00:00:01"), "'Test2 """ & msg & """, """ & msg2 & """'"
End Sub
Sub Test2(msg As String, msg2 As String)
MsgBox msg & " " & msg2
End Sub

Note the placement of the single quotes and double quotes.

 

[daveatthewell]

Thanks that was just what was required. But it raises a more general question on the use of quotes in concatenated strings and variables. Is there any guidance on this where the individual parts of the resultant string already contain quotes as in 'Sheet1'!A3. Of course the solution you provided works, but working on the principle that all double quotes must come in pairs, does this mean that the """ is a double quote enclosed in double quotes?

 

[Richard Schollar]

Hi Dave

When you build up a string and you want that string to include double quotes, you need to 'escape' the double quote (otherwise it terminates the string) and you do this by doubling up the double quotes hence:

Range("A1").Value = "My Name is ""Richard"""

Produces output of:

My Name is "RIchard"

in A1. The contsruct on the end of three double quotes """ literally means:

Include a double quote in the output string (ie the "" pair) and then terminate the string (the final "). To include a double-quote pair in the output string (commonly required if you are writing a formula to a cell with an IF statement where you want one result from the IF to be an empty string) you need to escape the double quote, then escape it again (ie so 4 double quotes in total). This then hasn't terminated the string, so you will ultimately need another double quote to do this:

Range("A1").Formula = "=IF(B1>0,B1,"""")"

Produces the formula in A1 of:

=IF(B1>0,B1,"")

In the code, the first double quote pair results in the first double quote in the formula above, and the second double quote rsults in the second above. The double quote following the closing parethesis ) terminates the formula string (and is not part of the output formula entered in the cell).

Does this make sense to you? Does it help?

 

[daveatthewell]

Hi Richard.
Yep - all makes sense now. Can't think why I didn't figure that a double double quote was no different to a double & when wanting to use a literal & in a construct. Can't help thinking that it would make code clearer if VB could use the construct 'Esc' (without quotes) to 'Escape' a character. I'm sure I saw it 'centuries' ago when I first started programming in Fortran or some such.
Anyway. Thanks for the advice .. and enjoy the Easter break.
David